(Solved) – VBA Code Help – Copying multiple sheets into a new workbook (for distribution)

  • by
(solved)-–-vba-code-help-–-copying-multiple-sheets-into-a-new-workbook-(for-distribution)

I am having trouble editing this vba code after recording a macro. The macro code is as per below. There is always an error that appears at the line ” Sheets(“Split BU (HUTAS)”).Copy After:=Workbooks(“Book4″).Sheets(1)”.

The idea here is to duplicate these 4 sheets into a whole new workbook and then pasting them as values prior to saving.

Would an expert here be able to help with the editting of the code to make this more flexible?

Thank you!

Sub Macro7()
'
' Macro7 Macro
'

'
    Sheets("Pivot").Select
    Sheets("Pivot").Copy
    Windows("Spend automator.xlsm").Activate
    Sheets("Split BU (HUTAS)").Select
    Sheets("Split BU (HUTAS)").Copy After:=Workbooks("Book4").Sheets(1)
    Windows("Spend automator.xlsm").Activate
    Sheets("Localization Spend").Select
    Sheets("Localization Spend").Copy After:=Workbooks("Book4").Sheets(2)
    Windows("Spend automator.xlsm").Activate
    Sheets("Bedok, Changi, Bandung Spend").Select
    Sheets("Bedok, Changi, Bandung Spend").Copy After:=Workbooks("Book4").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:= _
        "C:Usersfadhilah.afifDesktopData Excel FilesDec 2019DEC 2019 ACTUALS SPEND.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *