Loop Through Destination Workbook's worksheets while pasting cell values from another Workbook

[ksillas]

Ok so my goal is this: I have generated data in the U1:V5 fields in a workbook called ORIGIN (which has 30 sheets) and I want to paste the following in a workbook called DESTINATION (which has 2 sheets):

The sheet names in the ORIGIN sheet go from 101 to 129 the data is always in the U1:V5 range.
The sheet names in the DESTINATION sheet are V1 (V1 contains the data from U1:U5) and V2 (contains data from V1:V5)


This loop is for the "V1" sheet in DESTINATION

ORIGIN U1 goes in DESTINATION B7
ORIGIN U2 goes in DESTINATION D7
ORIGIN U3 goes in DESTINATION F7
ORIGIN U4 goes in DESTINATION H7
ORIGIN U5 goes in DESTINATION J7

This applies for each sheet in origin so for the next sheet B7 will be B8 and so on. Origin stays the same.

This loop is for the "V2" sheet in DESTINATION

ORIGIN V1 goes in DESTINATION B7
ORIGIN V2 goes in DESTINATION D7
ORIGIN V3 goes in DESTINATION F7
ORIGIN V4 goes in DESTINATION H7
ORIGIN V5 goes in DESTINATION J7

This applies for each sheet in origin so for the next sheet B7 will be B8 and so on. Origin stays the same.


Now. Here is the start of my code, but I can't seem to get it working, been trying for 2 days to populate 1 of the cells with an "i" counter.. I've tried For Loops, and Do whiles, getting frustrated now

Sub CopyPaster()
Dim x As Workbook
Dim y As Workbook
Dim i As Long
Dim ws_num As Integer


'## Open both workbooks first:
Set x = Workbooks.Open("C:\PATH\ORIGIN.xlsx")
Set y = Workbooks.Open("C:\PATH\DESTINATION.xlsx")


'Now, transfer values from x to y:


For i = 101 To 116
    'ThisWorkbook.Worksheets(i).Activate
    y.Sheets("V1").Range("B7").Value = x.Sheets(i).Range("U1")
    i = i + 1
Next i


'Close x:
'x.Close

This is supposed to start filling U1. It opens the documents correctly but doesn't fill any cells.

Thank you for your help guys, I'm trying to learn but I'm stumped.

 

[John_w]

Try this:

Public Sub Copy_Origin_To_Destination()

    Dim originWb As Workbook
    Dim destinationWb As Workbook
    Dim originWs As Worksheet
    Dim n As Long
    
    Set originWb = Workbooks.Open("C:\PATH\ORIGIN.xlsx")
    Set destinationWb = Workbooks.Open("C:\PATH\DESTINATION.xlsx")

    n = 0
    For Each originWs In originWb.Worksheets
        With destinationWb.Worksheets("V1")
            .Range("B7").Offset(n).Value = originWs.Range("U1").Value
            .Range("D7").Offset(n).Value = originWs.Range("U2").Value
            .Range("F7").Offset(n).Value = originWs.Range("U3").Value
            .Range("H7").Offset(n).Value = originWs.Range("U4").Value
            .Range("J7").Offset(n).Value = originWs.Range("U5").Value
        End With
        With destinationWb.Worksheets("V2")
            .Range("B7").Offset(n).Value = originWs.Range("V1").Value
            .Range("D7").Offset(n).Value = originWs.Range("V2").Value
            .Range("F7").Offset(n).Value = originWs.Range("V3").Value
            .Range("H7").Offset(n).Value = originWs.Range("V4").Value
            .Range("J7").Offset(n).Value = originWs.Range("V5").Value
        End With
        n = n + 1
    Next
    
    destinationWb.Close SaveChanges:=True
    originWb.Close SaveChanges:=False

End Sub

 

[ksillas]

This worked BEAUTIFULLY John_w! Now that I see it it's a little bit more obvious, I believe I understand it now. Thank you!!