easy fix just need first sheet only in each file on this copy all first sheets into one workbook

[jordanburch]

Hi All,

I cant seem to figure out how to get this code to copy just the first sheet weather its active or not. Please help!

Jordan

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
              Worksheets(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

 

[Tupe77]

Do you mean like this?
I didn't find any other fault, except for the loop where you go through all the sheets. It produces multiple copies.

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)


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


          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

My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."

 

[jordanburch]

Do you mean like this?
I didn't find any other fault, except for the loop where you go through all the sheets. It produces multiple copies.

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)


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


          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

My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."

sweet wroked perfect