Copy all sheet contents from selected workbook and paste into current workbook sheets of the same name

[TurpsUK]

Hello there,
I am relatively new to excel VBA, so any help here much appreciated!

I have a source processing workbook with defined sheet names "X", "Y", "Z" etc . They are empty of data before the Macro is executed. I want to pull in all the data content from another workbook that have the same sheet names using copy paste values. So, using a file picker open the target file (which has been extracted from an internal database as an xlxs. into downloads folder) and copy each sheet range content from "A1: x" into the workbook with the same names.
I have managed to develop a Macro (below) that copies the sheets in, which works okay. But the problem is that in the processing workbook, there are a series of nested formula's that will use the data introduced, and therefore it creates multiple formula Reference errors. I'm hoping CopyPaste Values from the specific ranges will allow relative references and sheet names to be maintained, making the forward work much easier.

Kind Regards, Turps

Sub Select_file()

Dim FilePicker As FileDialog
ChDir "C:\"
Dim b1 As Workbook
Dim ws As Worksheet
Set b1 = ActiveWorkbook
Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)
With FilePicker
.Title = "Please select your Watson Data Download File to Copy"
.AllowMultiSelect = False
.ButtonName = "Confirm"
If .Show = -1 Then
mypath = .SelectedItems(1)
Else
End
End If
End With

Workbooks.Open Filename:=mypath
For Each ws In ActiveWorkbook.Sheets
ws.Copy after:=b1.Sheets(b1.Sheets.Count)
Next ws
End Sub

 

[John_w]

Try this:

Public Sub Copy_Workbook_Sheets_Values_To_Same_Sheets()

    Dim FilePicker As FileDialog
    Dim mainWb As Workbook, targetWb As Workbook
    Dim targetFile As String
    Dim ws As Worksheet
    
    Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)
    With FilePicker
        .Title = "Please select your Watson Data Download File to Copy"
        .AllowMultiSelect = False
        If .Show Then
            targetFile = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With
    
    Set mainWb = ActiveWorkbook
    
    Application.ScreenUpdating = False
    
    Set targetWb = Workbooks.Open(targetFile)
    For Each ws In targetWb.Worksheets
        ws.Range("A1").CurrentRegion.Copy
        mainWb.Worksheets(ws.Name).Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    Next
    Application.CutCopyMode = False
    targetWb.Close SaveChanges:=False
    
    Application.ScreenUpdating = True
    
    MsgBox "Finished"
    
End Sub

 

[TurpsUK]

Hi John,
Thank you very much for the code. It worked fantastically well - very quick!
The only issue I had to work through were a few spelling mismatches on sheet names from source to target.

Best Regards

Paul