Bottom Line: Learn how to use VBA macros to copy & paste data from one Excel workbook to another, including adding data to the bottom of an existing range or replacing data.
Skill Level: Intermediate
Video Tutorial
Download the Excel Files
Follow along with the video above using the same Excel files that I use. You can download them by clicking below. Here's the workbook that I copy data from in my example:
And here's the workbook that I copy data to. This is the one that has all the macro code in it:
Copy Data from One Workbook to Another Using Excel Macros
There are a few ways to copy & paste data with VBA. We are first going to use the Range.Copy method. This allows us to perform the entire action in one line of code.
Workbooks("New Data.xlsx").Worksheets("Export").Range("A2:D9").Copy _
Workbooks("Reports.xlsm").Worksheets("Data").Range("A2")
The Range.Copy method has an optional Destination parameter that allows us to specify the range we want to paste to.
We reference the source workbook, worksheet, and range that we want to copy from. For the Destination parameter we reference the destination workbook, worksheet, and the beginning cell of the range to paste to.

The Range.Copy method does a regular copy and paste that includes formatting and formulas. If you just want to paste values, there is an example below.
Important Points to Remember
When using this macro to copy data from one workbook to another, keep these points in mind.
- You must reference the correct file extension in the Workbooks property (see video above for details).
- Workbooks do not have to be macro enabled for this to work.
- This code can be stored in a separate workbook, such as your Personal Macro Workbook, if you choose. (Learn how to create a Personal Macro Workbook here.)
- You do not need to select or activate the workbooks, worksheets, or even ranges first. This is because the code already specifies those details.
- Both workbooks must be open when using this code. But the process of opening and closing workbooks can be automated with more code:
Sub OpenWorkbook()
'Open a workbook
'Open method requires full file path to be referenced.
Workbooks.Open "C:\Users\username\Documents\New Data.xlsx"
'Open method has additional parameters
'Workbooks.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)
'Help page: https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.open
End Sub
Sub CloseWorkbook()
'Close a workbook
Workbooks("New Data.xlsx").Close SaveChanges:=True
'Close method has additional parameters
'Workbooks.Close(SaveChanges, Filename, RouteWorkbook)
'Help page: https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.close
End Sub
PasteSpecial Method to Paste Values, Formats, etc.
When pasting data into the destination workbook using VBA, you can also use any of the normal Paste Special features.
There is an example macro below. You'll notice that my example uses the PasteValues type, but you could also use PasteFormulas, PasteFormats, or any of the other PasteSpecial options available. Here is a list of the PasteTypes.
'Copy range to clipboard
Workbooks("New Data.xlsx").Worksheets("Export").Range("A2:D9").Copy
'PasteSpecial to paste values, formulas, formats, etc.
Workbooks("Reports.xlsm").Worksheets("Data").Range("A2").PasteSpecial Paste:=xlPasteValues
To learn more about PasteSpecial options, check out my video series on Copy and Paste with VBA.
Pasting Below the Last Cell
Sometimes the size of your data ranges in the source and destination files will change every time you run the macro. For example, you may have a daily task of adding new entries from an exported sheet to a master list in another workbook.

In that case, you'll want to add the new entries directly below the last entry on your destination sheet. To do that, you can use the following macro.
Sub Copy_Paste_Below_Last_Cell()
'Find the last used row in both sheets and copy and paste data below existing data.
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
'Set variables for copy and destination sheets
Set wsCopy = Workbooks("New Data.xlsx").Worksheets("Export 2")
Set wsDest = Workbooks("Reports.xlsm").Worksheets("All Data")
'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
'2. Find first blank row in the destination range based on data in column A
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
'3. Copy & Paste Data
wsCopy.Range("A2:D" & lCopyLastRow).Copy _
wsDest.Range("A" & lDestLastRow)
This code pastes your source data just below the existing destination sheet data.
In addition to finding the last row in a range or sheet, you can find the last column or cell as well. Checkout my post and video on 3 ways to find the last used row or column to learn more.
Clearing the Destination Range Before Pasting
Instead of adding to a list in your destination range, you may prefer to clear the existing range before pasting the new data. You can do that with this macro.
Sub Clear_Existing_Data_Before_Paste()
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
Set wsCopy = Workbooks("New Data.xlsx").Worksheets("Export 2")
Set wsDest = Workbooks("Reports.xlsm").Worksheets("All Data")
'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
'2. Find first blank row in the destination range based on data in column A
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
'3. Clear contents of existing data range
wsDest.Range("A2:D" & lDestLastRow).ClearContents
'4. Copy & Paste Data
wsCopy.Range("A2:D" & lCopyLastRow).Copy _
wsDest.Range("A2")
End Sub
Running that macro will remove any existing data in the destination range before inserting the data from the source worksheet.

Alternative Code for Copying Data to Your Current Workbook
I wanted to also present to you a slightly different option for your macro. Instead of identifying the destination workbook by name, you can use the ThisWorkbook property. This can be done as long as the macro is stored in the destination (or source) workbook.
By doing this, you avoid having to change the code in the event you change the file name for your destination workbook. Here is the VBA code that uses ThisWorkbook.
Workbooks("New Data.xlsx").Worksheets("Export").Range("A2:D9").Copy _
ThisWorkbook.Worksheets("Data").Range("A2")
This reminds me that VBA will always assume that the macro you want to run applies to the active workbook if you don't specify a workbook in each line of code. I talk about that critical assumption and other important points about running VBA code in this video on VBA Assumptions.
Copy Paste Between Sheets in Same Workbook
You can modify any of the examples above to copy & paste between sheets in the same workbook. Just use the same workbook reference for the copy and destination ranges. Here is an example.
'Copy range to clipboard
Workbooks("New Data.xlsx").Worksheets("Export").Range("A2:D9").Copy
'PasteSpecial to paste values, formulas, formats, etc.
Workbooks("New Data.xlsm").Worksheets("Data").Range("A2").PasteSpecial Paste:=xlPasteValues
You won't always need to specify the workbook, but it is a good habit to get into. Otherwise, VBA makes assumptions that can get you in trouble.
Conclusion
I hope these tips and macros help save you time when copying data between workbooks. Automating this boring task will help prevent errors and make it easy for others to update your reports.
Please leave a comment below with any questions or suggestions. Thank you! 🙂
Excellent time saving tips – Thanks Jon!!
Thanks Marci! 🙂
HOW CAN I PAST IN THE UNLOCKED CELLS IF MY SHEET IS PROTECTED ?
Why when I use the macros to copy paste data does it make my excel size so large??
Hi Nura,
The file size should not differ from doing a copy/paste manually. Both running the macro or copy/pasting manually should produce the same result in the destination file. If your macro is copying all the cells on the sheet, instead of a specific range, then this can increase file size. In the macros above I show examples of copying specific ranges and how to find the last used cell on the sheet to only copy the used range. I hope that helps.
How would you handle it if you just wanted to copy unique data to the second file.
Hey Will,
Great question! Here is a post where I explain how to remove duplicates to create a list of unique values. In method #2 on that post I share a macro to remove duplicates using the RemoveDuplicates method. This can be modified to remove duplicates from one or multiple columns, just like the Remove Duplicates feature in Excel.
That macro adds a new sheet to the workbook, copies the data to the new sheet, and then removed duplicates. You can change that to copy the data to a different workbook instead of adding the new sheet.
I hope that helps. Thanks! 🙂
As always, great “small-things” automation, crystal clear explained and huge time saver. Thanks so much Jon. Keep up the good work, please!!
Thank you for the nice feedback Jordi! I really appreciate your support. 🙂
Hi Jon
I have applied this VBA code to copy the data of workbook(DataExport-worksheets(d) to the bottom of workbook (Import-worksheets(p). The code is as below
Sub Copy_Paste_Below_Last_Cell()
‘Find the last used row in both sheets and copy and paste data below existing data.
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
‘Set variables for copy and destination sheets
Set wsCopy = Workbooks(“New-Data.xlsx”).Worksheets(“Export 2”)
Set wsDest = Workbooks(“Reports.xlsm”).Worksheets(“All Data”)
‘1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, “A”).End(xlUp).Row
‘2. Find first blank row in the destination range based on data in column A
‘Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, “A”).End(xlUp).Offset(1).Row
‘3. Copy & Paste Data
wsCopy.Range(“A2:D” & lCopyLastRow).Copy _
wsDest.Range(“A” & lDestLastRow)
‘Optional – Select the destination sheet
wsDest.Activate
End Sub
when running the code an error is appear runtime error (9) subscript out of range. I do not know what is the problem and how can be solved.
I prefer to send the files here but I could not,
Thanks Jon, I have been manually Copy/Past(ing) from one workbook to another with various Special options but (aside from pasting values) whenever I change the source workbook name it changes the destination data. I would like to make copies of the source file without changing the destination data (still referencing the original name) similar to an absolute cell reference. Does the VBA reference change with a source file name change?
Hi Willie,
Great question! I believe you are referring to formulas that are copied to a new workbook? You want to retain the reference to the original workbook name, even if you change the source workbook name?
If that is correct you will want to Close the destination workbook before you change the name of the source. The references will NOT update when the destination workbook is closed. This step can be added to your macro and I provided code in the article and file to close the workbook with VBA.
When you open the destination file again you will get a warning message saying some of the links can’t be updated. At that point you can click Edit links to update them if needed.
I hope that helps.
Hi Jon,
congratulation for your incredible work.
I watch all the time with pleasure – you are an inspiration to me.
Thank you.
Thank you Nick! I really appreciate your support and that means a lot to me. 🙂
Thanks very much. I am new to learning Macros and very excited about it. You make learning fun by explaining it so thoroughly.
Thanks Nishi! I really appreciate the nice feedback. 🙂
Excellent as usual with you, Jon!
Thank you Sandeep! 🙂
Thank you. I appreciate the tips and will practice it
Awesome! Thanks Femi! 🙂
Hi Jon,
Thanks so much for the video. Very helpful.
I would also want to check if there is a code where I can copy only specific columns from one workbook and paste it to another.
Hi Mohammed,
Yes, there are a few ways to go about it. You can reference multiple areas in the copy range.
That is just an example and you will also want to add workbook and worksheet references.
I hope that helps.
Hi Jon and HNY,
I’m working on this new method, particularly the Paste Below option, and my question is, does the copy cells (“A”) need to be the same as the destination cells (“D”). This is how my data is setup.
Hi Lola,
Great question! No, the column references do NOT need to be the same. You can copy from any column and start the paste in another column. The results will be the same as if you do the same copy and paste in Excel manually.
I hope that helps.
Dear Sir, thank you for lesson it is difficult to follow instruction for time being however I will go throught slowly to learn how to copy and pase in VBA.
Rana Jang Bahadur
Hi Jon,
Greetings for the day,
Recently I started my learning in VBA and found your videos/sources are extremely useful to new beginners like me. I was able to perform all the Operations which mentioned above. But I have a mini project and struck hoping so this file/macros will help me.
Coming to the problem, I need to generate Reports and send them an email. so i usually get raw data and I filter the required data and copy the data and send emails. so I wanted to automate the process by running macros. However, post pulling the raw data I need to send them in a specific template/format. but the macros which you mentioned simply copies the data and pastes. It would be great if you help me. I am really struggling and hoping so i get support on this.
Hi Santoshlepak,
Sorry, we don’t provide individual support or consulting services at this time. I’m planning to do a separate post in the future on how to send files with Outlook using VBA. Thanks!
Hi Jon,
Thanks for the video, it has given me some ideas about a project I have been working on, but I need some extra clues. The project involves creating a tracker of orders that are issued in excel. With the video I know that I can use the source as a template for the orders, make the changes in the template, and then save the order with the number/code assigned to it, while the template still helps me to copy the information I need in the tracker (the destination)…not 100% ideal, but an start. However, if there is any change in the order, or I want to update anything, I have to type again everything in the template and do the whole process again. How can I amend the code so that I can apply it to a different source without having to change the name every time? I will have a look at the article about avoiding duplications, to solve the other issue the amendment would bring…but I would appreciate if you could point me to the direction where I can find the answers I need about applying the code to the source I want.
Hi John,
I took your VBA course in the summer of 2017 and got a chance to apply what I learned in March of 2018 for a contract client. The macro shortened a process which took 5 days for 20 salespeople to 1 day for 200 people. Could not have done this without your course. The assignment was extended from 3 months to 5 months as a result and gave me the opportunity to use Power Query and Power Pivot as well.
Writing VBA code is still a challenge but at least I understand the code and that makes it a lot easier to write. The best way to learn is to be challenged. I hope I get another opportunity to write macros in my current assignment.
You are a blessing to those who follow you on Excel Campus. Keep up the great work!
I’ve just finished getting the macro to copy some data to another workbook after deleting some of the rows that were already on the “copy to” workbook. Got it to work. It is very slick. Now I’ve got to find your instructions to open a workbook that is not open yet so I can copy to it.
Thanks for your instructions. They are great and easy to follow.
Brian
Thanks Brian! I’m happy to hear you got it working. There is code on the page above on how to open and close a workbook. It is also in the example file you can download above. You will just put that Workbooks.Open line at the beginning of the macro. I hope that helps.
Hi Jon,
A useful exercise.
Suppose I have a monthly worksheet where each sheet has differing amounts of data (all same columns) and I want to take the data from each sheet and consolidate in a new sheet within the same workbook, with no blank rows, do I just add each sheet in the variable, so
Set wsCopy – workbooks(January.xlsm”).Worksheet (1)
Set wsCopy – workbooks(January.xlsm”).Worksheet (2)
etc
Set wsCopy – workbooks(January.xlsm”).Worksheet (31)
And just set the final destination as worksheet 32?
Regards
Joe
Hi Joe,
Great question! The wsCopy variable is not additive like that. Instead, we have to run the macro for each sheet. However, we can use a loop to automate this.
In the following macro I just added a few lines with a for next loop that loops through the numbers 2 to 20. You can change those numbers to correspond to your sheet numbers. You only want to include the numbers (positions) of the sheets that you want to copy from. It will be easiest if all those sheets are together, but you can add additional code to skip specific sheets with an If statement.
In the code above the counter variable (i) is used in the Worksheets property when setting the reference for wsCopy. This is effectively what you were trying to do in your question where you listed the worksheets. The loop just does all this work for us and makes it much faster and easier.
Here is an article on the For Next Loop for VBA that explains more about it.
I hope that helps.
Hi Jon,
Thanks for the super speedy reply. Will try it.
Thanks.Joe
Hello, In above code I am unable to paste special
wsCopy.Range(“A2:D” & lCopyLastRow).Copy _
wsDest.Range(“A” & lDestLastRow).PasteSpecial Paste:=xlPasteValues
Hi Jon,
Thanks for the sharing on this VBA code. it’s really helpful and i thank you so much.
i’m a beginner in VBA code and really want to learn more as its really helpful in my daily work with excel.
i got 1 questions regards this sharing, do i need to create are button to run the program or the program run automatically.
Thanks Jon
Thanks Faiz! The macro will not run automatically unless you set it up to do so. You can run it from the VB Editor, create a button to run it, or have it run based on an event (user action). Here is a video and article on events.
I’m also working on an article on macro buttons that will be out next week. This article and video on macro buttons for filters does explain the basics though.
I hope that helps.
Hi Jon,
I am relatively new to VBA – love your site for easy to follow tips!
Regarding this macro, is there any way to do this where the column headings may be different? As someone noted above, I am looking to copy and paste the data from multiple sheets to make one consolidated sheet. However, the data from my source sheets comes with different column headings i.e. what is column A in one sheet may be column C in another sheet.
Hi Bec,
Yes, this is definitely possible. There are a few ways to go about it depending on how the data is set up.
If the starting Row is the same in each sheet, then you can use the WorksheetFunction.Match method. This is the same as the MATCH function you would use in Excel to lookup data and return the row or column number in a range.
Here is an example that looks in row 5 to find the word Date. You can change that to the value in the starting cell. It then uses the lCol variable with the column number in the next line to create the copy range. The column index in the second Cells reference adds 10 to the lCol variable. You would change that to the number of columns in your data set.
With wsCopy lCol = WorksheetFunction.Match(“Date”, .Range(“A5:K5”), 0) .Range(.Cells(5, iCol), .Cells(lCopyLastRow, iCol + 10)).CopyEnd With
If the starting row or value of the starting cell changes in every sheet, then you can use something like the Range.Find method to find the first used cell on the sheet. Here is an article that explains more about finding the first used cell.
I hope that helps get you started. 🙂
Thanks, this is really helpful – I will give it a go!
I noticed another question about looping through worksheets to do this copy and paste – would there be any way to do this if your worksheets were not sequentially numbered? Eg if you have a handful of sheets each with a discrete name string?
Thanks 🙂
Hi Bec,
Yes, there are a few ways to go about it.
One easy way is to re-order the sheets within the workbook so they are in a group. Then just loop through those sheets.
Another option is to use an If statement for sheets that contain a specific name or phrase. The InStr (In String) function searches for a value in a string. In this case we are looking for the word “data” anywhere in the sheet name.
You could also check for a value in a cell within the sheet. Or loop through a list of sheet names that is stored on another sheet. There are a lot of options, and this is why the For Next Loop is such a powerful tool.
I’ll do a follow-up post in the future with some of these techniques. I hope that helps get you started. 🙂
Hi Jon, Thank you so much for this tutorial! In my situation, the name of the workbook that I need to copy/paste is always different. I am wondering if it is possible to open a file browser in order to navigate to the file that I would like to copy/paste. Thank you again!
Hi Jon,
for 3rd items : Copy and Paste Data.
How can i change the command to paste as special as values&number.
Currently the copy and paste are copying the formula on the ranges
Hello Jon and the team,
I need to copy a file as many times as the users in a list and rename the files with users names. Do you happen to have a vba code for that. I would greatly appreciate the help!!!
Thank you and have a wonderful day
Anna
Hello Jon and Team,
I am using the code example you have for “Pasting Below the Last Cell”. But instead of having the code from the copy range get pasted below the last cell in the destination range, I would like to know how the code would be modified so that copy range gets pasted into the destination sheet starting a cell “A:11?
Thanks
Hello Jon and Team,
I am using the code example you have for “Pasting Below the Last Cell”, I like how it allows for the copy range to be dynamic. But instead of having the code from the copy range get pasted below the last cell in the destination range, I would like to know how the code would be modified so that copy range gets pasted into the destination sheet starting a cell “A:11?
Thanks
I have Excel (xlsm) with application written in VBA. I need to open another excel workbook (xlsx file) online on OneDrive. When I open thru ‘Set ww = Workbooks.Open(“https://..’ it always opens on local machine so other users on onedrive cannot access it while my program is using it. I used the following command to open online
Set oExcel = New Excel.Application
Set oWB = oExcel.Workbooks.Open(“https://…..”)
and could get number of rows by :-
oWBLR = oWB.Sheets(“Customers”).Cells(Rows.Count, 1).End(xlUp).Row
But when I am trying to copy by :-
oWB.Sheets(“Customers”).Select
Range(Cells(2, 1), Cells(oWBLR, “G”)).Select
Selection.Copy
It does not select or copy any cells
I think I am missing something. I took this out of your video on VBA assumptions above and it doesn’t work unless it is in the workbook ReconcileTest.xlsm: It gives me a “Run-time error ‘9’: Subscript out of range”
Workbooks(“ReconcileTest.xlsm”).Worksheets(“Data”).Range(“Q1”).Value = 200
I also tried this and it only works if I change New Data to ReconcileTest when it is in the ReconcileTest file:
Workbooks(“New Data.xlsm”).Worksheets(“Data”).Range(“A1:B20”).Copy
Workbooks(“ReconcileTest.xlsm”).Worksheets(“Data”).Range(“O1:P20”).PasteSpecial
In both cases both files are open. Any suggestions? Thanks
I had the same error, check the details of the to and from carefully to make sure that it is correct. After I checked and fixed mine it worked. Also, try a few things to fix your excel. Link below: (https://www.repairmsexcel.com/blog/excel-hangs-when-copy-pasting)
Hi Jon,
how to make it auto upload by time ?
Merhaba from turkey hello,
İki bilgisayar arasında kopyalama yaparken özel bir işlem gerekir mi?
Çok güzel yöntemlerle anlatmışsın.
Sub Copy_PasteSpecial_Method()
‘Copy range to clipboard
Workbooks(“New-Data.xlsx”).Worksheets(“Export”).Range(“A2:D9”).Copy
‘PasteSpecial to paste values, formulas, formats, etc.
Workbooks(“Reports.xlsm”).Worksheets(“Data”).Range(“A2”).PasteSpecial Paste:=xlPasteValues
{Note: ofter that i want to save the workbook file at “E:\myinvoices\ with invoice number autosave}
End Sub
Hi JON ACAMPORA,
Firstly of all , thanks for code to help my project.
But i have 3 data to copy and paste it to other workbook.
Can you help me to share the coding if i want to copy more than 2 data in 1 workbook.
Thanks.
Hello sir, maybe you can help me with a rather complex problem, can help me with that.
In the worksheet “Menu” I have in cell “E2” I have data from 1 to 3, now I have cell “B4” and “B10” I have an ID reference to names (there are 20), what do I want now do, for example. if E2 = 1, I want to go to worksheet “1” then look at the IdNr then put the data in the right place. It contains date for “B4” = cell “C6”, “E6”, “E8”.
for “B10” = cell “C12”, “E12”, “E14”.
So with flea cup and a loop, maybe the next formula.
if “E2” = 1 then
Workbook 1
if B4 = 1 then and B10 = 2 then
Hlookup (B4, workbook 1 (A1: CV21), 1)
lookup (B10; workbook 1 (A1: CV21), 3.3)
C6 = workbook 1 (places in column 3 row 3) for each number in b4)
E6 = workbook 1 (places in column 5 row 3) for each number in b4)
E8 = workbook 1 (places in column 4 row 3) for each number in b4)
if B10 = 2 and B4 = 1 then
Hlookup (B10, workbook 1 (A1: CV1), 1)
lookup (B10; workbook 1 (A1: CV21), 3.3)
C6 = workbook 1 (places in column 8 (is also column 3 of “B10”) row 3) for each number in b4)
E6 = workbook 1 (places in column 10 (is also column 5 of “B10”) row 3) for each number in b4)
E8 = workbook 1 (places in column 9 (is also column 4 of “B10”) row 3) for each number in b4)
So maybe it is useful to work with a loop.
I do not know if this formula is correct.
It would be useful to (search on both B4 and B10) than put the data in the right place, is this possible?
Hi I have identical workbooks for each of about 15 clients in MS Teams. Each client has its own team and the workbook is saved in the same folder in each team. I need to copy one worksheet from each workbook and combine into an a single worksheet. Is there any VB for copying worksheets in teams?
Hi Sir,
Suppose the file name is changing daily, how can I copy new folder file and save with new date (next business day).
Another, how can I copy data from a changing workbook filename/location?
Thanks for your help. 🙂
Hi Jon and Team,
This Tutorial is useful indeed, I was able to execute these on my project, however I have a problem, combining “last used cell” and “paste as values” though the debugger says no error but upon running the macro it gives me a different results , mine should copy data from source up to the last used row, and paste it on another file’s last used row, for example, my Last used row is A35 if I run the code it will paste it on A192 which is not what I expect 🙁 could you help me solve my problem :(.
Sub Copy_PasteSpecial_ValuesandLastrowused()
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
‘Set variables for copy and destination sheets
Set wsCopy = Workbooks(“New-Data.xlsx”).Worksheets(“Export 2”)
Set wsDest = Workbooks(“Reports.xlsm”).Worksheets(“All Data”)
‘1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, “A”).End(xlUp).Row
‘2. Find first blank row in the destination range based on data in column A
‘Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, “A”).End(xlUp).Offset(1).Row
‘Copy range to clipboard
Workbooks(“New-Data.xlsx”).Worksheets(“Export 2”).Range(“A2:D” & lCopyLastRow).Copy
‘PasteSpecial to paste values, formulas, formats, etc.
Workbooks(“Reports.xlsm”).Worksheets(“All Data”).Range(“A2” & lDestLastRow).PasteSpecial Paste:=xlPasteValues
End Sub
Hi,
Instead of IDestLastrow in below
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, “A”).End(xlUp).Offset(1).Row
Use Lastrow as per below code.
LastRow = wsDest.Cells(wsDest.Rows.Count, “A”).End(xlUp).Row + 1
Hi Jon,
This is really helpful and I currently have the pasting below last cell code working, so a huge thanks for sharing.
I would like to add an If statement where the rows are only copied over if Column “J” has Yes. I’ve tried a few different things but can’t get it to work. How do I go about adding this extra step?
Very Help full
Hi,
I am using “Pasting Below the Last Cell” for one of my project.
But I am getting the Error as “Run-time error ‘9’, Subscript out of range” at line “Set wsCopy = Workbooks(“New Data.xlsx”).Worksheets(“Export 2″)”.
Could you please help in to understand how to resolve this.
Hi there, my VB code is:
Dim Source As Workbook
Dim SheetSource As String
Dim SheetTarget As String
Dim RangeCopy As String
Dim RangePaste As String
Dim File As String
File = “C:\…xlsx”
SheetSource = “Source”
SheetTarget = “Target”
RangeCopy = “K46:T71”
RangePaste = “A10”
Set Source = Workbooks.Open(File)
Source.Worksheets(SheetSource).Range(RangeCopy).Copy
ThisWorkbook.Worksheets(SheetTarget).Range(RangePaste).PasteSpecial Paste:=xlPasteValues
I get run-time error 9, subscript out of range.
Any help would be appreciated. Thanks!
I was getting this with the workbooks.close command, and found an answer in the comments here: https://stackoverflow.com/questions/22225643/error-closing-workbook-subscript-out-of-range
I tried removing the full file path from the close method and just referenced (“file.xlsx”) instead of (“C:locationsubfolderfile.xlsx”) and that resolved it.
HI JON,
When i used this method { Copy_Paste_Below_Last_Cell }
1- Its copy functions =0.
Is there any way to make it copy the value in the cell?
2- Can you make it copy certain cells and pasted in certain cells?
thanks for your amazing effort .
Dear, I have a excel Form which the employees will fill and send me. I need to capture some of the column details in another workbook as report format. The common field is employee ID number. So if I insert employee ID in the report work book, the data from that particular work book which contains this employee ID must capture. The excel sheet name will be the employee ID number. Can you help. Thanks
Hi
I am trying to copy the range of cells with formulat to a new worksheet. But getting the below error. Can you please help.
Run-time error ‘-2147352565 (8002000b)’:
The specific dimension is not valid for the current chart type.
Below is my code. FYI, it is successfully opening the file mentioned in filename1. It is failing in ‘Set SourceRange’ statement.
Sub RefTable()
Dim sourceBook As Workbook
Dim filename1 As String
Dim SourceRange As Range
Dim DestinationRange As Range
filename1 = Cells(4, 3).Value
Application.ScreenUpdating = False
Set sourceBook = Workbooks.Open(filename1, , False)
Set SourceRange = Workbooks(filename1).Worksheets(“Sheet3”).Range(“A1:T100”)
Set DestinationRange = ThisWorkbook.Sheets(“Tables”).Range(“A1”)
SourceRange.Copy DestinationRange
‘Sheets(“Tables”).Name = “Tables”
sourceBook.Close
Application.ScreenUpdating = True
End Sub
As part of this, i want to copy both the literals and fomulas present in the cells between A1 to T100 of filename1 workbook to another workbook.
Assistance would be highly appreciated.
Thanks
Girish
Hello,
Can you please help me with different rows to be copy and pasted in specific rows.
For E.g:
Copy from:
E57:L57
E111:L111
E145:L145
Paste in:
D6:K6
D11:K11
D14:K14
respectively
I can able to copy paste 1 row by below formula:
Sub Copy_PasteSpecial_Method()
‘Copy range to clipboard
Workbooks(“Apr 19.xlsx”).Worksheets(“30 Apr”).Range(“E57:L57”).Copy
‘PasteSpecial to paste values, formulas, formats, etc.
Workbooks(“Macro practice.xlsx”).Worksheets(“Data”).Range(“D6:K6”).PasteSpecial Paste:=xlPasteValues
End Sub
Please note – Copy and paste are in different file.
Please look at the formula.
From – Apr 19.xlsx
To – Macro practice.xlsx
Hi Quick question on the copy and paste code below:
‘4. Copy & Paste Data
wsCopy.Range(“A2:D” & lCopyLastRow).Copy _
wsDest.Range(“A2”)
I see the code for copy but not the actual code for paste.. can you help me understand .. thanks a million.
Sharon
Hi Sharon,
The Copy method has an optional parameter for the Destination range.
The underscore character is continuing on the line. However, this is really just one line of code.
You can write it in one line of code and just put a space between the word Copy and the reference to the destination range.
The following will copy A1:A10 and paste it starting in cell B2.
I hope that helps. Thanks again and have a nice day! 🙂
Hi,
I wish to thank you for the most useful information in this article. It has helped me a great deal.
Hi
I noticed that the file extensions of the two workbooks referred to in the code under the paragraph, “Copy Paste Between Sheets in Same Workbook”, are not the same. The file extension in the 2nd line of code there should also be “.xlsx”.
Hi, what to do if i need to copy data from one workbook to another based on condition? thank you.
Hello,
I have a reference line(Row) should be inserted to multiple excels(Different name), is der a possible way to automate it using VBA
The Destination file has values, which should not be deleted.
Hi Jon, this is really helpful, being new to VBA this simple approach you show is excellent.
This code fits what I am trying to do, however I have 50 different “source files” from where the copy rage will come, and 01 file (1 tab) where the data will be pasted, deleting previous data every time I tun the VBA.
How do i go about this? copy the code down as many times as “sources of data” I have ?
Thanks,
J
Dear,
i am looking for a macro which could find and pick up data as per column heading from the raw file and paste it in template.
When I transfer data from one workbook to another it works but I need to paste special added to it so I only import the values and not formulas.
Sub Copy_Paste_Below_Last_Cell()
‘Find the last used row in both sheets and copy and paste data below existing data.
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
‘Set variables for copy and destination sheets
Set wsCopy = Workbooks(“Production Data Log.xlsm”).Worksheets(“Data”)
Set wsDest = Workbooks(“transfer data.xlsm”).Worksheets(“Data”)
‘1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, “A”).End(xlUp).Row
‘2. Find first blank row in the destination range based on data in column A
‘Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, “A”).End(xlUp).Offset(1).Row
‘3. Copy & Paste Data
wsCopy.Range(“Y26:AH45” & lCopyLastRow).Copy _
wsDest.Range(“A” & lDestLastRow)
Hi,
I believe this should help:
wsCopy.Range(“Y26:AH45” & lCopyLastRow).Copy
wsDest.Range(“A” & lDestLastRow).PasteSpecial xlPasteValues
Note that the underscore was removed right after the ‘copy’ keyword.
It worked for me.
Cheers
Leandro