Referencing multiple sheets based on list of names of the sheets

[ArtofExcel]

I'm looking to make a summary sheet for specific information that would be pulled from multiple sheets in the workbook. These sheets are created from the following macro

Sub Update_Names()


Do While Worksheets.Count > 6 'Clears old sheets (first 6 sheets stay)
Application.DisplayAlerts = False
Worksheets(7).Delete
Application.DisplayAlerts = True
Loop


Dim MyCell As Range, MyRange As Range 'repopulates sheets bases of list in "Names"

Set MyRange = Sheets("Names").Range("F4") 'List starts in F4 of current sheet
Set MyRange = Range(MyRange, MyRange.End(xlDown))


For Each MyCell In MyRange
Sheets.Add after:=Sheets(Sheets.Count) 'creates a new worksheet
Worksheets("Template").Cells.Copy ActiveSheet.Range("A1") 'Copies template for each name
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
End Sub

I want to use the same list that starts in F4 to pull data from each sheet that this macro creates and pull it into a summary page, notice the length of the list is variable, the "names" group is defined using an offset function that allows for it to be any length to allow all names to have a page created for them with no extras ect. Ideally the summary would input into a table format table I could pull in that had name, data 1, data 2, ect. I've played with versions of indirect and whatnot but defining the sheets based on the names in the list "names" is holding me up here. Thanks in advance for any help you can give.

 

[ArtofExcel]

If it helps this this is the definition of the "names" list
=OFFSET(Employees!$F$4,0,0,COUNTA(Names!$F:$F)-1)

 

[ArtofExcel]

Used wrong version this is the actual names list:

=OFFSET(Names!$F$4,0,0,COUNTA(Names!$F:$F)-1)*

 

[cerfani]

why not have your macro just copy paste the data wherever you want it instead of by formula?

 

[ArtofExcel]

The data will not be available when this macro is run (it will be filled in by hand after) and I don't see any way this macro can be changed to update a different sheet (not a created one) to list all the filled in data, the only part I see that may transfer is the

Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet

piece but I do not know VBA well enough to make anything useful off that information.

 

[cerfani]

The data will not be available when this macro is run (it will be filled in by hand after) and I don't see any way this macro can be changed to update a different sheet (not a created one) to list all the filled in data, the only part I see that may transfer is the

Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet

piece but I do not know VBA well enough to make anything useful off that information.

If you know the names of the sheets or the position of the tabs from left to right... you can reference any sheet you want and write data whenever you need to.

If filling in the data by hand is not a random process and has logic then you can add filling in the data to the macro after sheets gets created in the loop... just open the source data and copy over whatever at that time and you can additionally copy over to a created summary sheet. You can do anything really, that you could do as a user manually opening workbooks and copying stuff.

 

[ArtofExcel]

If you know the names of the sheets or the position of the tabs from left to right... you can reference any sheet you want and write data whenever you need to.

If filling in the data by hand is not a random process and has logic then you can add filling in the data to the macro after sheets gets created in the loop... just open the source data and copy over whatever at that time and you can additionally copy over to a created summary sheet. You can do anything really, that you could do as a user manually opening workbooks and copying stuff.

That data input into the worksheets will be "random" in the way that the program will have no way to know what will be in each cell, I can run an index match to check for the data after it is input by hand but what I'm asking is how would I reference the sheets, I know I can reference them. Ideally I could could could how many names I have in the list and check that many sheets start at 7 until i reach sheet 6+(names in list) pulling the data I need from each one. The issue is I have no idea HOW to do this in VBA or an excel function.

 

[cerfani]

That data input into the worksheets will be "random" in the way that the program will have no way to know what will be in each cell, I can run an index match to check for the data after it is input by hand but what I'm asking is how would I reference the sheets, I know I can reference them. Ideally I could could could how many names I have in the list and check that many sheets start at 7 until i reach sheet 6+(names in list) pulling the data I need from each one. The issue is I have no idea HOW to do this in VBA or an excel function.

the data being random is irrelevant, from where, to where and when you put data somewhere is what you need to program... the values are irrelevant unless they dictate what I just mentionaed and then you program the logic. Excel can perform consistent logic and queries on random data formatted in a consistent manner for the macro.

I was trying to say if you get a consistently formatted report you need to copy data from then that means it is not random. If Bob in accounting just pastes stuff wherever he feels like then that is random.

 

[ArtofExcel]

I have the from where. I know where I'd like it to go. My question is *how* can I put data from a single, consistent, cell in multiple sheets, the names and amount of which will be unknown, but will be equal to the number of names in a predetermined list, and put the data pulled from those sheets into a nice orderly table in a different sheet.

 

[ArtofExcel]

As in, what would an example of code be for pulling cell A1 in sheets 7-X (when x = 6 + Y (Y=number of names in a list) and putting that data in cells A1-A(Y) in a summary sheet. I have only cursory knowledge of vba so I have no idea what something like this might look like.