Forum Discussion
Copy Single Sheet from Two seperate, closed workbooks chosen through windows explorer pop up
Just like the title says, ultimately want to run the VBA and windows explorer is opened twice (one for each work book - as name changes) to select the workbooks, then have the tabs copied to a new workbook.
1 Reply
- Try
Sub Combine2Workbooks()
Dim FD As FileDialog
Dim BookA As Workbook
Dim BookB As Workbook
Dim BookC As Workbook
Dim strFileA As String
Dim strFileB As String
Dim WSheet As Worksheet
Dim i As Long
Set FD = Application.FileDialog(msoFileDialogFilePicker)
With FD
.Title = "Select the first workbook"
.Filters.Clear
.Filters.Add "Excel Workbooks", "*.xlsx"
.AllowMultiSelect = False
If .Show = -1 Then
strFileA = .SelectedItems(1)
Else
MsgBox "You did not select a workbook"
Exit Sub
End If
End With
With FD
.Title = "Select the first workbook"
.Filters.Clear
.Filters.Add "Excel Workbooks", "*.xlsx"
.AllowMultiSelect = False
If .Show = -1 Then
strFileB = .SelectedItems(1)
Else
MsgBox "You did not select a workbook"
Exit Sub
End If
End With
Set BookA = Workbooks.Open(strFileA)
Set BookB = Workbooks.Open(strFileB)
Set BookC = Workbooks.Add
BookC.SaveAs "BookC.xlsx"
For Each WSheet In BookB.Worksheets
WSheet.Copy After:=Workbooks("BookC.xlsx").Sheets(1)
Next
For Each WSheet In BookA.Worksheets
WSheet.Copy After:=Workbooks("BookC.xlsx").Sheets(1)
Next
End Sub
