Copy Colum and Paste Special to Colum to Right - Incrementally

[robbydogg]

Hello,

Could anyone help me with this one?
I wans to copy all data from column H, then Past it as values to Column I, then next time i press the button for column H data to paste specail to Column J and so on - so it keeps adding the data to the next column automatically.
Can anyone help me with the macro code for this?

Thanks
Rob

 

[jbeaucaire]

Try this macro:

Sub CopyRowH()
Dim lastrow As Long
Dim nextcol As Long

lastrow = Range("H65000").End(xlUp).Row
nextcol = Range("H1").End(xlToRight).Offset(0, 1).Column

Application.ScreenUpdating = False
Application.EnableEvents = False
    
    Range("H1:H" & lastrow).Copy
    Cells(1, nextcol).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Range("H1").Select

Application.ScreenUpdating = True
Application.EnableEvents = Trur

End Sub

 

[robbydogg]

great - works perfectly. thanks

 

[robbydogg]

aaargh - now for some reason it gives me "Runtime Error 1004" can anyone help?

 

[jbeaucaire]

Sorry, try this instead. My bad.

Sub CopyRowH()
Dim lastrow As Long
Dim nextcol As Long

lastrow = Range("H65000").End(xlUp).Row
nextcol = Range("IQ1").End(xlToLeft).Offset(0, 1).Column

Application.ScreenUpdating = False
Application.EnableEvents = False
    
    Range("H1:H" & lastrow).Copy
    Cells(1, nextcol).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Range("H1").Select

Application.ScreenUpdating = True
Application.EnableEvents = Trur

End Sub

 

[robbydogg]

hi, thanks again for that. works a treat!!!
(now watch someone delete the button i assigned it to )