How can I copy data from one sheet to another sheet in Excel 2007 using a macro?

I am using MS Office 2007. How can I copy whole data from sheet one to sheet two? I want to copy the whole data from row 5 to onward in sheet two. The whole scenario is given below in de开发者_开发技巧tail.

Sheet one:

Copy the data from column B and Row 3

Sheet Two:

Paste the Copied Data in Column B and Row 3

Sheet One:

Copy the whole data from Column B to Column G and Row 5 to onward

Sheet Two:

Paste whole copied data in sheet two from last filled row to onward

Data doesn't overwrite on any row or column. Every data will be added in sheet two from sheet one when macro will be run.

The best way to learn how to do this is to record a macro. Excel has a very good macro recorder. Just start to record a new macro, then manually perform all the steps you describe in your question. After you have finished, stop the macro recorder and take a look at the code that Excel generated for you.

Alt+F11 is the shortcut key to get to the VBA code editor in Excel.

This should do the trick. But before you try, do a SaveAs to a different file, so if it doesn't give the desired results you can go back to the other version. There are no Undos with macros.

Sub CopyInfo()
    Sheet2.Range("B3").Value = Sheet1.Range("B3").Value
    Sheet1.Range("B5:G65000").Copy
    Sheet2.Range("B5").Paste
    Application.CutCopyMode = False
End Sub