Save the 3 sheets in a new workbook every time macro runs.

[An Quala]

Hello, I want a code please that will save the 3 sheets in a new workbook. Sheet names are 'Sponsored Products Campaigns', 'Sponsored Brands Campaigns' and 'Sponsored Display Campaigns' after running the previous code. Before saving, it should also delete the column U in 'Sponsored Products Campaigns' and 'Sponsored Display Campaigns' and Column T in 'Sponsored Brands Campaigns'.

PS: I don't know what's the possibility of naming the new file though.

 

[kevin9999]

See if this gives you what you want. You can see where to name the new file in the code.

Sub Save_To_New()
    With ThisWorkbook.Sheets(Array("Sponsored Products Campaigns", "Sponsored Display Campaigns", "Sponsored Brands Campaigns"))
        .Copy
    End With
    
    With ActiveWorkbook
        .Worksheets("Sponsored Products Campaigns").Range("U:U").EntireColumn.Delete
        .Worksheets("Sponsored Display Campaigns").Range("U:U").EntireColumn.Delete
        .Worksheets("Sponsored Brands Campaigns").Range("T:T").EntireColumn.Delete
    End With
    
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\New File Name Here" & ".xlsx", FileFormat:=51
End Sub

 

[An Quala]

See if this gives you what you want. You can see where to name the new file in the code.

Sub Save_To_New()
    With ThisWorkbook.Sheets(Array("Sponsored Products Campaigns", "Sponsored Display Campaigns", "Sponsored Brands Campaigns"))
        .Copy
    End With
   
    With ActiveWorkbook
        .Worksheets("Sponsored Products Campaigns").Range("U:U").EntireColumn.Delete
        .Worksheets("Sponsored Display Campaigns").Range("U:U").EntireColumn.Delete
        .Worksheets("Sponsored Brands Campaigns").Range("T:T").EntireColumn.Delete
    End With
   
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\New File Name Here" & ".xlsx", FileFormat:=51
End Sub

Hi, thank you! It is working fine, but giving error when I am saving it the second time, I want it to rename a new file every time we run this code, maybe with the help of msgbox it can ask 'What is the name of this new file?' and then save the file with that name, also maybe it can ask 'Where to save?' because for now it is saving in my E/: Drive,

Thank you!

 

[kevin9999]

Hi, thank you! It is working fine, but giving error when I am saving it the second time, I want it to rename a new file every time we run this code, maybe with the help of msgbox it can ask 'What is the name of this new file?' and then save the file with that name, also maybe it can ask 'Where to save?' because for now it is saving in my E/: Drive,

Thank you!

Rather than reinvent the wheel, you might as well use the

Application.GetSaveAsFilename

method. Like this:

Sub Save_To_New_V2()
    With ThisWorkbook.Sheets(Array("Sponsored Products Campaigns", "Sponsored Display Campaigns", "Sponsored Brands Campaigns"))
        .Copy
    End With
   
    With ActiveWorkbook
        .Worksheets("Sponsored Products Campaigns").Range("U:U").EntireColumn.Delete
        .Worksheets("Sponsored Display Campaigns").Range("U:U").EntireColumn.Delete
        .Worksheets("Sponsored Brands Campaigns").Range("T:T").EntireColumn.Delete
    End With
   
    Application.GetSaveAsFilename ("Name the File")
End Sub

 

[An Quala]

Hi, it is giving saving window, but after saving it is not actually saving, just opening a new excel file name as "Book1", "Book2", etc, can you please check, thanks!

 

[kevin9999]

Hi, it is giving saving window, but after saving it is not actually saving, just opening a new excel file name as "Book1", "Book2", etc, can you please check, thanks!

OK, try the following. It should prompt the user for a filename and location.

Sub Save_To_New_V3()
    With ThisWorkbook.Sheets(Array("Sponsored Products Campaigns", "Sponsored Display Campaigns", "Sponsored Brands Campaigns"))
        .Copy
    End With
    
    With ActiveWorkbook
        .Worksheets("Sponsored Products Campaigns").Range("U:U").EntireColumn.Delete
        .Worksheets("Sponsored Display Campaigns").Range("U:U").EntireColumn.Delete
        .Worksheets("Sponsored Brands Campaigns").Range("T:T").EntireColumn.Delete
    End With
    
    Dim FileName As Variant
    FileName = Application.GetSaveAsFilename(FileFilter:="Microsoft Excel file (*.xlsx), *.xlsx")
    ActiveWorkbook.SaveAs FileName:=FileName
End Sub

 

[An Quala]

Hi, it is working perfectly, just one issue, in case if we don't want to save the file we click on cancel, so it got saved by itself with name False.xlsx, is there any solution to prevent that? Or if we even cancel to save as False.xlsx, it gives the error, so any solution to prevent this error too.

Also just like it deleted the 1 column in each sheet before saving, I want to delete more columns as well,

In "Sponsored Products Campaigns" Column U and AB:AR Entire Columns
In "Sponsored Brands Campaigns" Column T and AI:AW Entire Columns
In "Sponsored Display Campaigns" Column U and Y:AO Entire Columns

After deleting the mentioned columns give an option to save,


Highly appreciated, thank you.

 

[kevin9999]

In the code below, I've added a message box asking to confirm whether the user wants to save the file or not. If they answer no, then the edited file is simply discarded. I've also demonstrated how you can indicate multiple columns to delete.

Option Explicit
Sub Save_To_New_V4()
    With ThisWorkbook.Sheets(Array("Sponsored Products Campaigns", "Sponsored Display Campaigns", "Sponsored Brands Campaigns"))
        .Copy
    End With
    
    With ActiveWorkbook
        .Worksheets("Sponsored Products Campaigns").Range("U:U,AB:AR").EntireColumn.Delete
        .Worksheets("Sponsored Brands Campaigns").Range("T:T,AI:AW").EntireColumn.Delete
        .Worksheets("Sponsored Display Campaigns").Range("U:U,Y:AO").EntireColumn.Delete
    End With
    
    If MsgBox("Columns have been deleted" & vbNewLine & vbNewLine & _
        "Do you want to save as a new file?", vbYesNo, "Confirm") = vbNo Then
        ActiveWorkbook.Close SaveChanges:=0
        Exit Sub
    End If
    
    Dim FileName As Variant
    FileName = Application.GetSaveAsFilename(FileFilter:="Microsoft Excel file (*.xlsx), *.xlsx")
    ActiveWorkbook.SaveAs FileName:=FileName
End Sub

 

[An Quala]

Hello @kevin9999 I have been using this code since then and it is working fine, I am just facing a single issue, whenever I don't have to save a file in a new workbook which is 50% of the times, it still processes and loads and after making a new workbook, it asks if I want to save it or not, so is it possible that we ask this information before even processing this? So if select "No", then it should not do anything related to this, also if any of the 3 sheets is empty, it should also not process it (which could be find by checking the cell A2 of any sheet, It will save a lot of time, thank you for the help.