VBA Copy ONLY worksheets of the same tab name

[davehnj]

I have a workbook with several tabs. I need to copy from another workbook ONLY the tabs with the same tab-name. I need this to be done in VBA. Any help would be appreciated. I am stumpted after many many tries at this.

 

[Andrew Poulsom]

What have you tried exactly and in what way does it fail?

 

[davehnj]

I have the code for getting the second Excel file (the one I'm copying from) and I have two loops (one within the other) - one that presumably will cycle through the source file sheets and one that will cycle through the destination file sheets and determine if the sheet names are the same. if they are, I attemp to copy the source contents to the destination contents.

it is failing because I cannot seem to ascertain which sheet is active at any given time

This is my code to-date:

Sub Import_source_sheets()

Dim sFile As String
Dim lX As Long
Dim x As Integer
Dim wks As Long
Dim wkd As Long


sFile = Application.GetOpenFilename(FileFilter:="Excel files, *.*")
If sFile = "False" Then GoTo End_Sub
Workbooks.Open Filename:=sFile
Set wkbdest = ActiveWorkbook
Set wkbsource = Workbooks.Open(sFile)
For wks = 1 To wkbsource.Worksheets.Count
For wkd = 1 To ActiveWorkbook.Worksheets.Count
If wkbsource.Worksheets(wks).Name = ActiveWorkbook.Worksheets(wkd).Name Then
wkbsource.Worksheets(wks).Select
ActiveSheet.UsedRange.Copy Destination:=ThisWorkbook.Worksheets(wkd).Range("A1")
End If
Exit For
Next wkd
Exit For
Next wks

ActiveWorkbook.Saved = True
ActiveWindow.Close

End_Sub:

End Sub

 

[Andrew Poulsom]

Why are you opening the same workbook twice?

Workbooks.Open Filename:=sFile
Set wkbdest = ActiveWorkbook
Set wkbsource = Workbooks.Open(sFile)

When you open a workbook it becomes the ActiveWorkbook. The workbook that contains your code is ThisWorkbook. You don't need to activate/select to copy:

wkbsource.Worksheets(wks).UsedRange.Copy Destination:=ThisWorkbook.Worksheets(wkd).Range("A1")

 

[davehnj]

Also Andrew, it simply does not work!

 

[Andrew Poulsom]

Try (untested):

Sub Import_source_sheets()
    Dim sFile As Variant
    Dim wkbDest As Workbook, wkbSource As Workbook
    Dim wks As Long
    Dim wkd As Long
    sFile = Application.GetOpenFilename(FileFilter:="Excel files, *.*")
    If sFile = False Then Exit Sub
    Set wkbDest = ThisWorkbook
    Set wkbSource = Workbooks.Open(sFile)
    For wks = 1 To wkbSource.Worksheets.Count
        For wkd = 1 To wkbDest.Worksheets.Count
            If wkbDest.Worksheets(wkd).Name = wkbSource.Worksheets(wks).Name Then
                wkbSource.Worksheets(wks).UsedRange.Copy Destination:=wkbDest.Worksheets(wkd).Range("A1")
                Exit For
            End If
        Next wkd
    Next wks
    wkbSource.Close SaveChanges:=False
End Sub

 

[davehnj]

Thank you so much, Andrew. This helped a lot. I am still cancelling out since my source sheets have meged cells and I am getting a message that says :Merged cells cannot be changed or deleted". How can I get around this?

 

[Andrew Poulsom]

Merged cells are best avoided as they cause more problems than they solve.

 

[davehnj]

Yes, I realize that. Unfortunately, the source file is being supplied by a client (over whose actions I have no control)

 

[Andrew Poulsom]

Then you could delete the sheet in wkbDest and copy the sheet from wkbSource.