Copy the data from one Excel workbook to another using vba

Elena Margulis

New Member
Joined
Aug 21, 2020
Messages
19
Office Version
  1. 365
Platform
  1. Windows
I have Book1 workbook with some dataset inside (it can be any records amount dataset), but always starts from cell A1
I need to copy the content of the Book1, which is located in Sheet1 into my other workbook Book2, Sheet1 using macro or vba - by pressing any short key (when macro will be executed)

I was trying to use the following code (advice from another site + research), but having an error - Microsoft Visual Basic for Application, 400

Please, help...

VBA Code:
Option Explicit

Sub OpenWorkbook()

    Dim swb As Workbook
    Set swb = Workbooks.Open("E:\Projects\AAA\Book1.xlsx")
    Dim sws As Worksheet: Set sws = swb.Worksheets("Sheet1")
    Dim srg As Range: Set srg = sws.Range("All Data")

    Dim dwb As Workbook
    Set dwb = Workbooks.Open("E:\Projects\AAA\Book2.xlsm")
    Dim dws As Worksheet: Set dws = dwb.Worksheets("Sheet1")
    Dim dCell As Range: Set dCell = dws.Range("A1")

    srg.Copy dCell
 

    swb.Close SaveChanges:=False
    dwb.Save

End Sub
 
Last edited:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,834
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
What is Range ALL DATA. You don't define it. Is it Named Range?
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,834
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Then you should define Lastrow at column for range.
VBA Code:
 Option Explicit
Sub OpenWorkbook() 
Dim swb As Workbook, Lr as Long, LC as Long

    Set swb = Workbooks.Open("E:\Projects\AAA\Book1.xlsx")
Dim sws As Worksheet: Set sws = swb.Worksheets("Sheet1")
Lr = sws.Cells(rows.count, 1).end(xlup).row
LC = sws.Cells(1, columns.count).end(xltoleft).column
Dim srg As Range: Set srg = sws.Range(Cells(1, 1), Cells(Lr, Lc))
Dim dwb As Workbook
Set dwb = Workbooks.Open("E:\Projects\AAA\Book2.xlsm")
Dim dws As Worksheet: Set dws = dwb.Worksheets("Sheet1")
Dim dCell As Range: Set dCell = dws.Range("A1")
srg.Copy dCell
swb.Close SaveChanges:=False
dwb.Save
End Sub
 

Elena Margulis

New Member
Joined
Aug 21, 2020
Messages
19
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Then you should define Lastrow at column for range.
VBA Code:
 Option Explicit
Sub OpenWorkbook()
Dim swb As Workbook, Lr as Long, LC as Long

    Set swb = Workbooks.Open("E:\Projects\AAA\Book1.xlsx")
Dim sws As Worksheet: Set sws = swb.Worksheets("Sheet1")
Lr = sws.Cells(rows.count, 1).end(xlup).row
LC = sws.Cells(1, columns.count).end(xltoleft).column
Dim srg As Range: Set srg = sws.Range(Cells(1, 1), Cells(Lr, Lc))
Dim dwb As Workbook
Set dwb = Workbooks.Open("E:\Projects\AAA\Book2.xlsm")
Dim dws As Worksheet: Set dws = dwb.Worksheets("Sheet1")
Dim dCell As Range: Set dCell = dws.Range("A1")
srg.Copy dCell
swb.Close SaveChanges:=False
dwb.Save
End Sub
Just tried - it should work on "Open Workbook" event - but nothing happens when I open Book2 - my dataset in A1 cell of Sheet1 Book1 doesn't go into Sheet1 Book2
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,834
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I think you don't familiar with workbook open event. for that
1. Open Workbook (I Suppose it is Book1).
2. Press ALT+F11 (to open VBA window)
3. At the Top-Left window , Under the Workbook name ( VBA Project (BOOK1) ) Select ThisWorkbook
4. Right-click on it & Select View Code
5. at the windows appeared (Top-right window). From first drop-down menu select workbook & from 2nd drop-down menu select open.
6. Within Code appears Paste your code without first and Last Line.
VBA Code:
Private Sub Workbook_Open()
Application.EnableEvents = False
Dim swb As Workbook, Lr As Long, LC As Long, sws As Worksheet
Dim dCell As Range, srg As Range, dwb As Workbook, dws As Worksheet
Set swb = ThisWorkbook
Set sws = swb.Worksheets("Sheet1")
Lr = sws.Cells(Rows.Count, 1).End(xlUp).Row
LC = sws.Cells(1, Columns.Count).End(xlToLeft).Column
Set srg = sws.Range(Cells(1, 1), Cells(Lr, LC))
Set dwb = Workbooks.Open("E:\Projects\AAA\Book2.xlsm")
Set dws = dwb.Worksheets("Sheet1")
Set dCell = dws.Range("A1")
srg.Copy dCell
swb.Close SaveChanges:=False
dwb.Save
Application.EnableEvents = True
End Sub
7. and Save file as Macro-enabled Workbook(.xlsm)
8. Finish. Now you can close workbook. & when you open this workbook data copied from this to other workbook without need to open it manualy.
9. Of Course you should enable contents when workbook open.

If you want to when open 2nd workbook, macro runs also you should add macro to it also With same method with some modification.
VBA Code:
Private Sub Workbook_Open()
Application.EnableEvents = False
Dim swb As Workbook, Lr As Long, LC As Long, sws As Worksheet
Dim dCell As Range, srg As Range, dwb As Workbook, dws As Worksheet
Set swb = Workbooks.Open("E:\Projects\AAA\Book1.xlsm")
Set sws = swb.Worksheets("Sheet1")
Lr = sws.Cells(Rows.Count, 1).End(xlUp).Row
LC = sws.Cells(1, Columns.Count).End(xlToLeft).Column
Set srg = sws.Range(Cells(1, 1), Cells(Lr, LC))
Set dwb = ThisWorkbook
Set dws = dwb.Worksheets("Sheet1")
Set dCell = dws.Range("A1")
srg.Copy dCell
swb.Close SaveChanges:=False
dwb.Save
Application.EnableEvents = True
End Sub
 

Elena Margulis

New Member
Joined
Aug 21, 2020
Messages
19
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I think you don't familiar with workbook open event. for that
1. Open Workbook (I Suppose it is Book1).
2. Press ALT+F11 (to open VBA window)
3. At the Top-Left window , Under the Workbook name ( VBA Project (BOOK1) ) Select ThisWorkbook
4. Right-click on it & Select View Code
5. at the windows appeared (Top-right window). From first drop-down menu select workbook & from 2nd drop-down menu select open.
6. Within Code appears Paste your code without first and Last Line.
VBA Code:
Private Sub Workbook_Open()
Application.EnableEvents = False
Dim swb As Workbook, Lr As Long, LC As Long, sws As Worksheet
Dim dCell As Range, srg As Range, dwb As Workbook, dws As Worksheet
Set swb = ThisWorkbook
Set sws = swb.Worksheets("Sheet1")
Lr = sws.Cells(Rows.Count, 1).End(xlUp).Row
LC = sws.Cells(1, Columns.Count).End(xlToLeft).Column
Set srg = sws.Range(Cells(1, 1), Cells(Lr, LC))
Set dwb = Workbooks.Open("E:\Projects\AAA\Book2.xlsm")
Set dws = dwb.Worksheets("Sheet1")
Set dCell = dws.Range("A1")
srg.Copy dCell
swb.Close SaveChanges:=False
dwb.Save
Application.EnableEvents = True
End Sub
7. and Save file as Macro-enabled Workbook(.xlsm)
8. Finish. Now you can close workbook. & when you open this workbook data copied from this to other workbook without need to open it manualy.
9. Of Course you should enable contents when workbook open.

If you want to when open 2nd workbook, macro runs also you should add macro to it also With same method with some modification.
VBA Code:
Private Sub Workbook_Open()
Application.EnableEvents = False
Dim swb As Workbook, Lr As Long, LC As Long, sws As Worksheet
Dim dCell As Range, srg As Range, dwb As Workbook, dws As Worksheet
Set swb = Workbooks.Open("E:\Projects\AAA\Book1.xlsm")
Set sws = swb.Worksheets("Sheet1")
Lr = sws.Cells(Rows.Count, 1).End(xlUp).Row
LC = sws.Cells(1, Columns.Count).End(xlToLeft).Column
Set srg = sws.Range(Cells(1, 1), Cells(Lr, LC))
Set dwb = ThisWorkbook
Set dws = dwb.Worksheets("Sheet1")
Set dCell = dws.Range("A1")
srg.Copy dCell
swb.Close SaveChanges:=False
dwb.Save
Application.EnableEvents = True
End Sub
Thank you very much; it worked perfectly - the 1st code version !

I did it a bit different - right clicked on [Sheet1] of [Book2], selected "View Code"; then just pasted all the code inside.
(Or - your steps - 5,6 and then - the code without the Sub / EndSub)

My mistake was - and the error - because I pasted the code into [Sheet1] - instead of pasting it into [ThisWorkbook] !!!

Thanks so much !!!
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,834
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You're Welcome & Thanks for follow-up.
 

Elena Margulis

New Member
Joined
Aug 21, 2020
Messages
19
Office Version
  1. 365
Platform
  1. Windows
You're Welcome & Thanks for follow-up.
Sorry, but still having a problem (just discovered it):
If I delete a rows (starting from 2nd row) in my source workbook - Book1 - then, after opening Book2 (where the data from Book1 should be copied) - I still see those "deleted" rows from Book1 that are now deleted in Book1

Example:
Book1
row 1 a
row 2 b
row 3 c

if I'll delete row 2,3 (b,c) then I will still see in
Book2
row 1 a
row 2 b
row 3 c

If I'll replace row 2,3 of book1 with something else then it will be updated of course. But not in case of deleting ...

Plz help ...
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,834
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
The code only works when opening file and after that not work. If you wantto see modification do after changing at book1 you have two option.
1. Add 2nd code at that post to Book2
Or
2. Add same code as code1 to book1 again but first line should be
Before_Close Not Open
 

Watch MrExcel Video

Forum statistics

Threads
1,130,061
Messages
5,639,834
Members
417,117
Latest member
Ravi Pandey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top