VBA 将工作表复制到新创建的文件-解网

问：

我有一个代码，可以筛选表中的唯一值，复制它们并粘贴到与唯一值同名的不同工作簿中。我还有两张工作表想添加到这些不同的工作簿中，没有过滤器或任何东西，只需复制和粘贴完全相同的工作表即可。这些工作表位于名为“Bonus”和“Time”的源文件中。如何将其合并到下面的代码中？非常感谢！

Sub filter()
Application.ScreenUpdating = False
Dim x As Range
Dim rng As Range
Dim rng1 As Range
Dim last As Long
Dim sht As String
Dim newBook As Excel.Workbook
Dim Workbk As Excel.Workbook

sht = "kalkulátor"

Set Workbk = ThisWorkbook

last = Workbk.Sheets(sht).Cells(Rows.Count, "b").End(xlUp).Row

With Workbk.Sheets(sht)
Set rng = .Range("A1:y" & last)
End With

Workbk.Sheets(sht).Range("B1:B" & last).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AA1"), Unique:=True

For Each x In Workbk.Sheets(sht).Range([AA2], Cells(Rows.Count, "AA").End(xlUp))

With rng
.AutoFilter
.AutoFilter Field:=2, Criteria1:=x.Value
.SpecialCells(xlCellTypeVisible).Copy

Set newBook = Workbooks.Add(xlWBATWorksheet)

newBook.Sheets.Add(After:=Sheets(Sheets.Count)).Name = x.Value
newBook.Activate
ActiveSheet.Paste
End With

newBook.SaveAs x.Value & ".xlsx"

newBook.Close SaveChanges:=False

Next x

' Turn off filter
Workbk.Sheets(sht).AutoFilterMode = False

With Application
.CutCopyMode = False
.ScreenUpdating = True
End With

End Sub

我试过了这个，但根本不对：

Sheets("Time").Copy After:=Workbooks.newBook.Sheets(Sheets.Count)

Excel VBA

    Dim aSht, oSht As Worksheet '**
    aSht = Array("Bonus", "Time") '**
    Set oSht = Workbk.Sheets(sht) '**
    For Each x In oSht.Range(oSht.[AA2], oSht.Cells(oSht.Rows.Count, "AA").End(xlUp)) '**
        With rng
            .AutoFilter
            .AutoFilter Field:=2, Criteria1:=x.Value
            .SpecialCells(xlCellTypeVisible).Copy
            Set newBook = Workbooks.Add(xlWBATWorksheet)
            newBook.Sheets.Add(After:=newBook.Sheets(newBook.Sheets.Count)).Name = x.Value
            ' newBook.Activate
            ActiveSheet.Paste
        End With
        Workbk.Sheets(aSht).Copy After:=newBook.Sheets(newBook.Sheets.Count) '**
        newBook.SaveAs x.Value & ".xlsx"
        newBook.Close SaveChanges:=False
    Next x

上一个：Excel 图表可按月份显示有间隙的日历数据

下一个：VBA 连接单元格范围，用“OR”的分隔符分隔，并在到达空白单元格时停止？

VBA 将工作表复制到新创建的文件

VBA to copy sheets to newly created files

评论