combining multiple sheets into 1 sheet in a new excel, automated across multiple workbooks and naming the new sheet after the new workbook.

[miks]

I have multiple workbooks with multiple worksheets (the number of worksheets is consistent across the workbooks) and I would like to merge each workbooks sheets into a new sheet for each workbook in a new workbook. All the worksheets in each workbook only have 2 column headings that match, i want those to match up and to leave cells blank where there isnt a corresponding heading
All the tabs are pretty much called the same name (between workbooks not within) so i would like the new sheet to be named after the workbook.
This code is great for pulling the files over but im unsure how to modify it to merge each sheet ina workbook vertically (and in order sheet 3 then 1 then 2) and name it after the workbook.

multiple sheets
Sub CopySheets()
Application.ScreenUpdating = False
Dim fd As FileDialog, lRow As Long, vSelectedItem As Variant, srcWB As Workbook, desWB As Workbook
Set desWB = ThisWorkbook
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = True
If .Show = -1 Then
For Each vSelectedItem In .SelectedItems
Set srcWB = Workbooks.Open(vSelectedItem)
Sheets(Worksheets(3).Name, Worksheets(1).Name, Worksheets(2).Name).Copy after:=desWB.Sheets(desWB.Sheets.Count)
srcWB.Close False
Next
Else
End If
End With
Application.ScreenUpdating = True
End Sub

thanks in advance for any advice

 

[miks]

I have multiple workbooks with multiple worksheets (the number of worksheets is consistent across the workbooks) and I would like to merge each workbooks sheets into a new sheet for each workbook in a new workbook. All the worksheets in each workbook only have 2 column headings that match, i want those to match up and to leave cells blank where there isnt a corresponding heading
All the tabs are pretty much called the same name (between workbooks not within) so i would like the new sheet to be named after the workbook.
This code is great for pulling the files over but im unsure how to modify it to merge each sheet ina workbook vertically (and in order sheet 3 then 1 then 2) and name it after the workbook.

multiple sheets
Sub CopySheets()
Application.ScreenUpdating = False
Dim fd As FileDialog, lRow As Long, vSelectedItem As Variant, srcWB As Workbook, desWB As Workbook
Set desWB = ThisWorkbook
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = True
If .Show = -1 Then
For Each vSelectedItem In .SelectedItems
Set srcWB = Workbooks.Open(vSelectedItem)
Sheets(Worksheets(3).Name, Worksheets(1).Name, Worksheets(2).Name).Copy after:=desWB.Sheets(desWB.Sheets.Count)
srcWB.Close False
Next
Else
End If
End With
Application.ScreenUpdating = True
End Sub

thanks in advance for any advice

oops I meant Sheets(Array(Worksheets(3).Name, Worksheets(1).Name, Worksheets(2).Name)) but i want to merge these into 1 sheet named after the workbook for each workbook seperate sheet (with a column with which worksheet its from preferably)
the headers for the columns i want to match are spp and notes, the unmatched colums in too empty for where there is no match

 

[Alex Blakenburg]

Did you still want help with this ?