none
Copy a dynamic range of sheets and paste in multiple sheets of another workbook vba RRS feed

  • Question

  • The code below was recommended for copying data from a workbook to another workbook where sheets have the same name in both. I need to modify it to only copy some of the sheets, not all. For example, the source and target workbooks each have 50 sheets named Product1 - Product50, but source workbooks are coming from different users.  In once case I'll copy Sheets Product10 - Product25, in another I'll copy Product35-Product42.  I think I need an array, but I can't figure out how to define the start and end for it.

    Thank you

    Sub FromThis2That()
        Dim SourceBook As Workbook
        Dim TargetBook As Workbook
        Dim sh As Worksheet
       
        Set SourceBook = ThisWorkbook
        Set TargetBook = Workbooks("TargetBook")
       
        For Each sh In SourceBook.Worksheets
            sh.Range("A2:AD166").Copy
            TargetBook.Worksheets(sh.Name).Range("A2").PasteSpecial
        Next sh
    End Sub

    Tuesday, February 9, 2016 2:32 PM

Answers

  • Sorry - I edited the code in my browser, so I forgot to change Next sh to Next i.  This will work on a list in column A of NewSheet, starting in row 2:

    Sub FromThis2ThatV2()
        
        Dim sh As Worksheet
        Dim SourceBook As Workbook
        Dim TargetBook As Workbook
        Dim i As Long
        
        Set SourceBook = ThisWorkbook
        Set TargetBook = Workbooks("TargetBook")
        
        For i = 2 To Sheets("NewSheet").Cells(Rows.Count, "A").End(xlUp).Row
            Set sh = SourceBook.Worksheets(Sheets("NewSheet").Cells(i, "A").Value)
            sh.Range("F20:W34").Copy
            TargetBook.Worksheets(sh.Name).Range("F20").PasteSpecial
        Next i
        
    End Sub

    • Marked as answer by Consultant64 Wednesday, February 10, 2016 4:31 PM
    Wednesday, February 10, 2016 3:46 PM

All replies

  • If you know the specific range of sheets, you could use

    Sub FromThis2That()
        Dim SourceBook As Workbook
        Dim TargetBook As Workbook
        Dim sh As Worksheet

        Dim i As Integer
        
        Set SourceBook = ThisWorkbook
        Set TargetBook = Workbooks("TargetBook")
        
        For i = 10 To 25

            Set sh = SourceBook.Worksheets(i)
            sh.Range("A2:AD166").Copy
            TargetBook.Worksheets(sh.Name).Range("A2").PasteSpecial
        Next i
    End Sub

    If you don't know or want to vary the sheets that you use, select the sheets in SourceBook before running this version

    Sub FromThis2That()
        Dim SourceBook As Workbook
        Dim TargetBook As Workbook
        Dim sh As Worksheet
        
        Set SourceBook = ThisWorkbook
        Set TargetBook = Workbooks("TargetBook")
        
        For Each sh In SourceBook.Windows(1).SelectedSheets
            sh.Range("A2:AD166").Copy
            TargetBook.Worksheets(sh.Name).Range("A2").PasteSpecial
        Next sh
    End Sub


    Tuesday, February 9, 2016 3:16 PM
  • Thanks so much for your help Bernie!  I am sorry that I forgot to mention that the returns are not necessarily on consecutive tabs; so I could get Product5-Product10, Product14, Product24.
    Tuesday, February 9, 2016 6:45 PM
  • I'm guessing you want the specific number version, not the selectedsheets version:

    Sub FromThis2That()
        Dim SourceBook As Workbook
        Dim TargetBook As Workbook
        Dim sh As Worksheet

        Dim i As Variant
        
        Set SourceBook = ThisWorkbook
        Set TargetBook = Workbooks("TargetBook")
        
        For Each i In Array(5, 6, 7, 8, 9, 10, 14, 24)
            Set sh = SourceBook.Worksheets("Product" & i) 'Just in case they are not in order
            sh.Range("A2:AD166").Copy
            TargetBook.Worksheets(sh.Name).Range("A2").PasteSpecial
        Next i
    End Sub




    Tuesday, February 9, 2016 7:09 PM
  • If I had a list of the worksheet names that I want to copy on another sheet in the source workbook . . . the list is dynamic, sometimes it's a list of 5 sheet names, sometimes it's 12, how can I use that list to feed the array parameters?

    Tuesday, February 9, 2016 10:07 PM
  • If it is a dynamic named range (like  =OFFSET(Sheet1!A1,1,0,COUNTA(Sheet1!A:A)-1,1)   ), then you could use code like this

        For i = 1 To Range("DynamicNamedRange").Cells.Count
            Set sh = SourceBook.Worksheets(Range("DynamicNamedRange").Cells(i).Value
            sh.Range("A2:AD166").Copy
            TargetBook.Worksheets(sh.Name).Range("A2").PasteSpecial
        Next i

    You could also use code like this for a list in column A starting at row 2:

        For i = 2 To Sheets("SheetName").Cells(Rows.Count,"A").End(xlUp).Row
            Set sh = SourceBook.Worksheets(Sheets("SheetName").Cells(i,"A").Value
            sh.Range("A2:AD166").Copy
            TargetBook.Worksheets(sh.Name).Range("A2").PasteSpecial
        Next i

    Tuesday, February 9, 2016 10:16 PM
  • Thanks Bernie.  I tried to run the code using your second recommendation (below) and this is the error I get:

    Compile error:  Invalid Next control variable reference

    and sh in "Next sh" is highlighted.

    Sub FromThis2That()

    Dim sh As Worksheet
    Dim SourceBook As Workbook
    Dim TargetBook As Workbook

    Set SourceBook = ThisWorkbook
    Set TargetBook = Workbooks("TargetBook")

    For i = 2 To Sheets("NewSheet").Cells(Rows.Count, "A").End(xlUp).Row
            Set sh = SourceBook.Worksheets(Sheets("NewSheet").Cells(i, "A").Value)
            sh.Range("F20:W34").Copy
            TargetBook.Worksheets(sh.Name).Range("F20").PasteSpecial
        Next sh
       
    End Sub

    I got this to run when I keyed the sheet names into the macro, but I'd like it to pick them up from the NewSheet tab since I have a function specific to each workbook that puts the right sheet names there.

    Sub CopySheetData()

    Dim sh As Worksheet
    Dim SourceBook As Workbook
    Dim TargetBook As Workbook

    Set SourceBook = ThisWorkbook
    Set TargetBook = Workbooks("TargetBook")

    For Each sh In Worksheets(Array("Product12", "Product151", "ProductAB4", "ProductINT", "ProductOTC"))
            sh.Range("F20:W34").Copy
            TargetBook.Worksheets(sh.Name).Range("F20").PasteSpecial
        Next sh

    End Sub

    Thanks again for your help, I appreciate it.

    Wednesday, February 10, 2016 2:58 PM
  • Sorry - I edited the code in my browser, so I forgot to change Next sh to Next i.  This will work on a list in column A of NewSheet, starting in row 2:

    Sub FromThis2ThatV2()
        
        Dim sh As Worksheet
        Dim SourceBook As Workbook
        Dim TargetBook As Workbook
        Dim i As Long
        
        Set SourceBook = ThisWorkbook
        Set TargetBook = Workbooks("TargetBook")
        
        For i = 2 To Sheets("NewSheet").Cells(Rows.Count, "A").End(xlUp).Row
            Set sh = SourceBook.Worksheets(Sheets("NewSheet").Cells(i, "A").Value)
            sh.Range("F20:W34").Copy
            TargetBook.Worksheets(sh.Name).Range("F20").PasteSpecial
        Next i
        
    End Sub

    • Marked as answer by Consultant64 Wednesday, February 10, 2016 4:31 PM
    Wednesday, February 10, 2016 3:46 PM
  • Perfect!  THANK YOU BERNIE!
    Wednesday, February 10, 2016 4:31 PM