An Quala
Board Regular
- Joined
- Mar 21, 2022
- Messages
- 146
- Office Version
- 2021
- Platform
- 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.
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