Trying to build a loop for a repetitive but dynamic data

[PaulWilson]

Hi all,

I am trying to find a way to loop 2 sets of instructions to copy and paste data instead of having it written like I currently have below. The first set of instructions takes one range of data from a worksheet, copies, and pastes it into another workbook and the second set of instructions copy and pastes the second range of data into the same destination workbook.

Set wsCopy = Workbooks("1").worksheets(worksheets.Count - 2)
Set wsDest = Workbooks("2").worksheets("(2)")
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "B").End(xlUp).Row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "C").End(xlUp).Offset(1).Row
wsCopy.Range("B4:D" & lCopyLastRow).Copy _
wsDest.Range("C" & lDestLastRow)

Set wsCopy = Workbooks("1").worksheets(worksheets.Count - 2)
Set wsDest = Workbooks("2").worksheets("(2)")
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "I").End(xlUp).Row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "C").End(xlUp).Offset(1).Row
wsCopy.Range("I4:K" & lCopyLastRow).Copy _
wsDest.Range("C" & lDestLastRow)

Set wsCopy = Workbooks("1").worksheets(worksheets.Count - 1)
Set wsDest = Workbooks("2").worksheets("(2)")
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "B").End(xlUp).Row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "C").End(xlUp).Offset(1).Row
wsCopy.Range("B4:D" & lCopyLastRow).Copy _
wsDest.Range("C" & lDestLastRow)

Set wsCopy = Workbooks("1").worksheets(worksheets.Count - 1)
Set wsDest = Workbooks("2").worksheets("(2)")
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "I").End(xlUp).Row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "C").End(xlUp).Offset(1).Row
wsCopy.Range("I4:K" & lCopyLastRow).Copy _
wsDest.Range("C" & lDestLastRow)

Set wsCopy = Workbooks("1").worksheets(worksheets.Count)
Set wsDest = Workbooks("2").worksheets("(2)")
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "B").End(xlUp).Row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "C").End(xlUp).Offset(1).Row
wsCopy.Range("B4:D" & lCopyLastRow).Copy _
wsDest.Range("C" & lDestLastRow)

Set wsCopy = Workbooks("1").worksheets(worksheets.Count)
Set wsDest = Workbooks("2").worksheets("(2)")
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "I").End(xlUp).Row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "C").End(xlUp).Offset(1).Row
wsCopy.Range("I4:K" & lCopyLastRow).Copy _
wsDest.Range("C" & lDestLastRow)


What I am trying to accomplish in this code is to copy a dynamic selection of data from the last three sheets of workbook "1" to a single sheet in workbook "2".

I am unsure as to what to do and have just picked up VBA coding, so any insight/help would be greatly appreciated.

 

[johnnyL]

How about:

    Dim wsCopy          As Workbook
    Dim wsDest          As Workbook
'
    Set wsDest = Workbooks("2").Worksheets("(2)")
'
    For CopyCount = 2 To 0 Step -1
        Set wsCopy = Workbooks("1").Worksheets(Worksheets.Count - CopyCount)
'
        wsCopy.Range("B4:D" & wsCopy.Cells(wsCopy.Rows.Count, "B").End(xlUp).Row).Copy _
                wsDest.Range("C" & wsDest.Cells(wsDest.Rows.Count, "C").End(xlUp).Offset(1).Row)
'
        wsCopy.Range("I4:K" & wsCopy.Cells(wsCopy.Rows.Count, "I").End(xlUp).Row).Copy _
                wsDest.Range("C" & wsDest.Cells(wsDest.Rows.Count, "C").End(xlUp).Offset(1).Row)
    Next

 

[PaulWilson]

Instead of taking data from the last three sheets in the workbook, it is taking data from a random 3 in the middle. I will get back to you after I have done some trial and error.

 

[PaulWilson]

I found the issue:

The worksheets(worksheets.count) was taking the total number of sheets from the destination workbook instead of the copy workbook. Below are the additions in red:

Workbooks.Open (thisworkbook.Path & "\1")

Dim wsCopy As Workbook
Dim wsDest As Workbook
'
Workbooks.Open (thisworkbook.Path & "\1")
Set wsDest = Workbooks("2").Worksheets("(2)")
'
For CopyCount = 2 To 0 Step -1
workbooks("1").activate
'
Set wsCopy = Workbooks("1").Worksheets(Worksheets.Count - CopyCount)
'
wsCopy.Range("B4:D" & wsCopy.Cells(wsCopy.Rows.Count, "B").End(xlUp).Row).Copy _
wsDest.Range("C" & wsDest.Cells(wsDest.Rows.Count, "C").End(xlUp).Offset(1).Row)
'
wsCopy.Range("I4:K" & wsCopy.Cells(wsCopy.Rows.Count, "I").End(xlUp).Row).Copy _
wsDest.Range("C" & wsDest.Cells(wsDest.Rows.Count, "C").End(xlUp).Offset(1).Row)
Next


Thank you for your help!

 

[johnnyL]

I assumed the partial code you posted was working.

Glad to help.