How to write a macro that will open, copy and paste data from a separate workbook

momentumons

Board Regular
Joined
Mar 30, 2020
Messages
52
Office Version
  1. 2016
Platform
  1. Windows
Hello! I have one tab in my workbook that contains all of the 'lookup data' I use in the rest of my model. It is mainly used for data validation lists.
Occasionally new line items will need to be added to these lists.
Eventually I will have three different models for 3 different teams but they all need to have the exact same data validation lists so that I can then bring them together in a master sheet.
I created a separate workbook called 'DATA' that simply housed these lists. However, simply linking every cell to this sheet has proven to be clunky and very breakable!
What i'd like is to write a macro that could "refresh" the data tab in my model workbook by opening up the 'DATA' workbook and copying & pasting (as values) the whole tab.
Then if anyone wanted to add a line item they'd need to go into the 'DATA' workbook to add it and update their own model...
Does that make sense? Can someone please help me write a macro.... or is there an easier way? :)
Thanks
Christie
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
Hi Christie,

Quick internet search found these results to open, copy and paste data from a separate workbook

Or you can start by recording a macro that does these steps and then posting resulting code here with any questions.

Otherwise can you provide more details, e.g.:

- Full path and name for source file (including file extension)
- Full path and name for destination file
- Files saved on a "normal" local area network or Sharepoint/Cloud?
- Sheet names data is copied and pasted to
- Row numbers the data starts in
- Confirmation column A in source Data (or other column) can be used to determine what is the last row to contain data

If it's a single sheet in a file that is used to store look up data, you may want to consider saving as a .CSV file, then connecting to it using Excel's data connections or reading data from it directly.

This should import data faster than open/copy/paste/close file method and your source data saved to a smaller file format (than say .xlsx).

However, if you're using Sharepoint it can be trickier due to how VBA interprets file paths: UNC vs local drives or url type address, I can't recall but it causes issues, might be something to consider.
 

turnerski

New Member
Joined
Apr 1, 2020
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
You could use power query to pull in the relevant data from the different data sets and then append them together. Then you just have to hit refresh (ctrl alt f5) to update the data in front of you, probably quicker than writing a macro.
 

momentumons

Board Regular
Joined
Mar 30, 2020
Messages
52
Office Version
  1. 2016
Platform
  1. Windows
Thanks for your fast reply! I have googled it but it's a bit complex so was hoping for some tailored help..really hoping someone would tell me there was an easier/better way thatn the macro, but if not...

- Source file will be called 'DATA' and the tab is 'DATA'. I'd copy all columns A:J
- Destination files (there will be three different ones) will have the same 'DATA' tab - ultimately the same info (I can change these names if that complicates it)
- Files saved on a shared Dropbox
- Confirmation column A in source Data (or other column) can be used to determine what is the last row to contain data: Yes - though I was thinking I could just copy/paste the whole thing to make it fool proof?
 

momentumons

Board Regular
Joined
Mar 30, 2020
Messages
52
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

You could use power query to pull in the relevant data from the different data sets and then append them together. Then you just have to hit refresh (ctrl alt f5) to update the data in front of you, probably quicker than writing a macro.
Oh yes that sounds much better! Thank you. I will check it out :)
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
This assumes you are running the macro from the workbook you are importing data into:
VBA Code:
Sub CopyData()

    Dim a   As Variant
    Dim x   As Long
  
    Dim SourceFileFullName As String

    '*********************************
    'Adjust to suit
    SourceFileFullName = "C:\fileA.xlsx"
    '*********************************
  
    Application.ScreenUpdating = False
  
    With Workbooks.Open(SourceFileFullName, False, True)
        With .Sheets("Data")
            a = .Cells(1, 1).Resize(.Cells(.Rows.Count, 1).End(xlUp).row, .Cells(1, .Columns.Count).End(xlToLeft).Column).Value
        End With
        .Close False
    End With
  
    With Sheets("Data")
        .Cells.Value = ""
        .Cells(1, 1).Resize(UBound(a, 1), UBound(a, 2)).Value = a
    End With
  
    Application.ScreenUpdating = True
    MsgBox "Finished importing data from file: " & vbCrLf & vblcrf & sourcefullfilename, vbOKOnly + vbInformation, "Data Imported"
  
    Erase a

End Sub
However, @turnerski's suggestion is likely to be easier and without need for VBA
 

momentumons

Board Regular
Joined
Mar 30, 2020
Messages
52
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Yep this was easy! Thanks. And I created a quick macro button with the ctrl alt f5 so anyone can easily refresh it. Awesome - thanks so much @turnerski ! I have never used Power Query but I think i'll have to look into it...
 

momentumons

Board Regular
Joined
Mar 30, 2020
Messages
52
Office Version
  1. 2016
Platform
  1. Windows
This assumes you are running the macro from the workbook you are importing data into:
VBA Code:
Sub CopyData()

    Dim a   As Variant
    Dim x   As Long
 
    Dim SourceFileFullName As String

    '*********************************
    'Adjust to suit
    SourceFileFullName = "C:\fileA.xlsx"
    '*********************************
 
    Application.ScreenUpdating = False
 
    With Workbooks.Open(SourceFileFullName, False, True)
        With .Sheets("Data")
            a = .Cells(1, 1).Resize(.Cells(.Rows.Count, 1).End(xlUp).row, .Cells(1, .Columns.Count).End(xlToLeft).Column).Value
        End With
        .Close False
    End With
 
    With Sheets("Data")
        .Cells.Value = ""
        .Cells(1, 1).Resize(UBound(a, 1), UBound(a, 2)).Value = a
    End With
 
    Application.ScreenUpdating = True
    MsgBox "Finished importing data from file: " & vbCrLf & vblcrf & sourcefullfilename, vbOKOnly + vbInformation, "Data Imported"
 
    Erase a

End Sub
However, @turnerski's suggestion is likely to be easier and without need for VBA
Thank you! You're all too clever. Is there any benefit of using the macro over power query? This seems to have solved my problem :)
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
I've barely used power query either, but but it may be easier to set up the connection and pull data in quicker as well as other reasons given above - I'll defer to @turnerski to answer :)



PS, you can remove line
VBA Code:
Dim x as Long
Noticed, I'm not using x in the code
 

Watch MrExcel Video

Forum statistics

Threads
1,129,452
Messages
5,636,340
Members
416,914
Latest member
DWC199

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