Copy Multiple Dynamic Named Ranges into one Worksheet

[Carin]

I have 13 workbooks with an OFFSET code in the Refers to section "=OFFSET(Sheet1!$A$1:$G$1,0,0,COUNTA(Sheet1!A:A),7)" The named range for all 13 workbooks is "MyRange." I wanted to run a macro that would go to all 13 workbook, copy the range and paste onto my "ZMaster" workbook, sheet 1. I'm trying to have a consolidate sheet for all of the data from the 13 workbooks without having to manually copy and paste everyday. Thank you

 

[markmzz]

Carin,

Try this code:

Sub CopyData()
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Name <> "Plan1" Then
            If Worksheets("Plan1").Range("A1").Value = "" Then
                ws.Range("MyRange").Copy _
                    Worksheets("Plan1").Range("A1")
            Else
                LastRow = Worksheets("Plan1").Range("A" & Rows.Count).End(xlUp).Row
                ws.Range("MyRange").Offset(1, 0).Copy _
                    Worksheets("Plan1").Range("A" & LastRow + 1)
            End If
        End If
    Next ws
End Sub

Markmzz

 

[Carin]

I'm sure what you gave me is perfect but I'm trying to figure it out...at what point does this paste? I'm assuming I would have to create the macro to open each workbook then use your macro 13 times? I'm confused. All the sheets are in a different workbook.

 

[markmzz]

I'm sure what you gave me is perfect but I'm trying to figure it out...at what point does this paste? I'm assuming I would have to create the macro to open each workbook then use your macro 13 times? I'm confused. All the sheets are in a different workbook.

Carin,

I'm Sorry. I read worksheet and not workbook. I'll review the code.

Markmzz

 

[markmzz]

Carin,

Try this code (for 3 workbooks-worksheets Pasta1 - Plan1, Pasta2 - Plan1 e Pasta3 - Plan1 and more the Master - Plan1 workbook/worksheet:

[COLOR=blue]Sub CopyData()[/COLOR]
[COLOR=blue] Dim Masterwk, my3wk As Workbook[/COLOR]
[COLOR=blue] Dim Masterws, my3ws As Worksheet[/COLOR]
[COLOR=blue] Dim i As Integer[/COLOR]
 
[COLOR=blue] Set Masterwk = ActiveWorkbook[/COLOR]
[COLOR=blue] Set Masterws = Masterwk.Worksheets("Plan1")[/COLOR]
 
[COLOR=blue] Masterws.Activate[/COLOR]
 
[COLOR=blue] For i = 1 To 3[/COLOR]
[COLOR=blue]     Workbooks.Open ThisWorkbook.Path & "\Pasta" & i[/COLOR]
 
[COLOR=blue]     Set my3wk = ActiveWorkbook[/COLOR]
[COLOR=blue]     Set my3ws = my3wk.Worksheets("Plan1")[/COLOR]
 
[COLOR=blue]     my3ws.Activate[/COLOR]
 
[COLOR=blue]     If Masterws.Range("A1").Value = "" Then[/COLOR]
[COLOR=blue]         my3ws.Range("MyRange").Copy _[/COLOR]
[COLOR=blue]             Masterws.Range("A1")[/COLOR]
[COLOR=blue]         Masterwk.Activate 'Test[/COLOR]
[COLOR=blue]     Else[/COLOR]
[COLOR=blue]         LastRow = Masterws.Range("A" & Rows.Count).End(xlUp).Row[/COLOR]
[COLOR=blue]         my3ws.Range("MyRange").Offset(1, 0).Copy _[/COLOR]
[COLOR=blue]             Masterws.Range("A" & LastRow + 1)[/COLOR]
[COLOR=blue]         Masterwk.Activate 'Test[/COLOR]
[COLOR=blue]     End If[/COLOR]
[COLOR=blue]     Application.CutCopyMode = False[/COLOR]
[COLOR=blue]     my3wk.Close SaveChanges:=False[/COLOR]
[COLOR=blue] Next i[/COLOR]
 
[COLOR=blue] Set my3ws = Nothing[/COLOR]
[COLOR=blue] Set my3wk = Nothing[/COLOR]
[COLOR=blue] Set Masterws = Nothing[/COLOR]
[COLOR=blue] Set Masterwk = Nothing[/COLOR]
 
[COLOR=blue]End Sub[/COLOR]

Markmzz