Adaptable Copy/Paste to another Sheet

[Hobi]

Hey.

I've come across the common problem that is 'I know what I want to do but I don't know the proper term for searching'.

For this problem I have two sheets ("Source" and "Destination"), and a Command button to copy and paste.

The source data is F4:F198 on sheet "Source". I want it initially pasted to G2:G196 on sheet "Destination".

If G2:G196 is not empty, I want it to paste in H2:H196. If H2:H196 is not empty, then paste into I2:I196 and so on to end at Z2:Z196. Once the data has been successfully pasted once, the loop should end.

The goal is to create something where I can change the data in "Source" F4:F198 and press the button to paste to the next empty "Destination" range.

For bonus points: some basic explanation for what each line does would be appreciated.

Does this make sense? Thanks in advance for any help or advice.

 

[NdNoviceHlp]

Hi Hobi and Welcome to the Board! Seems like this will work providing you have data in row 2 of A to F columns (the LastCol part of the code requires it). The resize does the same as copy and paste for the range. HTH. Dave

Sub test()
Dim Rng As Range, LastCol As Integer
'F4:F198 on sheet "Source"
Set Rng = Sheets("sheet2").Range("F4:F198")
With Sheets("sheet1")
LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
.Cells(2, LastCol + 1).Resize(Rng.Rows.Count, _
            Rng.Columns.Count).Cells.Value = Rng.Cells.Value
End With
End Sub

 

[Hobi]

Hi Hobi and Welcome to the Board! Seems like this will work providing you have data in row 2 of A to F columns (the LastCol part of the code requires it). The resize does the same as copy and paste for the range. HTH. Dave

Sub test()
Dim Rng As Range, LastCol As Integer
'F4:F198 on sheet "Source"
Set Rng = Sheets("sheet2").Range("F4:F198")
With Sheets("sheet1")
LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
.Cells(2, LastCol + 1).Resize(Rng.Rows.Count, _
            Rng.Columns.Count).Cells.Value = Rng.Cells.Value
End With
End Sub

This works almost perfectly.

I had some buttons for functionality on the "destination" sheet and initially the code was dumping it to the right of that. Once I removed them, it worked as intended. I'll have to trial and error some new button locations to see where it won't interfere.

More importantly, I now know what definitely works and can do my own reading on things like LastCol and Resize.

Thank you for the help.

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Dave
edit: I see that I forgot to change the sheet names to what you had outlined. Source sheet is Sheet2 and Destination sheet is Sheet1. My apologies for any confusion.