Code Runs Twice

[Emeng]

Sub ShowCapacity()

    Dim ws As Worksheet
    Dim lastrow As Long
    Dim FX3 As String
   
    FX3 = "=IF(ISNUMBER(SEARCH(""Total"",$B2)),C2,"""")"
   
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
   
    For Each ws In ActiveWorkbook.Sheets(Array("Ropes", "Erect", "Strip", "Insul.", "Grit", "NDT", "PVI"))
   
    ws.Activate
    lastrow = ws.Range("I" & Rows.Count).End(xlUp).Row
   
            Columns(10).Insert
            ws.Range("J2:J" & lastrow).Formula = FX3
            Columns("J:J").FormatConditions.Delete
            ws.Range("J2:J" & lastrow).Font.Bold = True
            ws.Range("J2:J" & lastrow).NumberFormat = "#,##0"
            ws.Range("J1") = "Capacity"
            Columns("J:J").Copy
            ws.Range("J1").PasteSpecial Paste:=xlPasteValues
            ws.Range("J1").PasteSpecial Paste:=xlPasteFormats
                     
    Next ws
   
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Hi all

I have a Sub which seems to run twice.
When calling it in a call sequence it inserts Column J twice.
If I comment the call out and run it via F5 it runs only once.

Is there something in my code that I am overlooking?

Any help is much appreciated,

Thanks & regards

Mark

Sub Index_Match2()
Call IM_Ropes2
Call IM_Erect2
Call IM_Strip2
Call IM_Insul2
Call IM_Grit2
Call IM_NDT2
Call IM_PVI2
Call FormatSubtotalColumns_Array
'Call ShowCapacity
End Sub

 

[Hui]

The ShowCapacity sub only inserts 1 Column at position J when ran with F5 or F8

The file doesn't include and of the other routines:
Call IM_Ropes2
Call IM_Erect2
Call IM_Strip2
Call IM_Insul2
Call IM_Grit2
Call IM_NDT2
Call IM_PVI2

But if they insert a Column J that would be the issue

 

[Smallman]

Hi Emeng

It only runs once when I run it. Few suggestions. You don't need to select anything. No need for a variable for your formula when you are using it once. Calcs don't need to go off for such a quick procedure and you might want to simplify your code. I could not see the full procedure, see below.

Sub ShowCapacity()
    Dim ws As Worksheet
    Dim lr As Long
 
    Application.ScreenUpdating = 0
   
    For Each ws In Sheets(Array("Ropes", "Erect", "Strip", "Insul.", "Grit", "NDT", "PVI"))
        lr = ws.Range("I" & Rows.Count).End(xlUp).Row
        ws.Columns(10).Insert
        ws.Range("J2:J" & lr).Formula = "=IF(ISNUMBER(SEARCH(""Total"",$B2)),C2,"""")"
        ws.Columns("J:J").FormatConditions.Delete
        ws.Range("J2:J" & lr).Font.Bold = True
        ws.Range("J2:J" & lr).NumberFormat = "#,##0"
        ws.Range("J1") = "Capacity"
        ws.Range("J2:J" & lr) = ws.Range("J2:J" & lr).Value
    Next ws
    Application.ScreenUpdating = 1
End Sub

I would suggest if you post the whole procedure we may be able to help.

Take care

Smallman