Copying data from one sheet to another with changing file name

Question

I'm trying to take data from one workbook and paste it into another.
The workbooks change every month. I'd like to select the source file using Application.fileDialog.

Sub CopyTest ()
      
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        'Show the dialog box
        .Show

        'Store in fullpath variable
        fullpath = .SelectedItems.Item(1)
    End With
    
    Dim sourceBook As Workbook
    Set sourceBook = Application.Workbooks.Open(sourceBookPath)
    Dim sourceSheet As Worksheet
    Set sourceSheet = sourceBook.Worksheets("Account Detail GHOA ")
    Dim targetBook As Workbook
    Set targetBook = Application.Workbooks.Open(targetBookPath)
    Dim targetSheet As Worksheet
    Set targetSheet = targetBook.Worksheets(“Macro Data”)
    sourceSheet.Range("A1:W79").Copy targetSheet.Range("A1:W79")

End Sub

I referenced this question to find the above partial solution: Excel VBA file name changes

"...it is not working". What isn't working? Do you get an error? What error, where? — BruceWayne, Dec 19 '19 at 22:15

score 0 · Accepted Answer · answered Dec 20 '19 at 15:23

I found the solution thank you all for the help!

Replace the sheet names with the ones for your work book. This code will enable you to select the source file (where data is being pulled from) and the target file (where you'd like it to paste to). This worked great for what I needed.

I plan on taking the raw data that I imported in (which changes on a monthly basis) and using vlookups to populate the summary tab.

    Sub CopyTest()

With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
 .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        'Show the dialog box
        .Show
'Store in sourceBook variable
       sourceBookPath = .SelectedItems.Item(1)
    End With

Dim sourceBook As Workbook
Set sourceBook = Application.Workbooks.Open(sourceBookPath)
Dim sourceSheet As Worksheet
Set sourceSheet = sourceBook.Worksheets("Account Detail GHOA")

With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
 .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        'Show the dialog box
        .Show
'Store in targetBook variable
       targetBookPath = .SelectedItems.Item(1)
    End With

Dim targetBook As Workbook
Set targetBook = Application.Workbooks.Open(targetBookPath)


With Worksheets("Macro Data").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "Macro Data"
Application.DisplayAlerts = True
End With

Dim targetSheet As Worksheet
Set targetSheet = targetBook.Worksheets("Macro Data")

sourceSheet.Range("A4:W79").Copy targetSheet.Range("A1:W79")

End Sub

Copying data from one sheet to another with changing file name

1 Answers1