Excel Macro/VBA: How to have the macro pick a specific tab?-CodePudding

I have this macro that pulls the first tab of every file and saves it to a master file, however, I had someone help with the coding to put it together. I now want to grab that last tab, better yet a specific tab, "Data Tab." Can someone look at the following coding and help me add that? Thank you all!

Sub MASTER_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)
 
                
                   countSheets = countSheets   1
                    wbkSrcBook.Sheets(1).Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
                Next
 
                wbkSrcBook.Close SaveChanges:=False
 
            
 
            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

I tried reading through this, but couldn't figure it out.

CodePudding user response：

How to convert a sheet to plain values.

Sub Example_Convert_Sheet_To_Values()
Sheet2.UsedRange.Value = Sheet2.UsedRange.Value
'OR
wbkSrcBook.Worksheets("Data Tab").UsedRange.Value = wbkSrcBook.Worksheets("Data Tab").UsedRange.Value
'OR (What you most likely need):
wbkCurBook.Sheets(wbkCurBook.Sheets.Count).UsedRange.Value = wbkCurBook.Sheets(wbkCurBook.Sheets.Count).UsedRange.Value
End Sub

CodePudding user response：

It looks like you got some good tips here. I wanted to see this through, so here is my version. It has some comments that may be helpful.
I've incorporated @Tim William 's suggestion.

Sub MASTER_MergeExcelFiles()
  Dim fnameList, fnameCurFile ' these are variants
  Dim countFiles&, countSheets& ' these are Long
  Dim wksCurSheet As Worksheet  '
  Dim wbkCurBook As Workbook, wbkSrcBook As Workbook ' need "as .." on both
  
  fnameList = Application.GetOpenFilename( _
    FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", _
    Title:="Choose Excel files to merge", MultiSelect:=True)
  If VarType(fnameList) = vbBoolean Then ' this checks they hit "Cancel"
    MsgBox "No files selected", Title:="Merge Excel files"
' If (UBound(fnameList) > 0) Then ' not needed because previous "If" takes care of "Canceled"
  Else
'   countFiles = 0 ' not needed
'   countSheets = 0 ' not needed
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Set wbkCurBook = ActiveWorkbook
    For Each fnameCurFile In fnameList
      countFiles = countFiles   1
      Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)
      countSheets = countSheets   1
      wbkSrcBook.Sheets("Data Tab").Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
      wbkSrcBook.Close SaveChanges:=False
    Next fnameCurFile ' more clear?
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
  End If
' End If ' with deleted "If"
End Sub