When we are dealing with many worksheet, it is a routine thing to copy data from one worksheet to another in Excel VBA. For example, we may automate a task which required to get the data from differen worksheets (some times different workbooks). In this situation, we need to copy the some part the worksheet and paste it in a target worksheet.
We can use Copy method of a range to copy the data from one worksheet to another worksheet.
The following example will show you copying the data from one sheet to another using Excel VBA.
'In this example I am Copying the Data from Sheet1 (Source) to Sheet2 (Destination)
'Copy the data
'Activate the destination worksheet
'Select the target range
'Paste in the target destination
Application.CutCopyMode = False
- Open an excel workbook
- Enter some data in Sheet1 at A1:B10
- Press Alt+F11 to open VBA Editor
- Insert a Module for Insert Menu
- Copy the above code and Paste in the code window
- Save the file as macro enabled workbook
- Press F5 to run it
Now you should see the required data (from sheet1) is copied to the target sheet (sheet2).
We can use two methods to copy the data:
Method 1: In this method, we do not required to activate worksheet. We have to mention the source and target range. This is the simple method to copy the data.
Method 2: In this method, we have to activate the worksheet and paste in a range of active worksheet.
The main difference between two methods is, we should know the destination worksheet name in the first method, in second method we can just activate any sheet and paste it.
Download the Example Macro Workbook:
Download the Example VBA Macro File and Explore the code example to copy the data from one sheet to another worksheet:
More Macros on Copying Data Using VBA: