Excel宏/VBA：如何让宏选择一个特定的选项卡？

6ju8rftf 于 2022-12-20 发布在其他

关注(0)|答案(2)|浏览(282)

我有这个宏，拉每个文件的第一个选项卡，并将其保存到主文件，但是，我有别人的编码帮助，把它放在一起。我现在想抓住最后一个选项卡，更好的是一个特定的选项卡，“数据选项卡”。有人能看看下面的编码，并帮助我添加吗？谢谢大家！

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

我试着阅读了一遍，但没读出来。

excel

来源：https://stackoverflow.com/questions/74827561/excel-macro-vba-how-to-have-the-macro-pick-a-specific-tab

2条答案

按热度按时间

t8e9dugd1#

如何将工作表转换为普通值。

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

赞(0）回复(0）举报 2022-12-20

2izufjch2#

看起来你在这里得到了一些很好的提示。我想看到这一点通过，所以这里是我的版本。它有一些评论，可能会有帮助。
我采纳了蒂姆·威廉的建议。

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

赞(0）回复(0）举报 2022-12-20

我来回答

Excel宏/VBA：如何让宏选择一个特定的选项卡？

2条答案

相关问题

热门标签

最新问答