Updating other Excel sheets automatically from Master sheet.

Posts
19
Registration date
Monday January 21, 2019
Status
Member
Last seen
February 6, 2019
-
Hi,

I have been trying to sort this for a while. I have entered data in my master sheet and I have 4 other sheets. When I put new data into the master sheet I want the other sheets to automatically update, can anyone help me?
See more 

4 replies

Best answer
Posts
772
Registration date
Tuesday March 7, 2017
Status
Administrator
Last seen
October 19, 2019
951
1
Thank you
Hello,

Please see this solution to your question!

Best,
Daniel Telele

Community Manager and Social Media Editor - CCM.net

mandymindy
Posts
19
Registration date
Monday January 21, 2019
Status
Member
Last seen
February 6, 2019
-
Hi Daniel,

I am not really good at that, is that something you can help me with?
Respond to Daniel Telele
Posts
1251
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2019
205
0
Thank you
Hello MandyMindy,

To help you further, it would be good if you could supply a sample of your workbook. Upload a sample to a free file sharing site such as ge.tt or Drop Box then post the link to your file back here. Make sure that the sample is an exact replica of your actual workbook showing inputs and expected outputs. If your data is sensitive, then please use dummy data. A few rows of data per sheet will suffice.
Seeing your workbook sample will simplify the process of helping you.

Cheerio,
vcoolio.
mandymindy
Posts
19
Registration date
Monday January 21, 2019
Status
Member
Last seen
February 6, 2019
-
mandymindy
Posts
19
Registration date
Monday January 21, 2019
Status
Member
Last seen
February 6, 2019
-
this is the dummy, the January page is the master page and I want to add more data below and for it to update in the other depot sheets automatically, thanks as I have more I want to do it too like this sheet.
Respond to vcoolio
Posts
1251
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2019
205
0
Thank you
Hello MandyMindy,

The following code, placed in a standard module and assigned to a button, should do the task for you:-


Sub Test()

        Dim ar As Variant, sh As Worksheet, ws As Worksheet
        Dim i As Integer
                
        ar = Array("BA", "WW", "BE", "AB", "HA", "TW")
        Set sh = Sheets("January")

Application.ScreenUpdating = False

For i = LBound(ar) To UBound(ar)
        Set ws = Sheets(ar(i))
        ws.Range("B8", ws.Range("H" & ws.Rows.Count).End(xlUp)(2)).ClearContents
        sh.Range("B7", sh.Range("B" & sh.Rows.Count).End(xlUp)).AutoFilter 1, ar(i)
        sh.Range("B8", sh.Range("H" & sh.Rows.Count).End(xlUp)).Copy ws.Range("B" & Rows.Count).End(3)(2)
        sh.[B7].AutoFilter
        ws.Columns.AutoFit
Next i

Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Data transfer completed!", vbExclamation, "Status"

End Sub


The code will filter Column B for the sheet names and transfer the relevant rows of data to each individual sheet.
Each individual sheet is refreshed each time the code is run.

Following is the link to your sample file with the code implemented:-

http://ge.tt/16eGn3u2

In the sample, you'll note that I've cleared all the destination sheets just so you can see how the code works. Click on the "RUN" button to see it work.

I hope that this helps.

Cheerio,
vcoolio.
vcoolio
Posts
1251
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2019
205 -
Hi MandyMindi,

The file I've been testing on is the exact sample that you posted in post #4 so I can't see why you are having problems. Are you using a Mac per chance?
mandymindy
Posts
19
Registration date
Monday January 21, 2019
Status
Member
Last seen
February 6, 2019
-
I am using windows.
vcoolio
Posts
1251
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2019
205 -
Ok. Try changing line 15 in the code above from:-


sh.Range("B8", sh.Range("H" & sh.Rows.Count).End(xlUp)).Copy ws.Range("B" & Rows.Count).End(3)(2)


to

 sh.Range("B8", sh.Range("H" & sh.Rows.Count).End(xlUp)).Copy
 ws.Range("B" & Rows.Count).End(3)(2).PasteSpecial xlValues


This will "kill off" anything that may interfere with the execution of the code by pasting values only to the destination sheets.

Let us know what happens.

Cheerio,
vcoolio.
mandymindy
Posts
19
Registration date
Monday January 21, 2019
Status
Member
Last seen
February 6, 2019
-
Not working,
vcoolio
Posts
1251
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2019
205 -
"Not working".

Are you receiving any error messages?
Respond to vcoolio
Posts
1251
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2019
205
0
Thank you
Good day MandyMindy,

Let's try the following:-

In the sample file, place the following code into the January worksheet module:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("B8", Range("H" & Rows.Count).End(xlUp))) Is Nothing Then Exit Sub
Test

End Sub


This code is a WorkSheet_Change event for the entire range (B8:H) and will execute each time you type a value in any cell in the range and then move on to the next cell. On each cell entry, it calls the Test macro.

Ignore the existing "RUN" button (don't use it).

In the January sheet, type in another complete row of data for say, sheet HA.

Check all sheets to see if they have had all their relevant rows of data transferred over including the additional row of data you've just typed into the January sheet for sheet HA.

Type a few additional rows of data in the January sheet for random destination sheets and then check that they have been transferred over to their individual sheets.

To implement the above code:-

- Right click on the January sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

Let us know how this works and also let us know if any error messages come up.

Cheerio,
vcoolio.
vcoolio
Posts
1251
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2019
205 -
.................or, in the meantime, you could try the following modified version of the code:-


Sub Test()

        Dim ar As Variant, sh As Worksheet, ws As Worksheet
        Dim i As Integer, lr As Long
                
        ar = Array("BA", "WW", "BE", "AB", "HA", "TW")
        Set sh = Sheets("January")
        lr = sh.Range("B" & Rows.Count).End(xlUp).Row
        
Application.ScreenUpdating = False

With sh.Range("B8:H" & lr)
        .Value = .Value
End With

For i = LBound(ar) To UBound(ar)
        Set ws = Sheets(ar(i))
        ws.Range("B8", ws.Range("H" & ws.Rows.Count).End(xlUp)(2)).ClearContents
        sh.Range("B7", sh.Range("B" & sh.Rows.Count).End(xlUp)).AutoFilter 1, ar(i)
        sh.Range("B8", sh.Range("H" & sh.Rows.Count).End(xlUp)).Copy ws.Range("B" & Rows.Count).End(3)(2)
        sh.[B7].AutoFilter
        ws.Columns.AutoFit
Next i

Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Data transfer completed!", vbExclamation, "Status"

End Sub


Cheerio,
vcoolio.
mandymindy
Posts
19
Registration date
Monday January 21, 2019
Status
Member
Last seen
February 6, 2019
-
I did send you my entire work book and no I am not receiving any error messages.
vcoolio
Posts
1251
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2019
205 -
OK. So did you try the code above (post #36)?
mandymindy
Posts
19
Registration date
Monday January 21, 2019
Status
Member
Last seen
February 6, 2019
-
yeah I tried that code and entered it into the main page and nothing updated
vcoolio
Posts
1251
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2019
205 -
What do you mean by:

".....and entered it into the main page...."


Did you not place the code into a standard module?
Respond to vcoolio