Check if there's data in a sheet before executing the code, if not then jump to the next sheet

An Quala

Board Regular
Joined
Mar 21, 2022
Messages
146
Office Version
  1. 2021
Platform
  1. Windows
Hi, In the following code, there are three sheets named as 'Sponsored Products Campaigns', 'Sponsored Brands Campaigns' and 'Sponsored Display Campaigns'

I want it to do the following thing,

Check if A1 of 1st sheet is empty then directly jump to the 2nd sheet and similarly to the 3rd sheet, if in case all the 3 sheets are empty then return a msgbox as "All 3 Sheets are empty"

In other words ignore the empty sheets but work on the sheets with data among all three sheets,

Thank you.




VBA Code:
Sub workingp()

    Dim c As Range, va, x
     For Each x In Split("Sponsored Products Campaigns|Sponsored Brands Campaigns|Sponsored Display Campaigns", "|")
        Set c = Worksheets(x).UsedRange
        va = c.Value
        Worksheets(x).Cells.NumberFormat = "General"
        c = va
    Next
    
    'Sponsored Products Campaigns
    
    Sheets("Sponsored Products Campaigns").Select
    
    Columns("D:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Columns("Y:Y").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("G2").FormulaR1C1 = "=XLOOKUP(RC[1],R2C8:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C8,R2C10:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C10)"
    
    Range("G2").AutoFill Destination:=Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row)
    
        Columns("G:G").Copy
    Columns("G:G").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

With Sheets("Sponsored Products Campaigns").Cells(1).CurrentRegion.Resize(, 49)
    With .Offset(1).Resize(.Rows.Count - 1).Columns(49)
        .Value = .Parent.Evaluate("=IF((" & .Offset(, -47).Address & "<>""Keyword"")*(" & .Offset(, -47).Address & "<>""Product Targeting"")+(" & .Offset(, -30).Address & "<>""enabled"")+(" & .Offset(, -4).Address & "<>""enabled"")+(" & .Offset(, -3).Address & "<>""enabled""),"""",1)")
         On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
         On Error GoTo 0
        .ClearContents
    End With
End With

    Range("F2").FormulaR1C1 = _
        "=XLOOKUP(RC7,'Control Panel'!R9C4:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C4,'Control Panel'!R9C5:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C5)"
    Range("F2").AutoFill Destination:=Range("F2:F" & Cells(Rows.Count, 1).End(xlUp).Row)
    Sheets("Control Panel").Select
    Range("D2:E2").Copy
    Sheets("Sponsored Products Campaigns").Select
    Range("D2").Select
    ActiveSheet.Paste
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC5<>"""",""update"","""")"
    Range("C2:E2").AutoFill Destination:=Range("C2:E" & Cells(Rows.Count, 1).End(xlUp).Row)
    Range("Y1").NumberFormat = "General"
    Range("Y1").FormulaR1C1 = "Bid Change %"
    Range("Y2").FormulaR1C1 = "=IFERROR((RC4-RC24)/RC24,"""")"
    Range("Y2").Style = "Percent"
    Range("Y2").AutoFill Destination:=Range("Y2:Y" & Cells(Rows.Count, 1).End(xlUp).Row)
    Columns("Y:Y").Copy
    Columns("Y:Y").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("C:F").Copy
    Columns("C:F").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("F:G").Delete Shift:=xlToLeft
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("V2").Select
    ActiveSheet.Paste
    Range("E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("Q2").Select
    ActiveSheet.Paste
    Columns("D:E").Delete Shift:=xlToLeft

With Sheets("Sponsored Products Campaigns").Cells(1).CurrentRegion.Resize(, 45)
    With .Offset(1).Resize(.Rows.Count - 1).Columns(45)
        .Value = .Parent.Evaluate("=IF((" & .Offset(, -42).Address & "<>""update""),"""",1)")
         On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
         On Error GoTo 0
        .ClearContents
    End With
End With

Range("A1:AR" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter

'Sponsored Brands Campaigns

Sheets("Sponsored Brands Campaigns").Select
    
    Columns("D:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Columns("X:X").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("G2").FormulaR1C1 = "=XLOOKUP(RC[1],R2C8:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C8,R2C10:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C10)"
    
    Range("G2").AutoFill Destination:=Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row)
    
        Columns("G:G").Copy
    Columns("G:G").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

With Sheets("Sponsored Brands Campaigns").Cells(1).CurrentRegion.Resize(, 54)
    With .Offset(1).Resize(.Rows.Count - 1).Columns(54)
        .Value = .Parent.Evaluate("=IF((" & .Offset(, -52).Address & "<>""Keyword"")*(" & .Offset(, -52).Address & "<>""Product Targeting"")+(" & .Offset(, -36).Address & "<>""running"")*(" & .Offset(, -36).Address & "<>""other"")+(" & .Offset(, -37).Address & "<>""enabled"")+(" & .Offset(, -3).Address & "<>""enabled""),"""",1)")
         On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
         On Error GoTo 0
        .ClearContents
    End With
End With
    Range("F2").FormulaR1C1 = _
        "=XLOOKUP(RC7,'Control Panel'!R9C4:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C4,'Control Panel'!R9C5:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C5)"
    Sheets("Control Panel").Select
    Range("D4:E4").Copy
    Sheets("Sponsored Brands Campaigns").Select
    Range("D2").Select
    ActiveSheet.Paste
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC5<>"""",""update"","""")"
    Range("C2:F2").AutoFill Destination:=Range("C2:F157")
    Range("X2").FormulaR1C1 = "=IFERROR((RC4-RC23)/RC23,"""")"
    Range("X2").Style = "Percent"
    Range("X1").NumberFormat = "General"
    Range("X1").FormulaR1C1 = "Bid Change %"
    Range("X2").AutoFill Destination:=Range("X2:X" & Cells(Rows.Count, 1).End(xlUp).Row)
    Columns("X:X").Copy
    Columns("X:X").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("C:F").Copy
    Columns("C:F").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("F:G").Delete Shift:=xlToLeft
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("U2").Select
    ActiveSheet.Paste
    Range("E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("O2").Select
    ActiveSheet.Paste
    Columns("D:E").Delete Shift:=xlToLeft
With Sheets("Sponsored Brands Campaigns").Cells(1).CurrentRegion.Resize(, 49)
    With .Offset(1).Resize(.Rows.Count - 1).Columns(49)
        .Value = .Parent.Evaluate("=IF((" & .Offset(, -46).Address & "<>""update""),"""",1)")
         On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).EntireRow.Select
         On Error GoTo 0
        .ClearContents
    End With
End With
 
    Range("A1:AV" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter
    
    'Sponsored Display Campaigns
 
    Sheets("Sponsored Display Campaigns").Select
    
    Columns("D:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Columns("Y:Z").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("G2").FormulaR1C1 = "=XLOOKUP(RC[1],R2C8:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C8,R2C9:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C9)"
    
    Range("G2").AutoFill Destination:=Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row)
    
        Columns("G:G").Copy
    Columns("G:G").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

With Sheets("Sponsored Display Campaigns").Cells(1).CurrentRegion.Resize(, 48)
    With .Offset(1).Resize(.Rows.Count - 1).Columns(48)
        .Value = .Parent.Evaluate("=IF((" & .Offset(, -46).Address & "<>""Audience Targeting"")*(" & .Offset(, -46).Address & "<>""Product Targeting"")+(" & .Offset(, -31).Address & "<>""enabled"")+(" & .Offset(, -5).Address & "<>""enabled"")+(" & .Offset(, -4).Address & "<>""enabled""),"""",1)")
         On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
         On Error GoTo 0
        .ClearContents
    End With
End With
    Range("Z2").FormulaR1C1 = "=IF(RC24="""",RC45,RC24)"
    Range("Z2").AutoFill Destination:=Range("Z2:Z" & Cells(Rows.Count, 1).End(xlUp).Row)
    Columns("Z:Z").Copy
    Columns("X:X").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("X1").Select
    Selection.NumberFormat = "General"
    ActiveCell.FormulaR1C1 = "Bid"
    Columns("Z:Z").Delete Shift:=xlToLeft
    Range("F2").FormulaR1C1 = _
        "=XLOOKUP(RC7,'Control Panel'!R9C4:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C4,'Control Panel'!R9C5:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C5)"
    Range("F2").AutoFill Destination:=Range("F2:F" & Cells(Rows.Count, 1).End(xlUp).Row)
    Sheets("Control Panel").Select
    Range("D6:E6").Copy
    Sheets("Sponsored Display Campaigns").Select
    Range("D2").Select
    ActiveSheet.Paste
    Range("C2").FormulaR1C1 = "=IF(RC5<>"""",""update"","""")"
    Range("C2:E2").AutoFill Destination:=Range("C2:E" & Cells(Rows.Count, 1).End(xlUp).Row)
    Range("C2:E" & Cells(Rows.Count, 1).End(xlUp).Row).Select
    Range("Y1").NumberFormat = "General"
    Range("Y1").FormulaR1C1 = "Bid Change %"
    Range("Y2").FormulaR1C1 = "=IFERROR((RC4-RC24)/RC24,"""")"
    Range("Y2").Style = "Percent"
    Range("Y2").AutoFill Destination:=Range("Y2:Y" & Cells(Rows.Count, 1).End(xlUp).Row)
    Columns("Y:Y").Copy
    Columns("Y:Y").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("C:F").Copy
    Columns("C:F").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("F:G").Delete Shift:=xlToLeft
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("V2").Select
    ActiveSheet.Paste
    Range("E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("O2").Select
    ActiveSheet.Paste
    Columns("D:E").Delete Shift:=xlToLeft

With Sheets("Sponsored Display Campaigns").Cells(1).CurrentRegion.Resize(, 43)
    With .Offset(1).Resize(.Rows.Count - 1).Columns(43)
        .Value = .Parent.Evaluate("=IF((" & .Offset(, -40).Address & "<>""update""),"""",1)")
         On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
         On Error GoTo 0
        .ClearContents
    End With
End With

Range("A1:AP" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter

MsgBox (Done)

End Sub
 
Hi, for example this is the first part of the code, for 'Sponsored Products Campaigns' sheet, I just want to add "If cellA1 <> "" Then" part here and for that I need to reference it the same way like With Sheets("Sponsored Products Campaigns"), can you help me make this code referencing the same way, so I can add the condition for cellA1 <> "", all this code is for same sheet yet referenced multiple times. I understand one is referenced to the range and other is for the sheet, but they both can be referenced the same way? If not then how to add if then statement for the sheet?




Rich (BB code):
Sub workingp()
 
    'Sponsored Products Campaigns
    
    Sheets("Sponsored Products Campaigns").Select
    
    Columns("D:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Columns("Y:Y").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("G2").FormulaR1C1 = "=XLOOKUP(RC[1],R2C8:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C8,R2C10:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C10)"
    
    Range("G2").AutoFill Destination:=Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row)
    
        Columns("G:G").Copy
    Columns("G:G").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

With Sheets("Sponsored Products Campaigns").Cells(1).CurrentRegion.Resize(, 49)
    With .Offset(1).Resize(.Rows.Count - 1).Columns(49)
        .Value = .Parent.Evaluate("=IF((" & .Offset(, -47).Address & "<>""Keyword"")*(" & .Offset(, -47).Address & "<>""Product Targeting"")+(" & .Offset(, -30).Address & "<>""enabled"")+(" & .Offset(, -4).Address & "<>""enabled"")+(" & .Offset(, -3).Address & "<>""enabled""),"""",1)")
         On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
         On Error GoTo 0
        .ClearContents
    End With
End With

    Range("F2").FormulaR1C1 = _
        "=XLOOKUP(RC7,'Control Panel'!R9C4:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C4,'Control Panel'!R9C5:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C5)"
    Range("F2").AutoFill Destination:=Range("F2:F" & Cells(Rows.Count, 1).End(xlUp).Row)
    Sheets("Control Panel").Select
    Range("D2:E2").Copy
    Sheets("Sponsored Products Campaigns").Select
    Range("D2").Select
    ActiveSheet.Paste
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC5<>"""",""update"","""")"
    Range("C2:E2").AutoFill Destination:=Range("C2:E" & Cells(Rows.Count, 1).End(xlUp).Row)
    Range("Y1").NumberFormat = "General"
    Range("Y1").FormulaR1C1 = "Bid Change %"
    Range("Y2").FormulaR1C1 = "=IFERROR((RC4-RC24)/RC24,"""")"
    Range("Y2").Style = "Percent"
    Range("Y2").AutoFill Destination:=Range("Y2:Y" & Cells(Rows.Count, 1).End(xlUp).Row)
    Columns("Y:Y").Copy
    Columns("Y:Y").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("C:F").Copy
    Columns("C:F").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("F:G").Delete Shift:=xlToLeft
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("V2").Select
    ActiveSheet.Paste
    Range("E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("Q2").Select
    ActiveSheet.Paste
    Columns("D:E").Delete Shift:=xlToLeft

With Sheets("Sponsored Products Campaigns").Cells(1).CurrentRegion.Resize(, 45)
    With .Offset(1).Resize(.Rows.Count - 1).Columns(45)
        .Value = .Parent.Evaluate("=IF((" & .Offset(, -42).Address & "<>""update""),"""",1)")
         On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
         On Error GoTo 0
        .ClearContents
    End With
End With

Range("A1:AR" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
try this:
VBA Code:
  'Sponsored Products Campaigns
    
    Sheets("Sponsored Products Campaigns").Select
   If Range("A1") <> "" Then                         ' add this line
      
    Columns("D:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Columns("Y:Y").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("G2").FormulaR1C1 = "=XLOOKUP(RC[1],R2C8:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C8,R2C10:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C10)"
    
    Range("G2").AutoFill Destination:=Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row)
    
        Columns("G:G").Copy
    Columns("G:G").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

With Sheets("Sponsored Products Campaigns").Cells(1).CurrentRegion.Resize(, 49)
    With .Offset(1).Resize(.Rows.Count - 1).Columns(49)
        .Value = .Parent.Evaluate("=IF((" & .Offset(, -47).Address & "<>""Keyword"")*(" & .Offset(, -47).Address & "<>""Product Targeting"")+(" & .Offset(, -30).Address & "<>""enabled"")+(" & .Offset(, -4).Address & "<>""enabled"")+(" & .Offset(, -3).Address & "<>""enabled""),"""",1)")
         On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
         On Error GoTo 0
        .ClearContents
    End With
End With

    Range("F2").FormulaR1C1 = _
        "=XLOOKUP(RC7,'Control Panel'!R9C4:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C4,'Control Panel'!R9C5:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C5)"
    Range("F2").AutoFill Destination:=Range("F2:F" & Cells(Rows.Count, 1).End(xlUp).Row)
    Sheets("Control Panel").Select
    Range("D2:E2").Copy
    Sheets("Sponsored Products Campaigns").Select
    Range("D2").Select
    ActiveSheet.Paste
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC5<>"""",""update"","""")"
    Range("C2:E2").AutoFill Destination:=Range("C2:E" & Cells(Rows.Count, 1).End(xlUp).Row)
    Range("Y1").NumberFormat = "General"
    Range("Y1").FormulaR1C1 = "Bid Change %"
    Range("Y2").FormulaR1C1 = "=IFERROR((RC4-RC24)/RC24,"""")"
    Range("Y2").Style = "Percent"
    Range("Y2").AutoFill Destination:=Range("Y2:Y" & Cells(Rows.Count, 1).End(xlUp).Row)
    Columns("Y:Y").Copy
    Columns("Y:Y").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("C:F").Copy
    Columns("C:F").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("F:G").Delete Shift:=xlToLeft
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("V2").Select
    ActiveSheet.Paste
    Range("E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("Q2").Select
    ActiveSheet.Paste
    Columns("D:E").Delete Shift:=xlToLeft

With Sheets("Sponsored Products Campaigns").Cells(1).CurrentRegion.Resize(, 45)
    With .Offset(1).Resize(.Rows.Count - 1).Columns(45)
        .Value = .Parent.Evaluate("=IF((" & .Offset(, -42).Address & "<>""update""),"""",1)")
         On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
         On Error GoTo 0
        .ClearContents
    End With
End With

Range("A1:AR" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter
End If                                             ' add this line
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top