VBA Help -- Moving First Sheet from Multiple workbooks to One Workbook

[mitchdb1]

Hi I'm having issues. I can move all of the sheets to the new Workbook, however, the first sheet on the main workbook gets copied 30 times, what am I missing? Thank you!

Sub MergeExcelFiles()
Dim fnameList, fnameCurFile As Variant
Dim countFiles, countSheets As Integer
Dim wksCurSheet As Worksheet
Dim wbkCurBook, wbkSrcBook As Workbook

fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)

If (vbBoolean <> VarType(fnameList)) Then

If (UBound(fnameList) > 0) Then
countFiles = 0
countSheets = 0

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set wbkCurBook = ActiveWorkbook

For Each fnameCurFile In fnameList
countFiles = countFiles + 1

Set wbkSrcBook = Workbooks.Open(FileName:=fnameCurFile)

For Each wksCurSheet In wbkSrcBook.Sheets
countSheets = countSheets + 1
Sheets(1).Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
Next

wbkSrcBook.Close SaveChanges:=False

Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
End If

Else
MsgBox "No files selected", Title:="Merge Excel files"
End If
End Sub

 

[StephenCrump]

Welcome to the Forum!

I'm guessing that you want:

wbkSrcBook.Sheets(1).Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)

I'm not sure why this is inside a loop? I'm guessing you want the first sheet from every wbkSrcBook copied once , rather than n times, where n is the sheet count?

 

[mitchdb1]

Thank you! I'm quite a newbie with VBA, so any help is most appreciated! What's a loop?

I tried using that edit (thank you for that), however, now I receive 30 copies of sheet 1 of wbkSrcBook 1 and 2 copies of sheet 1 of wbkSrcBook 2. I do want the first sheet from every wbksrcbook copied once. Is there another way to do this? Or is there something I'm doing wrong in the code? Thank you!

 

[StephenCrump]

'Here's the loop I was referring to.
'If there are N sheets in wbkSrcBook, this snippet of code will loop N times and hence create N copies of Sheet 1.
For Each wksCurSheet In wbkSrcBook.Sheets
    countSheets = countSheets + 1
    wbkSrcBook.Sheets(1).Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
Next

'I think you just want:
wbkSrcBook.Sheets(1).Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)

Do you need countSheets? It looks like the only relevant count is countFiles?

 

[mitchdb1]

This is perfect! Thank you so much! I'm sure I'll be back for when I have more issues.

 

[StephenCrump]

I'm glad we could help. And you're welcome back any time.