Auto populate data to master worksheet from other sheets in a shared workbook

[chevyfam661]

Hello all,

My goal is to auto populate data from new worksheets to a summary master worksheet, all in the same workbook. I found an old thread that has almost exactly what I'm looking for, however, I couldn't obtain code to apply it to my worksheet.

Thanks in advance.

[chevyfam661]

This is the VBA code I found in the prior thread that 'mrice' so kindly provided. My summary sheet would need the data from 14 cells in each worksheet, that will all have the same format; b5, b6, h58, s24, s58, s34, h62, s25, s62, s38, h66, p42, s66, s42

I'm manually bringing data over using "=" function. Any assistance would be greatly appreciated.


Private Sub Worksheet_Activate()
Dim Sheet As Worksheet
For Each Sheet In Me.Parent.Sheets
If Sheet.Name <> Me.Name Then
If Sheet.Cells(Rows.Count, 1).End(xlUp).Row <> 1 Then
Sheet.Range(Sheet.Cells(2, 1), Sheet.Cells(Sheet.Cells(Rows.Count, 1).End(xlUp).Row, 10)).Copy Destination:=Me.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

End If
Else
Me.Range(Cells(2, 1), Cells(Rows.Count, 10)).Clear
End If
Next Sheet
End Sub

Hello all,


My goal is to auto populate data from new worksheets to a summary master worksheet, all in the same workbook. I found an old thread that has almost exactly what I'm looking for, however, I couldn't obtain code to apply it to my worksheet.

Thanks in advance.

[Doc.AElstein]

Hi chevyfam661
Welcome to the Forum

Please when you get time, check out and get familiar with the forum Rules and posting technique suggestions:
https://www.excelforum.com/forum-rul...rum-rules.html
http://www.excelforum.com/developmen...ml#post4606484

It is good to have the code that you gave in Post #2, which is close to what you want from the other Thread, - but a couple of other things are helpful also:
_1 :- Link to existing Thread
As you said the previous Thread is close to what you want, ( and as we are almost adding another solution to it , it is debatable whether you high jacked it or not… ) … but.. in any case a link to that Thread is very helpful when you start a new Thread, so we can quickly get to it and help get clued up on what you are after. So please copy the URL from the Browser URL Bar and paste it in when referencing it.
( I am assuming it is this Thread:
https://www.excelforum.com/excel-pro...-workbook.html
URL from URL Bar: https://www.excelforum.com/excel-programming-vba-macros/683139-auto-populate-data-to-a-master-worksheet-from-other-sheets-in-a-shared-workbook.html )

_2:- Please use code tags:
Instead of posting something like this:

Private Sub Worksheet_Activate()
Dim Sheet As Worksheet
'
End Sub

Click on the # editor icon , to get BB Code Code tags, ( or add those Tag pairs manually ) so that you get this seen in the Editor window before you post :

[CODE] Private Sub Worksheet_Activate()
Dim Sheet As Worksheet
'
End Sub[/CODE]

That has the effect that after posting, the code comes out in a nice code window, like this:

Please Login or Register  to view this content.

Have a go at Editing your Post #2 please to add those tags and then Save to repost. ( Note: there is a Forum Software Bug which sometime cause the original contents to vanish without trace when you hit the Edit button, so you might want to copy the post contents to the clipboard before you hit the Edit button, so that you can re-paste in the case that the original contents vanish unexpectedly. )

_3: Practice posting
You might find it helpful to practice posting here in the Test Sub Forum:
https://www.excelforum.com/development-testing-forum/
You can start a new Thread there, - give it a title like, “Just testing and practicing posting. No Reply needed”
Then you can practice things like code tags etc…

_.......____________

_4:- Coming back to your question:…..

As far as I can recall, the 'mrice' code assumes data in the first column of the data worksheets and copies each data column and stacks the data in the first column of the Master / Summary worksheet.
The code is an Event type, specifically , Worksheet_Activate() , that is to say it kicks in when the Worksheet is activated in whose code module the code, Sub Worksheet_Activate() is in.
'mrice' put the code in first tab, :
( https://www.excelforum.com/excel-pro...ml#post4012440 To see the codeyou do this: _1:- Right Click on Tab ; _2:-Select something like “Show Code” - RightClickTab_ShowCode.JPG :- http://imgur.com/JbJSffy )

So I assume that is something like what you want to happen, that is to say when anyone selects the summary worksheet, then all the info will be updated, that to say, the current data in the Summary worksheet will be cleared and then it will be updated with the current data from the data worksheets ?

You are wanting to pull info form various cells in each worksheet. So it is very similar to what 'mrice' did.

_.__

What I will do / have done, for you first is give you a code to get you started that just updates the date in the first column of the Summary worksheet. ( That is to say it brings in the Values from cell B5 in the data worksheets ).
I will “open it up”, making it a bit longer and more complicated then necessary including a lot of explaining 'Green comments
See if it is along the lines of what you want, and see if you can extend it to do the updating for all the other cells .. ( adding code lines at this point in the given code: *** ADD HERE CODE LINES TO BRING IN THE OTHER CELLS DATA . )
See how you get on , and me or someone will help if you need further help.

Code is here:
https://www.excelforum.com/developme...ml#post4730378



Alan.

P.S.

I do not quite understand why you have 4 empty rows in your sample Summary worksheet? , or why you missed the last worksheet?

You show this:_..
Using Excel 2007 32 bit

Row\Col	A
4	Date
5	18.08.2017
6	31.03.2017
7	03.04.2017
8	04.04.2017
9	05.04.2017
10	06.04.2017
11	06.04.2017
12	12.04.2017
13	12.04.2017
14	25.04.2017
15	26.04.2017
16
17
18
19
20	14.06.2017
21	14.06.2017
22	27.07.2017
23	02.08.2017

Worksheet: Summary


_... whereas my code will , first in section Rem 2 , clear that data Range in column A, and then it will go through all data worksheets and return the following updated data values:

Row\Col	A
4	Date
5	18.08.2017
6	31.03.2017
7	03.04.2017
8	04.04.2017
9	05.04.2017
10	06.04.2017
11	06.04.2017
12	12.04.2017
13	12.04.2017
14	25.04.2017
15	14.06.2017
16	14.06.2017
17	14.06.2017
18	27.07.2017
19	02.08.2017
20	26.04.2017

Worksheet: Summary

( That above action occurs when you select the Tab “Summary” )

Anyway see how you get on, and let us know how you get on please.