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.

 

[cerfani]

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.

if i follow correctly then this...

1. you want to copy cell A1 from worksheet index 7 to x (example last 4 sheets would be x = 10)
2. you want to paste each sheet's copied value on a summary sheet starting at A1 going down (A1 = Sheet7!A1, A2 = Sheet8!A1, etc)

Dim y As Long, j as Long, i As Long, lastSheetIndex As Long
y = WorksheetFunction.Counta(theListRange) 'define your range here as a range object
lastSheetIndex = 6 + y
j = 1
For i = 7 To lastSheetIndex 'make sure the sheets you read from start at Tab #7 and are followed by all other sheets to read from... summary sheet should be before the 7th sheet
    ThisWorkbook.Worksheets("Summary").Cells(j, 1).Value2 = ThisWorkbook.Sheets(i).Cells(1, 1).Value2
    j = j + 1
Next i

that would do what you just described

 

[cerfani]

make sure to check latest edit for previous post, (i edited a few times)

 

[ArtofExcel]

That last edit got it! Thank you! Exactly what I was Looking for.

 

[cerfani]

great! no prob!