Copy Data from one Worksheet to Another in Excel VBA

Copy Data from one Worksheet to Another in Excel VBA
Previous Next
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.

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

We 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:

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).

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 file to download: http://analysistabs.com/download/copy-data-one-sheet-another/

By Valli|June 14th, 2013|Excel VBA|63 Comments
Share This Story, Choose Your Platform!

About the Author: Valli
Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing useful VBA examples ad Tips to helps us automating daily tasks.
Related Posts
63 Comments
Ron Brown December 4, 2013 at 10:40 PM - Reply
I am trying to copy specific ranges from 12 worksheets on from the “Source” workbook to the same specific ranges on 12 worksheets to the “Target” workbook. How can I set up a “loop” to accomplish this in a VBA code? Thanks!

– Ron –
PNRao December 4, 2013 at 11:05 PM - Reply
‘If your input files are always same then store them in an Array
mySource=Array(“File1.xlsx”,”File1.xlsx”,…,”File12.xlsx”)

‘If they are not same always then read your workbook names and store it in an Array (mySource)

'open each workbook

For iCntr=1 to 12
set sourceWB=Workbooks.Open(mySource(iCntr-1)) ' since array starts from 0
sourceWB.Sheets("YourSheetName").Range("YourRange").copy ThisWorkbook.Sheets("YourTargetSheet").Range("TargetRange")
'Example:sourceWB.Sheets("Sheet1").Range("A1:A10").copy Destination:=ThisWorkbook.Sheets("YourTargetSheet").Range("A" &iCntr*10+1)
next

Search with a keyword ‘downloads’ in our site, you will get the working file to see the code.

Hope this helps-PNRao!
Ping January 22, 2014 at 6:15 AM - Reply
How can we select user choice range and paste it in user defined range? Can we give ability to transpose the data if user wants? Do u gave any vba script that can help me with that?
PNRao January 22, 2014 at 11:13 PM - Reply
Hi,

We give the user to select a range to copy and range to paste in two dif

I am trying to copy specific ranges from 12 worksheets on from the “Source” workbook to the same specific ranges on 12 worksheets to the “Target” workbook. How can I set up a “loop” to accomplish this in a VBA code? Thanks!

– Ron –
PNRao December 4, 2013 at 11:05 PM - Reply
‘If your input files are always same then store them in an Array
mySource=Array(“File1.xlsx”,”File1.xlsx”,…,”File12.xlsx”)

‘If they are not same always then read your workbook names and store it in an Array (mySource)

'open each workbook

For iCntr=1 to 12
set sourceWB=Workbooks.Open(mySource(iCntr-1)) ' since array starts from 0
sourceWB.Sheets("YourSheetName").Range("YourRange").copy ThisWorkbook.Sheets("YourTargetSheet").Range("TargetRange")
'Example:sourceWB.Sheets("Sheet1").Range("A1:A10").copy Destination:=ThisWorkbook.Sheets("YourTargetSheet").Range("A" &iCntr*10+1)
next

Search with a keyword ‘downloads’ in our site, you will get the working file to see the code.

Hope this helps-PNRao!
Ping January 22, 2014 at 6:15 AM - Reply
How can we select user choice range and paste it in user defined range? Can we give ability to transpose the data if user wants? Do u gave any vba script that can help me with that?
PNRao January 22, 2014 at 11:13 PM - Reply
Hi,

We give the user to select a range to copy and range to paste in two different ways.

1. You can use two input boxes: one is to accept the ranges from users and other one is to choose the range to paste.
Please check this link to for advanced input box examples: http://analysistabs.com/excel-vba/inputbox-accept-values/

2.The other method is, creating userform and place two RefEdit controls. one is for to select the range to copy and the other one is the for accepting the range to paste.

So, now you know the range to copy and the range to paste the data. Then you can use, pastespecial method to transpose the data

Example: Let’s say you accepted two ranges from user and stored as rngToCopy and rngToPaste

Range("rngToCopy").Copy
Range(rngToPaste).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= False, Transpose:=True

Hope this helps.
Thanks-PNRao!
Matt May 6, 2014 at 11:36 PM - Reply
Hey! I work for a transportation company and have a workbook in which I keep track of trailers parked at a lot daily. Every day I copy the previous day’s sheet and rename to today’s date.

So I have sheet “05-05” and I copy it and rename the new sheet to “05-06”. I have been Googling this for hours now to no avail: I am trying to create a button that runs a macro that creates a copy of the active sheet, and renames it to today’s date. If I have to include the year in my sheet names that is fine. Can anyone help?
vasu April 1, 2015 at 11:07 AM - Reply
DEAR Matt,
pls explain ur need in detail. i will help you. I am also in Logistic service. Its a favour for me to help anotherf logistics man. Thank u. Vasu
Aliya May 8, 2014 at 1:46 PM - Reply
Hi there,

Could you please help me with the following:

I have two worksheets named Data and Levels. In Data worksheet I have thousands of rows and three columns ( region, ID, and sum). In Levels worksheet I have only hundreds of rows and two columns (ID and Level). What I need is to put for each row in the first sheet its corresponding level from the second sheet.

I’m only a beginner, so detailed explanation would be much appreciated,

Thank you
Josh August 13, 2014 at 12:16 AM - Reply
I am working on a macro to transfer data from one spreadsheet to another. I would like ot transfer the data to the next open row within the second spreadsheet. Basically I want to take electronic batch sheets, with data entered in them, and transfer that data to a central source for record keeping. Is this doable?

Thanks for any help you can offer.
PNRao August 17, 2014 at 11:34 AM - Reply
Hi Josh,
We can do this, every time you need to find the last row in the common file and update the data from that particular row. You can search for the examples provided to fi