Dynamic Reference to Worksheets

[MikeG]

I have a range (5 rows by one column) that lists the names of several worksheets:


Prod1
Prod2
Prod7
Prod3
Prod4

The range name is Sheets_List.

I would like to have a simple macro that loops through each worksheet in turn, each time copying the range from Cells(1,1) to Cells(1,y), where y will be defined at the top of the macro.

The copied ranges will be pasted in one list in worksheet "Summary", starting at Cells(2,3).

Could someone help me with the macro?

Thanks,

MikeG

 

[Gary McMaster]

Hi Mike,

Not sure what you are looking for as an output. Am I close?

Gary

In a standard module:

Public Sub Test()

Dim y As Long
Dim oSheetName As Range
Dim oSheetList As Range
Dim oCopyFrom As Range
Dim oCopyTo As Range

y = 7

'Assumes named range "Sheets_List" exists on sheet named "Summary"
Set oSheetList = ThisWorkbook.Worksheets("Summary").Range("Sheets_List")
Set oCopyTo = ThisWorkbook.Worksheets("Summary").Range("C2")


For Each oSheetName In oSheetList
    Set oCopyFrom = Worksheets(oSheetName.Text).Range("A1").Resize(1, y)
    oCopyFrom.Copy oCopyTo
    Set oCopyTo = oCopyTo.Offset(1, 0)
Next oSheetName

End Sub

 

[Norie]

Do you want the data from the sheets copied down?

eg copy data from Prod1 then below that data from Prod2, then Prod7 and so on

 

[MikeG]

Hi Mike,

Not sure what you are looking for as an output. Am I close?

Gary

In a standard module:

Public Sub Test()

Dim y As Long
Dim oSheetName As Range
Dim oSheetList As Range
Dim oCopyFrom As Range
Dim oCopyTo As Range

y = 7

'Assumes named range "Sheets_List" exists on sheet named "Summary"
Set oSheetList = ThisWorkbook.Worksheets("Summary").Range("Sheets_List")
Set oCopyTo = ThisWorkbook.Worksheets("Summary").Range("C2")


For Each oSheetName In oSheetList
    Set oCopyFrom = Worksheets(oSheetName.Text).Range("A1").Resize(1, y)
    oCopyFrom.Copy oCopyTo
    Set oCopyTo = oCopyTo.Offset(1, 0)
Next oSheetName

End Sub

Gary:

Thanks - just what I was looking for.

Mike

 

[MikeG]

Do you want the data from the sheets copied down?

eg copy data from Prod1 then below that data from Prod2, then Prod7 and so on

Norie:

Thanks, - I'm covered by Gary's macro, but appreciate your reply.

 

[MikeG]

Gary,

One question out of interest.

You have the line...

oCopyFrom.Copy oCopyTo

I had not come across this syntax. I assume it means "copy oCopyFrom and then put this value into oCopyTo". Just wanted to double check. Just having the space was what I haven't seen before.

So if I was using Range, could I do...

Range("A1").copy Range("B1")

Thanks,

Mike

 

[Gary McMaster]

So if I was using Range, could I do...

Range("A1").copy Range("B1")

Yes, I believe that would work but to be on the safe side and to help someone else who may have to modify the code later better understand what's happening, I prefer to fully qualify the range by prefixing it with the sheet name and possibly even the workbook name. From the code sample:

Set oCopyTo = ThisWorkbook.Worksheets("Summary").Range("C2")
Set oCopyFrom = Worksheets(oSheetName.Text).Range("A1").Resize(1, y)

or in your example:

Activesheet.Range("A1").copy Activesheet.Range("B1")

Glad it was what you needed. Thanks for the feedback.

Gary

 

[MikeG]

Yes, I believe that would work but to be on the safe side and to help someone else who may have to modify the code later better understand what's happening, I prefer to fully qualify the range by prefixing it with the sheet name and possibly even the workbook name. From the code sample:

Set oCopyTo = ThisWorkbook.Worksheets("Summary").Range("C2")
Set oCopyFrom = Worksheets(oSheetName.Text).Range("A1").Resize(1, y)

or in your example:

Activesheet.Range("A1").copy Activesheet.Range("B1")

Glad it was what you needed. Thanks for the feedback.

Gary

Thanks again.

Mike