Run macro on selected sheets
in [Programming]
|
Prev: COM add-ins
Next: IF for inverting true/false
From: Dave Peterson on 5 Apr 2010 14:17 I don't see how that can loop through each of the sheets. Did you try that suggested code? terilad wrote: > > Hi Dave > > This doesn't work for me maybe because I changed my code, my new code is. > > Sub ClearStockCard() > 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 > > Regards > > Mark > > "Dave Peterson" wrote: > > > 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 > > . > > -- Dave Peterson |