Why copy but not paste?

[Flintstone]

I’m trying to copy 16 cells located in various places on sheet1.

A1
B6:G6, I6
B9:H9, J9

Paste them to the last row on sheet2.

A1:P1

Can this be done. If not why?

[Leith Ross]

Hello Flintstone,

Because both sheets are in the same workbook, you don't even need to use the copy method. I am assuming the order you show on worksheet 1 is the same order used to fill the range A1 to P1 on worksheet 2. Also, I am assuming "last row" refers to the next blank row on worksheet 2. I am sure you will correct me if any of my assumptions are incorrect. I have written the code as a macro. Here is the code...


Public Sub TransferData()

Dim N As Long
Dim Wks1 As Worksheet
Dim Wks2 As Worksheet

'Assign references to object variables
Set Wks1 = Excel.Worksheets("Sheet1")
Set Wks2 = Excel.Worksheets("Sheet2")

'Find the next empty row on worksheet 2
N = Wks2.Cells(Rows.Count, 1).End(xlUp).Row + 1

'Directly assign cell values from worksheet to the other
Wks2.Range("A" & N).Value = Wks1.Range("A1").Value
Wks2.Range("B" & N &":G" & N).Value = Wks1.Range("B6:G6").Value
Wks2.Range("H" & N).Value = Wks1.Range("I6").Value
Wks2.Range("I" & N &":O" & N).Value = Wks1.Range("B9:H9").Value
Wks2.Range("P" & N).Value = Wks1.Range("J9").Value

End Sub

Cut and paste this code into a VBA project module to use it as a macro, or cut and paste the code into your code where you need it. If you have any questions or problems, contact me via e-mail at [email protected].

Sincerely,
Leith Ross

[Flintstone]

Thank you for your response and the salution. It's clear I have a lot more learn.