VBA to Copy Multiple sheets to new workbook & save as .xlsb or .xlsx

[rahildhody]

Hi,

I have a vba code that needs to copy an array of sheets from one workbook & paste as values to another workbook & save the destination workbook as either xlsx or xlsx.

Below is the code:

Sub SaveSheetsAsCSV()

    Dim ws As Worksheet, ws1 As Worksheet
    Dim newWorkbook As Workbook, wbSource As Workbook, wbDestination As Workbook
    Dim sheetNames As Variant
    Dim rngFilePath As Range, rngScenario As Range, rngVersion As Range
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    ' Set the scenario, version & file path to save the new files
    Set rngFilePath = ThisWorkbook.Names("filepath").RefersToRange
    Set rngScenario = ThisWorkbook.Names("scenario").RefersToRange
    Set rngVersion = ThisWorkbook.Names("version").RefersToRange
    
    If Right(rngFilePath, 1) <> "\" Then
        rngFilePath = rngFilePath & "\"
    End If
        

'------------------------Create new workbook for budget assumptions template------------------------

    ' Set the source workbook
    Set wbSource = ThisWorkbook ' Change to the source workbook
    
    ' Set the destination workbook
    Set wbDestination = Workbooks.Add ' Change to the destination workbook
    
    ' Array of sheet names to be copied
    sheetNames = Array("Budget Inputs>>>", "Originations & Rates_budget", "Uniform Rolling Inputs", "Collective Provisions", "AUM_Collective Prov_budget", "CoF_budget") ' Add the names of the sheets you want to copy
    
    'copy array of sheets to the new workbook
    wbSource.Sheets(sheetNames).Copy Before:=wbDestination.Sheets(1)
    
    ' Paste as values and formatting
    For Each ws1 In wbDestination.Worksheets
    
            On Error Resume Next
                ws1.ShowAllData
            On Error GoTo 0
        
            ws1.Cells.Copy
            ws1.Range("A1").PasteSpecial Paste:=xlValues
        
    Next ws1

    ' Save the new workbook as xlsx with the worksheet name
    wbDestination.SaveAs rngFilePath & "Budget Assumptions Template_" & rngScenario & "_" & rngVersion & ".xlsb", FileFormat:=50

'wbDestination.SaveAs rngFilePath & "Budget Assumptions Template_" & rngScenario & "_" & rngVersion & ".xlsx", FileFormat:=51
    
    ' Close the new workbook without saving changes to it
    wbDestination.Close SaveChanges:=False



'------------------------Create new workbooks for ANAPLAN Upload files------------------------

    For Each ws In ThisWorkbook.Sheets(Array("MORT MET01 Upload", "MORT MET05 Upload", "MORT MET11 Upload", "MORT INP01 Upload", "MORT MET17 Upload", "DATAHUB LOA06"))
            
            On Error Resume Next
                ws.ShowAllData
            On Error GoTo 0
            
            Set newWorkbook = Workbooks.Add
            
            ' Rename the default sheet to the current worksheet name
            newWorkbook.Sheets(1).Name = ws.Name
            
            ' Copy the data from the original sheet to the new sheet
            ws.Cells.Copy newWorkbook.Sheets(1).Range("A1")
            
            ' Save the new workbook as CSV with the worksheet name
            newWorkbook.SaveAs rngFilePath & ws.Name & "_" & rngScenario & "_" & rngVersion & ".csv", xlCSV
            
            ' Close the new workbook without saving changes to it
            newWorkbook.Close SaveChanges:=False

    Next ws
    
    
  
    ' Release objects from memory
    Set wbSource = Nothing
    Set wbDestination = Nothing
    Set newWorkbook = Nothing
    Set ws = Nothing
    Set ws1 = Nothing
    
    
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    MsgBox "CSV files have been created and saved for specific worksheets.", vbInformation
End Sub

If i try using the commented code to saveas .xlsx, then the code skips the saving & jumps straight to the 2nd half of the code (which works perfectly) & if i use saveas .xlsb, then it gives me an error & asks me to debug or end the code & doesnt perform the function at all.

Not sure what I'm doing wrong. Could someone please assist with this?

Thanks in advance.

 

[rlv01]

If i try using the commented code to saveas .xlsx, then the code skips the saving & jumps straight to the 2nd half of the code (which works perfectly) & if i use saveas .xlsb, then it gives me an error & asks me to debug or end the code & doesnt perform the function at all.

What was the error?

Are you working with normal file paths, or are you trying to work with 'https' style SharePoint / OneDrive paths?

 

[rahildhody]

working with normal file paths for excel.

not sure what the error is. sometimes just stops the code & asks me to debug/end the code or runs the 2nd half of the code & doesnt save the first half at all.

I just ran the code & it saved the files from the 2nd half of the code, but didnt save the Budget Assumptions Template_v17 file with the 5-6worksheets as values. Just completely skipped past that code by the looks of things.

 

[rlv01]

Are you sure you are giving the save-as command a valid file path? An invalid file path would be the normal reason for code like yours producing an error at that line.

 

[rlv01]

Also, if you use the VBE to single step through the code with F8, does it still behave the same way?

 

[rahildhody]

it gave me a runtime error 1004

 

[rahildhody]

Also, if you use the VBE to single step through the code with F8, does it still behave the same way?

no it doesnt. if i step into the code with F8, it works flawlessly & saves the workbook down in the folder. its only when its run on its own that it skips it.

 

[rahildhody]

Are you sure you are giving the save-as command a valid file path? An invalid file path would be the normal reason for code like yours producing an error at that line.

I believe so, as its the same file path/naming convention for the 2nd half of the code as well & that works perfectly.

 

[rahildhody]

C:\Users\rdhody\Desktop\CY24 Month End\Test\

this is the file path that i use. & sometimes the user could omit the "\" at the end, so i've put in a test to check if it exists, & if not, put in the "\" at the end

 

[rahildhody]

Think i mightve figured out where the issue lies. the company requires us to fill our this sensitivity for each workbook created.

this is not showing up when i run the code. is there a way to ensure that this dialogue box comes up everytime the file gets to the saveas stage?