Delete the empty sheets from a created file

[An Quala]

Hello, I use the following code to make a new file after deleting the certain columns from the old file, but I want to add one more thing here, if any of the three sheets ('Sponsored Products Campaigns', 'Sponsored Brands Campaigns' ,'Sponsored Display Campaigns') are empty (i.e. A2 is empty) then don´t import that sheet at all. Ideally don´t delete the columns as well since that is unnecessary in case of empty sheets but ultimately I don´t want those sheets in the new file.

Can anyone please help me do that?

For Each x In Split("Sponsored Products Campaigns|Sponsored Brands Campaigns|Sponsored Display Campaigns", "|")
    If Worksheets(x).UsedRange.Address <> "$A$1" Then
 Campaigns "          Select Case x"
                Case "Sponsored Products Campaigns"
                            Worksheets(x).Range("F:G,J:Q,S:AA,AC:AU").EntireColumn.Delete
                Case "Sponsored Brands"
                            Worksheets(x).Range("E:F,J:N,P:U,W:AY").EntireColumn.Delete
                Case "Sponsored Display Campaigns"
                            Worksheets(x).Range("E:E,G:G,I:O,Q:Y,AA:AP").EntireColumn.Delete
          End Select
     End If
Next

 

[Joe4]

Is it possible for you to edit the code like this if easier? I hardly know VBA basics, thank you!

Where did you get this VBA code from then?

I was going to try to edit it, but I do not understand what you are doing with this part here:

     For Each x In Split("Sponsored Products Campaigns|Sponsored Brands Campaigns|Sponsored Display Campaigns", "|")
          s = s & Sheets(CStr(x)).Range("A" & Rows.Count).End(xlUp).Row - 1 & " "     'collect the last rows and use the space as separator
     Next
     sp = Split(s)                                              'split string on space
     MsgBox sp(0) & " SP, " & sp(1) & " SB and " & sp(2) & " SD targets have been optimized in " & Format(dTime, "0.0") & "seconds."    'make the text

What are you doing with this part of the code here?

 

[An Quala]

Where did you get this VBA code from then?

Someone from this forum wrote me this code a few months back.

What are you doing with this part of the code here?

This part is to count the number of rows present in the sheet currently and then show the numbers in the msgbox. It is irrelevant to the creation of the new sheet, apparently, thanks!

 

[Joe4]

OK, then try replacing the code under that with this:

    With ThisWorkbook.Sheets(Array("Sponsored Products Campaigns", "Sponsored Display Campaigns", "Sponsored Brands Campaigns"))
        .Copy
    End With
    
    Dim ws As Worksheet
    For Each ws In Worksheets
        If (ws.UsedRange.Address <> "$A$1") And (ws.Range("A2") <> "") Then
            Select Case ws.Name
                Case "Sponsored Products Campaigns"
                    ws.Range("F:G,J:Q,S:AA,AC:AU").EntireColumn.Delete
                Case "Sponsored Brands Campaigns"
                    ws.Range("E:F,J:N,P:U,W:AY").EntireColumn.Delete
                Case "Sponsored Display Campaigns"
                    ws.Range("E:E,G:G,I:O,Q:Y,AA:AP").EntireColumn.Delete
            End Select
        Else
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
        End If
    Next ws
    
    If MsgBox("Extra 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

 

[An Quala]

Thank you, thank you! It works perfectly.