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

[An Quala]

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.

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

 

[An Quala]

BUMP

 

[offthelip]

I just saw your post about not getting an answer, I did think about answering this a few days ago but I decided not to becuase the code you posted did not seem to be relevant to the question that you asked. A lot of it was the sort of code which I wouldn't want to mess with because it is not the way I would do that sort of task. However I did decide I would quickly write a bit of code which hopefully will give you some help and answers the question you asked. :

    Dim c As Range, va, x
     For Each x In Split("Sponsored Products Campaigns|Sponsored Brands Campaigns|Sponsored Display Campaigns", "|")
        With Worksheets(x)
           cellA1 = .Range(.Cells(1, 1), .Cells(1, 1))
           If cellA1 <> "" Then
            Set c = Worksheets(x).UsedRange
            va = c.Value
            Worksheets(x).Cells.NumberFormat = "General"
            c = va
         End If
        End With
    Next

this only steps over the little bit of code within this loop not the rest of your code

 

[An Quala]

OK thanks, where should I place this code? because my code has 3 parts, I think your code should be placed before the start of every part to check if there is data or not,

I shared my whole code with comment where each part is beginning because I thought we would need a if else if condition before the start of each part,

Part 1: Sponsored Products Campaigns

Part 2: Sponsored Brands Campaigns

Part 3: Sponsored Display Campaigns

Thank you.

 

[offthelip]

your code is a mess: you are addressing worksheets in a number of differents ways :

     For Each x In Split("Sponsored Products Campaigns|Sponsored Brands Campaigns|Sponsored Display Campaigns", "|")
        Set c = Worksheets(x).UsedRange

and then

 Sheets("Sponsored Products Campaigns").Select

and then

With Sheets("Sponsored Products Campaigns").Cells(1).CurrentRegion.Resize(, 49)
    With .Offset(1).Resize(.Rows.Count - 1).Columns(49)                                              ' does this actually work? it seems very dangerous to me, you don't know how big the current region is!!

and then after this last one you address a range

Range("F2").FormulaR1C1 = _

which defaults back to the active sheet. To do the check on A1 for each of these needs to be handled slightly differently.

You have got 14 with statements and 12 select sheet statements yet you are dealing with only 3 worksheets, this is very inefficient , difficult to follow and liable to error.
What I suggest is you reorder your code so that you are dealing with one worksheet at a time, choose either to do everything by selecting the sheet and dealing with it as the active sheet or preferably by using a with statement and dealing with it without making it the active sheet. swapping between these two methods doesn't make sense and makes errors very likely
I suspect this is why nobody wanted to answer your question even the basic question of how to check A1 is not blank before running code on workhseet is very very easy to answer.
Once you got your code reordered you only need a simple if statement around the code for each sheet, if you have selected the sheet then

     If (Range("A1") <> "") Then
     '''''
     End If

If you are using with statements then

           cellA1 = .Range(.Cells(1, 1), .Cells(1, 1))
           If cellA1 <> "" Then
            '....
            End If

 

[An Quala]

Hi I don't know about the codes and these are just done with macros but the good thing every sheet work is separate not combined, only in the first part which I will try to separate, otherwise all are separate, so is it possible for you to convert the first part 'Sponsored Products Campaigns' into "with" function please, then I will do for the rest.

 

[offthelip]

here you are:

With Sheets("Sponsored Products Campaigns")
    cellA1 = .Range(.Cells(1, 1), .Cells(1, 1))
   If cellA1 <> "" Then
    .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
   End If
End With

 

[An Quala]

Hi, can you please elaborate a little bit more, I am still confused, like after your "End With", there is again "With Sheets" code, so will we not keep all in the same with? You can check my code for better idea.

 

[offthelip]

I think the only thing I can do is demonstrate to you the different methods of addressing a worksheet, there are three different methods you have used to address the worksheets. you need to be clear as to which method you are using. the 3 different bits of this code all do the same thing. :

Sub test()
Worksheets("Sheet2").Select  ' select worksheet 2 just to make sure we don't start on workhseet 1
' using the with statement
With Worksheets("sheet1")
  tt = .Range("A1")
  MsgBox tt & "with"
End With
' uisng full addressing
 ts = Worksheets("sheet1").Range("a1")
  MsgBox ts & "full address"
  ' using Select the worksheet
 Worksheets("sheet1").Select
  tq = Range("a1")
  MsgBox tq & "select"


End Sub

 

[offthelip]

I just realised what you question was and the asnwer is they are not the same:

With Sheets("Sponsored Products Campaigns")

states that in the following code anything that starts with a dot means it refers to the worksheet Sponsored Products Campaigns
while your statement:

With Sheets("Sponsored Products Campaigns").Cells(1).CurrentRegion.Resize(, 49)

is a statement that defines a RANGE not worksheet, so this means that any code that follows it with a dot must be referring to range NOT a worksheet
completely different. You then following one with statement with another so that changes the meaning of the dot again. Just looking at your code it is very difficult to tell what you are trying to do. ( which is why I didn't want to try modifying it.