subtraction between two columns

Hasson

You declare sh as a Worksheet prior to the loop start (in bold):

Sub test1()
  Dim sh As Worksheet
  For Each sh In Worksheets

so the line:

With Sheets(sh).Cells(1).CurrentRegion

should read:

With sh.Cells(1).CurrentRegion

and likewise further down.

Hope this helps (or Willie can) since I don't have much spare time at present

Hello Hasson and welcome,

The reason for the "Type Mismatch" error is you are trying to reference/access a worksheet - sh - which doesn't exist (plus you are missing the quotes " "). As per your included file you need to change this to "as" (with the quotes, not - as) Also the last Next before End Sub is extra and not required - delete it. The line: .Columns("E:F").Delete deletes the entire columns E & F. I assume you only intend to delete the contents below the headers. To do so I added 2 variables: Dim LRow1 As Long, LRow2 As Long. I changed

With Sheets("sh").Cells(1).CurrentRegion
  .Columns("E:F").Delete
  End With

to

With Sheets("as")
        LRow1 = Range("E2").End(xlDown).Row
        LRow2 = Range("F2").End(xlDown).Row
        Range("E2:E" & LRow1, "F2:F" & LRow2).ClearContents
    End With

Here is the complete code;

Sub test1()
  Dim sh As Worksheet, x As Variant

  Dim LRow1 As Long, LRow2 As Long

  For Each sh In Worksheets
    With Sheets("as").Cells(1).CurrentRegion
        x = Application.Match("net", .Rows(1), 0)
        If IsError(x) Then
            .Columns(.Columns.Count).Copy .Columns(.Columns.Count - 1)
            With .Columns(.Columns.Count + 1)
                .Cells(1) = "NET"
                With .Offset(1).Resize(.Rows.Count + 1)
                    .FormulaR1C1 = "=rc[-2]-rc[-1]"
                    .Value = .Value
                End With
            End With
        End If
    End With
  Next
   With Sheets("as")
        LRow1 = Range("E2").End(xlDown).Row
        LRow2 = Range("F2").End(xlDown).Row
        Range("E2:E" & LRow1, "F2:F" & LRow2).ClearContents
    End With

End Sub

Cheers   :-)