Description:

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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

BROWSE ALL TEMPLATES

50+ Project Management Templates Pack
Excel PowerPoint Word

VIEW DETAILS

Advanced Project Plan & Portfolio Template
Excel Template

VIEW DETAILS

Ultimate Project Management Template
Excel Template

VIEW DETAILS

20+ Excel Project Management Pack
Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack
PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack
Word Templates

VIEW DETAILS


Copy Data from one Worksheet to Another in Excel VBA – Solution(s):

Copy data from one Worksheet to anotherWe can use Copy method of a range to copy the data from one worksheet to another worksheet.

Copy Data from one Worksheet to Another in Excel VBA – An Example

The following example will show you copying the data from one sheet to another using Excel VBA.

Code:
'In this example I am Copying the Data from Sheet1 (Source) to Sheet2 (Destination)
Sub sbCopyRangeToAnotherSheet()

'Method 1
Sheets("Sheet1").Range("A1:B10").Copy Destination:=Sheets("Sheet2").Range("E1")

'Method 2
'Copy the data
Sheets("Sheet1").Range("A1:B10").Copy
'Activate the destination worksheet
Sheets("Sheet2").Activate
'Select the target range
Range("E1").Select
'Paste in the target destination
ActiveSheet.Paste

Application.CutCopyMode = False

End Sub
Instructions:
  1. Open an excel workbook
  2. Enter some data in Sheet1 at A1:B10
  3. Press Alt+F11 to open VBA Editor
  4. Insert a Module for Insert Menu
  5. Copy the above code and Paste in the code window
  6. Save the file as macro enabled workbook
  7. Press F5 to run it

Now you should see the required data (from sheet1) is copied to the target sheet (sheet2).

Explanation:

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:

Copy Data Form One Sheet To Another Sheet

More Macros on Copying Data Using VBA:

LIMITED TIME OFFER - Get it Now!
Advanced Project Plan Excel Template

 
Related Resource External VBA Reference