Copy and Paste

[RockStar75]

Hello all,

Not being a total expert with Excel 2010 / VBA I am struggling to create a simple macro that copies an active row from one sheet to the next available row in a different sheet. I keep getting stuck on the active row bit and feel that I have over complicated things.

Would anyone be able to provide me with a simple code that will perform the operation above.

Thank you very much in advance!

Chris

 

[VoG]

Try like this

Sub cpy()
ActiveCell.EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub

 

[RockStar75]

Hi Peter!!

Thank you so much...that is so much easier than what I was trying to construct!

Just one additional question...the code you presented copies and pastes the row from in Sheet 1 to the exact same location in Sheet 2. How would the code differ if I wanted to copy the active row from any location in Sheet 1 to the next available row in column A in Sheet 2?

Regards,

Chris

 

[VoG]

That should paste to a new row each time provided that column A is not blank.

 

[RockStar75]

Hmmmm...I thought that would be the case as well; however, it's working in such a way that it's copying to the exact same location in Sheet 2.

I was also wondering, is there a way to copy and paste only the values to the new sheet?

Regards,

Chris

 

[VoG]

For the values only try

Sub cpy()
ActiveCell.EntireRow.Copy
Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End Sub

 

[RockStar75]

Again, thanks!

This time a new issue came up...instead of copying just my selection, it is actually copying and pasting the entire row...all the way from A to ZZ. Very strange!

Regards,

Chris

 

[VoG]

If you just want the selection try

Sub cpy()
Selection.Copy
Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End Sub

 

[RockStar75]

Yah...I was just about to reply and mention that I had changed the first line to "Selection.Copy"...but you beat me to it! : )

Again, thank you very much...your assistance is much appreciated! : )

Regards,

Chris