How to Use VBA Macros to Copy Data to Another Workbook

Hi,

Thank you so much for the above code. I got it all working in 2 new workbooks that I created, but when I tried to add the code to an existing workbook then it is highlighting the first Sub line and I get the error Compile Error: Expected Identifier.

Do you know what I´ve done wrong?

Many thanks

Hi Jon and congratulations for the post. It’s clear and straight to the point.
One minor question: how should I modify the subs in case I needed to copy only the rows where one specific cell is empty?
Let me rephrase. One of the cells is a toggle field (“OK” = done, empty = yet to be done). I just want to copy the rows where the toggle is empty. Again, thank you for the post.

Hi, I am new to VBA and would like your help to update below code to paste values and formats ?

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)
End Sub

Hi this is an excellent VBA,

I have one query though, I’m trying to use this code to copy data from a worksheet that has data validation and conditional formatting in it. When I run it, the code also copies and pastes all data including validation and formatting to the destination worksheet. However, I would like to be able to copy data and paste it as VALUES ONLY, without the conditional formatting as well as the validation from the source worksheet.

Now, how should I end or replace this string:

wsCopy.Range(“A2:D” & lCopyLastRow).Copy _
wsDest.Range(“A2”)

so that I am copying and pasting unformatted values only from source sheet to destination sheet.

how do I auto run this. I want it in such a way that the minute I open the WB, the macros should run
but it doesnt.

Thank you for the great video. Would you happen to have a video on how to move data from one workbook to another based on a certain criteria & only if the data does not already exist in the destination workbook?

For example, I open a newly exported report every morning, (approximately 3k rows) & place a filter on it & in column D, I have 16 unique values. All of the rows have to be moved to their own workbook based on the value in column d. (So 16 different workbooks) but I don’t want to transfer the rows if they already exist.
Any advice on this would be greatly appreciated. Thank you!

Jon,

Thanks for this. I do have a quick question. I am trying to add index & Match Data from one table and pasting as values to the bottom of another table. The following code works but it’s pasting the formulas…

‘3. Copy & Paste Data
wsCopy.Range(“B9:H” & lCopyLastRow).Copy _
wsDest.Range(“A” & lDestLastRow)

I have been trying to modify to pastespecial but it keeps giving me errors. The following isn’t working.

‘3. Copy & Paste Data
wsCopy.Range(“B9:H” & lCopyLastRow).Copy _
wsDest.Range(“A” & lDestLastRow).PasteSpecial
Paste:=xlPasteValues

It is giving a compile error, expected end of statement.

Hi,

I can not export data if the another workbook is already opened. please suggest a code that can paste data even the destination workbook is already opened.

what if the range is different each time?

if i snd you a couple of files are you able to help please?

I need a help, the above copy method VBA is what i am looking for but I need the destination workbook and worksheet as variable and the value will be taken from the cell value of copy worksheet.
example: My active worksheet is named DATA ENTRY. in this, a cell G11 is a worksheet name of another workbook. that workbook name is in G10. I need to copy G9 value to G11worksheet in the last row.

I know this is little bit confusing.

in simple way, to this below code I need varying workbook name and worksheet names ( which are taken from cell values)

Workbooks(“New Data.xlsm”).Worksheets(“Data”).Range(“A2”).PasteSpecial Paste:=xlPasteValues

Any help will be appreciated..

Great examps, but none show how to code it IN the source wkbk & send it TO another, & I find no combo of your suggestions that work; get subscript out of range no matter how I try.
Workbooks.Open “E:POS2INVInvSys.xlsm”
Workbooks(“POS2INV”).Worksheets(“pivot”).Range(“A1:L485”).Copy_ ‘err:subscr outofrng (on copy part)
Workbooks(“InvSys”).Worksheets(“Sheet1”).Range (“A1”)
Workbooks(“InvSys”).Close SaveChanges = True
(open & close work, but not copy)

HI, I have an excel work book contains of 3 (THREE) excel sheet named as.

1)ITEM LIST Sheet
(in there FOUR columns used as named SR.NO, ITEM CODE, ITEM NAME and SALES PRICE. TWO Columns named ITEM CODE and ITEM NAME should be non-editable)

2)ITEM RECEIVED Sheet
(in there EIGHT columns used as named DATE, INVOICE NO, SUPPLIER NAME, ITEM CODE, ITEM NAME, ITEM QUANTITY, UNIT PRICE, INVOICE VALUE)

3)SUPPLIER LIST Sheet
(in there FIVE columns used as named DATE, INVOICE NO, SUPPLIER NAME, ITEM NAME, INVOICE VALUE. Used Columns also should be non-editable)

I have entered data to at ‘ITEM RECEIVED’ Sheet as per criteria. Now I want to transfer Scheduled Data such as cell data of “D5”,”H5”,” L5”,” P5” as ‘ITEM CODE’ and “E5”,” I5”,” M5”,” Q5” as ‘ITEM NAME’ From ‘ITEM RECEIVED’ Sheet to Cell “B3” as ‘ITEM CODE ‘and to Cell “C3” as ‘ITEM NAME’ to ‘ITEM LIST’ Sheet. Again I also want to transfer Scheduled Data such as cell data of “A5”,” B5”,” C5”, as ‘DATE’,’INVOICE’, ‘SUPPLIER NAME’,“E5”,” I5”,” M5”,” Q5” as ‘ITEM NAME’, “F5”,” J5”,” N5”,” R5” as ‘QTY’ and “X5” as ‘INVOICE VALUE’ From ‘ITEM RECEIVED’ Sheet to “B3”,”C3”, “D3” as ‘DATE’,’INVOICE’, ‘SUPPLIER NAME’, “E3”,”G3”, “I3”,”K3”,‘ITEM NAME‘ and to “F3”,”H3”, “J3”,”L3” as ‘QTY’ of ‘SUPPLIER LIST’ Sheet by pressing existing ‘SAVE’ Button in ‘ITEM RECEIVED’ Sheet by using VBA Code each time after completing task in ‘ITEM RECEIVED’ Sheet and it will be occurred as same frequently after every completing task. While enter I enter data in ‘ITEM RECEIVED’ Sheet, if there is found “ITEM CODE’ and ‘ITEM NAME’ are same then there should be no change. May I get help in this regards

HI, I have an excel work book contains of 3 (THREE) excel sheet named as.

1)ITEM LIST Sheet
(in there FOUR columns used as named SR.NO, ITEM CODE, ITEM NAME and SALES PRICE. TWO Columns named ITEM CODE and ITEM NAME should be non-editable)

2)ITEM RECEIVED Sheet
(in there EIGHT columns used as named DATE, INVOICE NO, SUPPLIER NAME, ITEM CODE, ITEM NAME, ITEM QUANTITY, UNIT PRICE, INVOICE VALUE)

3)SUPPLIER LIST Sheet
(in there FIVE columns used as named DATE, INVOICE NO, SUPPLIER NAME, ITEM NAME, INVOICE VALUE. Used Columns also should be non-editable)

I have entered data to at ‘ITEM RECEIVED’ Sheet as per criteria. Now I want to transfer Scheduled Data such as cell data of “D5”,”H5”,” L5”,” P5” as ‘ITEM CODE’ and “E5”,” I5”,” M5”,” Q5” as ‘ITEM NAME’ From ‘ITEM RECEIVED’ Sheet to Cell “B3” as ‘ITEM CODE ‘and to Cell “C3” as ‘ITEM NAME’ to ‘ITEM LIST’ Sheet. Simultaneously at once I also want to transfer Scheduled Data such as cell data of “A5”,” B5”,” C5”, as ‘DATE’,’INVOICE’, ‘SUPPLIER NAME’,“E5”,” I5”,” M5”,” Q5” as ‘ITEM NAME’, “F5”,” J5”,” N5”,” R5” as ‘QTY’ and “X5” as ‘INVOICE VALUE’ From ‘ITEM RECEIVED’ Sheet to “B3”,”C3”, “D3” as ‘DATE’,’INVOICE’, ‘SUPPLIER NAME’, “E3”,”G3”, “I3”,”K3”,‘ITEM NAME‘ and to “F3”,”H3”, “J3”,”L3” as ‘QTY’ of ‘SUPPLIER LIST’ Sheet by pressing existing ‘SAVE’ Button in ‘ITEM RECEIVED’ Sheet by using VBA Code for each time after completing every received task in ‘ITEM RECEIVED’ Sheet and it will be occurred as same frequently in next row of both ‘ITEM LIST’ and ‘SUPPLIER LIST’ Sheet. While I enter data in ‘ITEM RECEIVED’ Sheet, if there is found “ITEM CODE’ and ‘ITEM NAME’ are same in ‘ITEM LIST’ Sheet then there should not be RE-PASTE. Being new to VBA looking for VBA Code in this regards

Thank You [email protected] for Your Trying help me. but i need DATA Transfer from a particular Spreadsheet to TWO Spreadsheet in a same Workbook at a time after pressing SAVE Button in particular Spreadsheet from where the DATA will be transferred.

Oh, P.S. I ran all the code from a “workbook open event.” It makes it easier than placing a button or waiting for the user to remember to click on a button.

Hi It’s fantastic
One small problem New-Data.zip contains an .xls file and not a macro enabled file. Please help
Thanks
Leon