Worksheets array syntax error

[Emeng]

Hi all

The following code has a problem which I am unable to identify.

Sub IdentifyClashes()
    Dim ws As Worksheet
    Dim FX2 As String
    Dim FX3 As String
    Dim lastrow        As Long
   
    FX2 = "=IF(AND(C2=C3,A2+J2>A3),""REVIEW"","""")"
    FX3 = "=IF(O2=P2,P2,(P2 & O2))"
    lastrow = Range("B" & Rows.Count).End(xlUp).Row
   
    Application.ScreenUpdating = False
   
    For Each ws In ActiveWorkbook.Sheets(Array("Access", "Cranes"))
    With ws
        Range("O2:O" & lastrow).Formula = FX2
        Range("P3:P" & lastrow).Formula = FX2
        Range("Q2:Q" & lastrow).Formula = FX3
            Columns("A:A").Insert Shift:=xlToRight
            Range("R1").Value = "STATUS"
            Columns("R:R").Copy
            Range("A1").PasteSpecial xlPasteValues
        Columns("A:A").HorizontalAlignment = xlCenter
        Columns("N:R").Delete
    End With
    Next ws
End Sub

It is supposed to insert formulas into 3 columns, insert a new column & copy/paste results from last formula to the new column, tidy up (delete formulas)... then move to the next sheet.

However it is inserting two columns in one sheet and not cleaning up the other?

I think it's a simple syntax error but I can't seem to find it.

Any help is much appreciated.

Thanks in advance

Mark

 

[Hui]

Emeng

I would setup Excel and VB in side by side windows
In your code comment out the Application.ScreenUpdating = False line

then run the code stepping though it line by line using F8
this way you will see what and where it is going wrong

Also your file is an *.xlsx file which can't contain Macros
Save your file as a *.xlsm or *.xlsb file type, which both can

 

[Hui]

I would change

  For Each ws In ActiveWorkbook.Sheets(Array("Access", "Cranes"))
  With ws
  Range("O2:O" & lastrow).Formula = FX2

to

  For Each ws In ActiveWorkbook.Sheets(Array("Access", "Cranes"))
  With ws
  ws.Select
  Range("O2:O" & lastrow).Formula = FX2

 

[Emeng]

Hi Hui

Thanks for your replies... I was following up on your first and tried ws.Activate, which also does the trick
I copied this code from other macros I use and expected it would work... I had better go back & check!!
Thanks for your help.

Regards Mark

 

[Hui]

You can also use

ws.Range("O2:O" & lastrow).Formula = FX2

etc

 

[Emeng]

Thanks for the tip Hui,
My other code seems to work OK... it's a bit bamboozling when something you have relied on suddenly doesn't work. Thanks for the help.
Regards Mark