VBA 代码帮助 - 将多张工作表复制到新工作簿中（用于分发）

2024-7-8 • tag-icon

录制宏后，我无法编辑此 vba 代码。宏代码如下。我收到一条错误消息，提示“wsSplitBU.Copy After:=Aname.Sheets(1)”行中的限定符无效。

这里的想法是将这 4 张表复制到一个全新的工作簿中，然后在保存之前将它们粘贴为值。

这里的专家能否帮助编辑代码以使其更加灵活？

谢谢你！

   Sub Data_Cleanser()


Application.ScreenUpdating = False

Dim wsRaw As Worksheet
Set wsRaw = Sheets("RAW DATA")
Dim wsPivot As Worksheet
Set wsPivot = Sheets("Pivot_RAW_DATA")
Dim wsPivotM As Worksheet
Set wsPivotM = Sheets("Pivot")
Dim lastRowRD As Long
lastRowRD = wsRaw.Cells(Rows.Count, "A").End(xlUp).Row
Dim wbS As Workbook
Set wbS = Workbooks("Spend automator.xlsm")
Dim wsSplitBU As Worksheet
Set wsSplitBU = Sheets("Split BU (HUTAS)")
Dim wsLocalS As Worksheet
Set wsLocalS = Sheets("Localization Spend")
Dim wsPlantSp As Worksheet
Set wsPlantSp = Sheets("Bedok, Changi, Bandung Spend")

''''''''''''''''''''''''''''''
'Populate formula'
''''''''''''''''''''''''''''''

[Aa1].Resize(lastRowRD - 1, 1).FormulaR1C1 = ("BU Correction Generator")
[Aa2].Resize(lastRowRD - 1, 1).Formula = ("=VLOOKUP(N2,'BU CORRECTOR REFERENCE'!$A:$C,3,FALSE)")

''''''''''''''''''''''''''''''
'Refresh Pivot'
''''''''''''''''''''''''''''''

wsPivot.Select
ActiveSheet.PivotTables("PivotTable9").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
wsPivotM.Select
ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh

Dim Aname As String
Aname = ActiveWorkbook.Sheets(1).Range("A1").Value
Workbooks.Add

''''''''''''''''''''''''''''''
'Create new distributable workbook'
''''''''''''''''''''''''''''''

    wsPivotM.Copy
    wbS.Activate
    wsSplitBU.Copy After:=Aname.Sheets(1)
    wbS.Activate
    wsLocalS.Copy After:=Aname.Sheets(2)
    wbS.Activate
    wsPlantSp.Copy After:=Aname.Sheets( _
    3)
    Range("B4:M8").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Localization Spend").Select
    Range("B3:M19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("L1:M1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("L2").Select
    ActiveSheet.Paste
    Sheets("Split BU (HUTAS)").Select
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 3
    Range("C18:N46").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M1:N1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("M2").Select
    ActiveSheet.Paste
    Sheets("Pivot").Select
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:=SpendReport & ".xls"


End Sub

答案1

@akina 你能举个例子吗？ – Fadhilah Afif 19 分钟前

将工作表从此工作簿复制到另一个工作簿示例。

Sub copy_sheet()
    Workbooks.Open Filename:="c:\tmp\workbook2.xlsx"
    ThisWorkbook.Sheets("Sheet1").Copy Before:=Workbooks("workbook2.xlsx").Sheets("Sheet2")
    Workbooks("workbook2.xlsx").Save
    Workbooks("workbook2.xlsx").Close
End Sub

答案1

相关内容