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

[momentumons]

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

 

[JackDanIce]

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]

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]

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]

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]

This assumes you are running the macro from the workbook you are importing data into:

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]

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]

This assumes you are running the macro from the workbook you are importing data into:

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]

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

Dim x as Long

Noticed, I'm not using x in the code