Copy Data from One Workbook to Another

[ForrestGump01]

Hello all,

Should be a simple piece of code, but I'm getting a timeout error.

I've got a workbook, let's call it "summary", which I want to put a macro in that will open another workbook, select all the data from a specific tab "CFG", and paste it into a tab, of the same name "CFG" within the "summary" workbook. I do not want to copy the entire tab, rather just copy the cells and paste them as values, so that the formulas in the summary workbook still work.

FWIW I will be repeating this multiple times (e.g. there are multiple tabs I'll be copy-pasting, currently just testing it with one tab)

Any help is appreciated.

Here's my code:

Dim my_SourceFile As Variant
Dim my_SummaryFile As Variant


Sub Retrieve_IR()


my_SummaryFile = ThisWorkbook.Name
my_SourceFile = Application.GetOpenFilename(FileFilter:="Excel Files,*.xl*;*.xm*")

If my_SourceFile <> False Then
Workbooks.Open Filename:=my_SourceFile
End If

Sheets("CFG").Copy

Workbooks("my_SummaryFile").Activate
Sheets("CFG").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats


End Sub

 

[Fluff]

Do you want to overwrite the data in the summary book, or add the new data below?

 

[ForrestGump01]

Do you want to overwrite the data in the summary book, or add the new data below?

Overwrite it, this macro is used for updating the data in the summary workbook from the monthly refreshed source book.

 

[Fluff]

Ok, how about

Dim my_SourceFile As Variant
Dim my_SummaryFile As Workbook


Sub Retrieve_IR()
    Dim SourceWbk As Workbook

    Set my_SummaryFile = ThisWorkbook
    my_SourceFile = Application.GetOpenFilename(FileFilter:="Excel Files,*.xl*;*.xm*")
    
    If my_SourceFile <> False Then
        Set SourceWbk = Workbooks.Open(my_SourceFile)
    End If
    [COLOR=#ff0000]my_SummaryFile.Sheets("CFG").Range("A1").UsedRange.ClearContents[/COLOR]
    SourceWbk.Sheets("CFG").UsedRange.Copy
    my_SummaryFile.Sheets("CFG").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats

End Sub

The line in red clears the CFG sheet in the summary book, delete that line if not needed.

 

[ForrestGump01]

Thank you. This is PERFECT!

Now if I wanted to just continuously repeat this function to copy-paste multiple workbook, I could just go:

SourceWbk.Sheets("CFG").UsedRange.Copy
my_SummaryFile.Sheets("CFG").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats

SourceWbk.Sheets("CFG2").UsedRange.Copy
my_SummaryFile.Sheets("CFG2").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats

and so on and so fourth?

 

[Fluff]

You could, or you could do it like

Dim my_SourceFile As Variant
Dim my_SummaryFile As Workbook


Sub Retrieve_IR()
    Dim SourceWbk As Workbook
    Dim Ary As Variant
    Dim i As Long
    
    Ary = Array("CFG", "CFG2", "CFG3")
    Set my_SummaryFile = ThisWorkbook
    my_SourceFile = Application.GetOpenFilename(FileFilter:="Excel Files,*.xl*;*.xm*")
    
    If my_SourceFile <> False Then
        Set SourceWbk = Workbooks.Open(my_SourceFile)
    End If
    For i = 0 To UBound(Ary)
        my_SummaryFile.Sheets(Ary(i)).Range("A1").UsedRange.ClearContents
        SourceWbk.Sheets(Ary(i)).UsedRange.Copy
        my_SummaryFile.Sheets(Ary(i)).Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    Next i
End Sub

 

[ForrestGump01]

You could, or you could do it like

Dim my_SourceFile As Variant
Dim my_SummaryFile As Workbook


Sub Retrieve_IR()
    Dim SourceWbk As Workbook
    Dim Ary As Variant
    Dim i As Long
    
    Ary = Array("CFG", "CFG2", "CFG3")
    Set my_SummaryFile = ThisWorkbook
    my_SourceFile = Application.GetOpenFilename(FileFilter:="Excel Files,*.xl*;*.xm*")
    
    If my_SourceFile <> False Then
        Set SourceWbk = Workbooks.Open(my_SourceFile)
    End If
    For i = 0 To UBound(Ary)
        my_SummaryFile.Sheets(Ary(i)).Range("A1").UsedRange.ClearContents
        SourceWbk.Sheets(Ary(i)).UsedRange.Copy
        my_SummaryFile.Sheets(Ary(i)).Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    Next i
End Sub

Is there a benefit to the array version? How exactly does this code work? I don't understand the array arguments... Thank you though for the thoughtful answer!

 

[Fluff]

Main benefit of using the array is less code to write
The items in the array need to be the sheet names you want the code to work on. Just add as many sheet names as needed, in the same manner as I've shown, and the rest of the code will look after itself.