Updating other Excel sheets automatically from Master sheet.

Closed
mandymindy
Posts
19
Registration date
Monday January 21, 2019
Status
Member
Last seen
February 6, 2019
- Updated on Jan 21, 2019 at 05:41 AM
vcoolio
Posts
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
- Feb 6, 2019 at 05:05 PM
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?

4 replies

Daniel Telele
Posts
1227
Registration date
Tuesday March 7, 2017
Status
Member
Last seen
November 4, 2021
6,273
Jan 21, 2019 at 04:28 AM
Hello,

Please see this solution to your question!

Best,
1
mandymindy
Posts
19
Registration date
Monday January 21, 2019
Status
Member
Last seen
February 6, 2019

Jan 21, 2019 at 04:34 AM
Hi Daniel,

I am not really good at that, is that something you can help me with?
0
vcoolio
Posts
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
249
Jan 21, 2019 at 05:38 AM
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.
0
mandymindy
Posts
19
Registration date
Monday January 21, 2019
Status
Member
Last seen
February 6, 2019

Jan 21, 2019 at 06:36 AM
0
mandymindy
Posts
19
Registration date
Monday January 21, 2019
Status
Member
Last seen
February 6, 2019

Jan 21, 2019 at 06:37 AM
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.
0
vcoolio
Posts
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
249
Jan 21, 2019 at 08:34 AM
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.
0
mandymindy
Posts
19
Registration date
Monday January 21, 2019
Status
Member
Last seen
February 6, 2019

Jan 21, 2019 at 11:06 AM
Hi,

thank you for that, really helpful, can you not send it back in dropbox or to my email as that link is blocked for me on my server.
0
vcoolio
Posts
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
249
Jan 21, 2019 at 06:55 PM
0
mandymindy
Posts
19
Registration date
Monday January 21, 2019
Status
Member
Last seen
February 6, 2019

Jan 22, 2019 at 03:08 AM
Hi, this isn't working either.. says this.
this link has been automatically turned off.
0
vcoolio
Posts
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
249
Jan 22, 2019 at 03:47 AM
Hi MandyMindy,

I'm not surprised. I've been having problems with Drop Box for about 18 months now and its still not resolved. However, perhaps through One Drive we may succeed! I'm sure your server will allow One Drive links:-

https://1drv.ms/x/s!Ao80oL4IXkqSkRL31w58k9SqzvE7

Click on the down load button towards the top right once the link opens.

Let me know how this works out.

Cheerio,
vcoolio.
0
mandymindy
Posts
19
Registration date
Monday January 21, 2019
Status
Member
Last seen
February 6, 2019

Jan 22, 2019 at 04:27 AM
Hi,

I have entered new data onto the January page and it hasn't updated in the other sheets.
0
vcoolio
Posts
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
249
Jan 23, 2019 at 11:26 PM
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.
0
mandymindy
Posts
19
Registration date
Monday January 21, 2019
Status
Member
Last seen
February 6, 2019

Jan 28, 2019 at 04:01 AM
Hi,

no luck... see if it works on your end and try sending it over.
0
vcoolio
Posts
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
249
Jan 28, 2019 at 05:02 AM
Hello Mandymindy,

It works perfectly on this end. If I send it to you, I'd only be sending you the exact same sample file that you sent in post #4. So you'd be back at square one.
If you are on an internal server, the trust settings could be your problem.

Another option to try and sort this out:-
Would you be willing to send me your actual workbook (or a copy of it)?
If so, let me know and I'll PM you my private e-mail address.

Cheerio,
vcoolio.
0
mandymindy
Posts
19
Registration date
Monday January 21, 2019
Status
Member
Last seen
February 6, 2019

Jan 28, 2019 at 05:45 AM
hi,

sure send me your email address
0
vcoolio
Posts
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
249
Jan 28, 2019 at 05:56 AM
Keep an eye on your PMs.
0
mandymindy
Posts
19
Registration date
Monday January 21, 2019
Status
Member
Last seen
February 6, 2019

Jan 28, 2019 at 05:57 AM
ive sent you an email
0