Help with code that copies data to a second worksheet, but in a different order

[Not_Really_Me]

Hi all,
I'm trying to copy data from the data entry worksheet (Sheet1) to a second, data record worksheet (Sheet2), but I'm not that strong of a programmer so I've mostly been cobbling together different examples. The code below works well, except that I need the content in column I to be pasted at the end but it is always pasted between G and M.


Private Sub CommandButton1_Click()

lastRow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

For r = 4 To lastRow
If Worksheets("Sheet1").Range("A" & r).Value <> "" Then
Worksheets("Sheet1").Range("A" & r & ":G" & r & ",M" & r & ":S" & r & ",I" & r).Copy

Worksheets("Sheet2").Activate
lastRowRpt = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("Sheet2").Range("A" & lastRowRpt + 1).Select

ActiveSheet.Paste

End If

Next r

End Sub

There are also several areas where I need to jump back and forth in the order in which column content is pasted over (e.g., A:M, I, O:AO, N, etc.) Is it possible to do this?

Thanks!

 

[JLGWhiz]

Have you tried using the recorder to record your keystrokes when you do it manually? You can then 'clean up' the recorded code and edit it to make it more useful.

 

[Not_Really_Me]

No, I'm not familiar with that - can you recommend a good guide?

 

[JLGWhiz]

There are many more on line tutorials, but this is a good place to start.

How to Record and Test Excel Macro

How to record and run an Excel macro, for absolute beginners. Video and written step-by-step instructions. Get started with Excel programming.

www.contextures.com