how to copy data from sheet 1 to sheet 2 using macro

[Messenger]

I have two excel worksheets. What I want to do is to copy the content from sheet 1 and paste it into sheet 2. The copy button 'Macro' should not be fixed to copy only constant, it should be vibrant means if I add some more rows or columns in sheet1 and click copy button then it should paste all the rows and column in sheet2. Please give me an idea of looping concept in VBA, as I am new to it I also bought Bill Jelen book of VBA and Macros for MS office Excel 2007.

[Eric B]

According to me it can be done using a simple macro. There is no need of using looping concept. Following is the code given below.

Sub test()
Worksheets("sheet2").Cells.Copy
Worksheets("sheet1").Range("a1").PasteSpecial
End Sub

introduce a button in sheet 2 from toolbar and allocate this macro to the button and check whether it is working or not.

[Glenny]

Its a easiest way to do this, but I think this is not a first-class idea because its copying all whole sheet1. If I have have only 2 columns and I want to copy only these columns and rows which have data. So the loop has to search column wise and row wise and when it reaches the vacant cell (means last row with no records ) the loop should exit and copy all the data from sheet 1 to sheet 2.

[Macarenas]

I am copying Sht2 Cells A1,A2,A3,B1,B2,B3 to Sht1

A1 is Name1
A2 is Date/Time
A3 is Name2
B1 is Value1
B2 is Value2
B3 is Result.

Copy to Sheet1
A1 is Sht2.Name1
A2 is Sht2.Value1
A3 is Sht2.Value2
A4 is Sht2.Date/Time

just inserted the formula manually in each cell of the sheet.