Macro/VBA to loop and skip sheets based on cell value

Question

Can someone help me with a macro/VBA as what i'm asking for is a little advanced for me to achieve.

I have an excel Workbook with 10 sheets of data in it.

The macro i have basically re-formats and changes some elements on the sheet, its pretty basic but it does the job.

Previously i only had 1 sheet, but now i have 10 sheets i need my macro to loop through the sheets and make the same changes on each sheet BUT ONLY if the sheet DOES NOT contain the value 'UK' in cell A1. If a sheet contains UK in cell A1 i want it to skip that sheet, make no changes and move onto the next.

For examples sake, lets say i want to delete column H, and change the text colour to red. How would i do this using the rules i've outlined above?

I hope this makes sense.

Thanks in advance

FYI i'm using excel 2016

UPDATE:

The macro i have which i recorded is as follows;

Sub Sort_Data()
'
' Sort_Data Macro
'

'
    Sheets("Sheet2").Select
    Columns("AG:AI").Select
    Selection.Delete Shift:=xlToLeft
    Columns("AE:AE").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:AA").Select
    Range("AA1").Activate
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").Select
    Selection.Cut
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Columns("J:M").Select
    Selection.Cut
    Columns("H:H").Select
    Selection.Insert Shift:=xlToRight
    Columns("B:B").Select
    Selection.Copy
    Columns("P:P").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Columns("P:P").Select
    Application.CutCopyMode = False
    ExecuteExcel4Macro _
        "FORMULA.REPLACE(""$"","""",2,1,FALSE,FALSE,,FALSE,FALSE,FALSE,FALSE)"
    Range("Q2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-9]"
    Selection.AutoFill Destination:=Range("Q2:Q6")
    Range("Q2:Q6").Select
    Columns("H:H").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("R:R").Select
    Selection.Copy
    Columns("H:H").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Columns("Q:R").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("H1").Select
    ExecuteExcel4Macro "PATTERNS(0,0,0,,2,2,0,0)"
    Selection.Locked = True
    Selection.FormulaHidden = False
    ActiveCell.FormulaR1C1 = "Efficiency"
    Columns("B:O").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Cells.Select
    Selection.Columns.AutoFit
    Range("A1").Select
End Sub

I tried and failed to use the loop function explained here:

Microst Support

But i just can't grasp it. And i have no idea what so ever how to implement the 'skip sheets based on A1 value'

Thanks

Answer 1

This can be done fairly simply to allow you to just run one execution of VBA code rather than on a per sheet basis.

Public Sub AmendSheets()
    Dim i As Integer
' Start a count to go from sheet 1 to the total number of sheets
    For i = 1 To Application.Sheets.Count
' Select each sheet individually
    Worksheets(i).Activate

' Check for UK to see if changes are required
    If Range("A1").Value <> "UK" Then
' Put your changes in here

      End If
    Next i
End Sub

Answer 2

This is the solution that you need:

Public Function SheetReview()
    Dim wkb As Workbook
    Dim wks As Worksheet
    Set wkb = ThisWorkbook 'This object references the workbook
    wscount = wkb.Worksheets.Count 'Number of sheets in the workbook
    For i = 1 To wscount ' Loop one by one the sheets
        Set wks = wkb.Worksheets(i) 'Assign the current sheeet to object wks
        If wks.Cells(1, 1) <> "UK" Then ' If cell A1 is not UK
            With wks 'Let's work on the object wks
                '**********************
                'From here goes you code:
                'Sheets("Sheet2").Select <- Now you don't need this. it's referenced in the object wks
                Columns("AG:AI").Select
                Selection.Delete Shift:=xlToLeft
                .
                .
                .






            End With
        End If
    Next i
End Function

Open VBA / Macros with Alt+F11, right-click Workbook and insert a new module.

Paste the code in the right side.