Option Explicit
Sub ReconcileStockCard()

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
Select Case LCase(wks.Name)
'names of the sheets to skip
Case Is = "sheet9", "sheet13", "sheet33"
'do nothing
Case Else
With wks
.Range("D3").FormulaR1C1 = "=SUM(R[4]C[-2]:R[97]C[-2])"
.Range("D3").Copy
.Range("B7").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("a7:a36,b8:b36,d3,d7:d36").ClearContents
End With
End Select
Next wks
End Sub

As an aside, this portion:

.Range("D3").Copy
.Range("B7").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

could be replaced with:

.range("B7").value = .Range("D3").Value



terilad wrote:

Hi I have a workbook with 103 sheets, I want a macro to run on 100 of the
sheets.

Here is the macro, can anyone help me with this?

Sub ReconsileStockCard()
Range("D3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[4]C[-2]:R[97]C[-2])"
Range("D3").Copy
Range("B7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A7:A36,B8:B36").ClearContents
Range("D3").ClearContents
Range("D7").ClearContents
Range("D8").ClearContents
Range("D9").ClearContents
Range("D10").ClearContents
Range("D11").ClearContents
Range("D12").ClearContents
Range("D13").ClearContents
Range("D14").ClearContents
Range("D15").ClearContents
Range("D16").ClearContents
Range("D17").ClearContents
Range("D18").ClearContents
Range("D19").ClearContents
Range("D20").ClearContents
Range("D21").ClearContents
Range("D22").ClearContents
Range("D23").ClearContents
Range("D24").ClearContents
Range("D25").ClearContents
Range("D26").ClearContents
Range("D27").ClearContents
Range("D28").ClearContents
Range("D29").ClearContents
Range("D30").ClearContents
Range("D31").ClearContents
Range("D32").ClearContents
Range("D33").ClearContents
Range("D34").ClearContents
Range("D35").ClearContents
Range("D36").ClearContents
Range("A1:D2").Select
End Sub

Many thanks

--

Dave Peterson