move cell to next column

[ADZEAN83]

Hi,

I'm new to using macros and VBA in excel. I am trying to store data in another worksheet but it does not automatically move to the next column. I want to store incrementally to the right of the last column with data on the second sheet.

my macro code below as follows:
===

Sub Macro6()
'
' Macro6 Macro

Columns("A:E").Select ' select columns a to e in sheet1
Selection.Copy ' copy data in the columns
Sheets("Sheet2").Select ' SELECT SHEET 2
Range("A1").Select ' SELECT CELL A1 IN SHEEET 2
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False 'paste special
Sheets("Sheet1").Select 'go back to a1 sheet 1

' at this portion i am lost as to what should happen next.

End Sub
===
what i wanna happen is have a store button with this macro which will store incrementally in sheet2 moving to the right. when i click the button, macro should not overlap but should store to the right column after the last data stored.

thanks!

I am using window xp with office 2007.

 

[VoG]

Try this

Sub test()
Sheets("Sheet1").Columns("A:E").Copy
Sheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial Paste:=xlPasteValues
End Sub

 

[ADZEAN83]

PERFECT! thanks!