Updating other Excel sheets automatically from Master sheet.Closed

Question

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?

Daniel_CCM · Answer

Hello,

Please see this solution to your question!

Best,

vcoolio · Answer

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.

vcoolio · Answer

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 · Answer

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.

Updating other Excel sheets automatically from Master sheet.

4 responses

Subscribe To Our Newsletter!