Macro copy paste destination cell

[timjohnny]

Hi all,

with some great help from Fluff I was able to copy paste from one sheet to another using the following macro:

Sub timjohnny()
   Dim i As Long
   
   With Sheets("Rewards Structure")
      For i = 2 To 800
         .Range("A" & i).Resize(, 3).Copy Sheets("Index").Cells(4, i * 3 - 1)
      Next i
   End With
End Sub

Now I want to basically repeat this, but from a different origin sheet (but to the same destination sheet. But I need to specify the destination cell in the destination sheet as the cell in row 4 and the column two down from that into which the above code pastes. So basically, I want to repeat the above macro with a different origin sheet but make sure that I'm pasting into column c+2, where c=last column the above code pastes into.

Could anyone help me with that?

Thanks!

 

[johnnyL]

I haven't tested this, but I think it does what you asked for, just change the sheet names accordingly if need be:

Sub timjohnnyV2()
'
    Dim i           As Long
    Dim Destination As Worksheet
    Dim Source      As Worksheet
'
    Set Destination = Sheets("Index")                                               ' <--- Set this to the sheet name that you want to copy the data to
    Set Source = Sheets("Rewards Structure")                                        ' <--- Set this to the sheet name that you want to copy the data from
'
    NextColumnNumberInRowToWriteTo = Destination.Rows(4).Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column + 2 ' Returns a Column Number
'
    With Source
        For i = 2 To 800
            .Range("A" & i).Resize(, 3).Copy Destination.Cells(4, i * 3 - 1 + NextColumnNumberInRowToWriteTo)
        Next i
    End With
End Sub

 

[timjohnny]

Thanks a lot johnnyL! After loooking at your code and seeing that and where I need to add the column number

.Range("A" & i).Resize(, 3).Copy Destination.Cells(4, i * 3 - 1 + NextColumnNumberInRowToWriteTo)

I ended up just finding out the relevant column number using =columnn() in the destination sheet. This works in this case because I don't have a dynamic range (because in Fluff's formula i = 2 To 800), but I will keep your code on file for when I have a dynamic range!

Thanks again and all the best!

 

[johnnyL]

Your welcome @timjohnny