Run macro on selected sheets
in [Programming]
|
Prev: COM add-ins
Next: IF for inverting true/false
From: Mike H on 5 Apr 2010 11:08 oops, I need to qualify the ranges Sub ReconsileStockCard() Dim Ws As Worksheet S = "Sheet1,Sheet2,Sheet3" V = Split(S, ",") For Each Ws In ThisWorkbook.Worksheets If IsError(Application.Match(Ws.Name, V, 0)) Then Ws.Range("D3").FormulaR1C1 = "=SUM(R[4]C[-2]:R[97]C[-2])" Ws.Range("D3").Copy Ws.Range("B7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Ws.Range("A7:A36,B8:B36").ClearContents Ws.Range("D3").ClearContents Ws.Range("D7:D36").ClearContents ' Range("A1:D2").Select End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: > Hi, > > Try this. I tided your code up a bit. Note this line > > S = "Sheet1,Sheet2,Sheet3" > > Are the 3 sheets you DON'T want the code to run on so change to suit > > > Sub ReconsileStockCard() > Dim Ws As Worksheet > S = "Sheet1,Sheet2,Sheet3" > V = Split(S, ",") > For Each Ws In ThisWorkbook.Worksheets > If IsError(Application.Match(Ws.Name, V, 0)) Then > 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 > Application.CutCopyMode = False > Range("A7:A36,B8:B36").ClearContents > Range("D3").ClearContents > Range("D7:D36").ClearContents > End If > Next > End Sub > > -- > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "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
From: terilad on 5 Apr 2010 11:29 Hi Mike, This doesn't seem to work for me, it is running the code on the worksheet I have a link to run the macro even although I state the sheet not to run the macro, any ideas, also I have a new code, I input the wrong one the last time. New Code Sub ReconsileStockCard() 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 "Mike H" wrote: > Hi, > > Try this. I tided your code up a bit. Note this line > > S = "Sheet1,Sheet2,Sheet3" > > Are the 3 sheets you DON'T want the code to run on so change to suit > > > Sub ReconsileStockCard() > Dim Ws As Worksheet > S = "Sheet1,Sheet2,Sheet3" > V = Split(S, ",") > For Each Ws In ThisWorkbook.Worksheets > If IsError(Application.Match(Ws.Name, V, 0)) Then > 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 > Application.CutCopyMode = False > Range("A7:A36,B8:B36").ClearContents > Range("D3").ClearContents > Range("D7:D36").ClearContents > End If > Next > End Sub > > -- > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "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
From: Mike H on 5 Apr 2010 11:36 While the revised version of my code will do what you want dave peterson has a better solution, use that -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "terilad" wrote: > Hi Mike, > > This doesn't seem to work for me, it is running the code on the worksheet I > have a link to run the macro even although I state the sheet not to run the > macro, any ideas, also I have a new code, I input the wrong one the last time. > > New Code > > Sub ReconsileStockCard() > 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 > > "Mike H" wrote: > > > Hi, > > > > Try this. I tided your code up a bit. Note this line > > > > S = "Sheet1,Sheet2,Sheet3" > > > > Are the 3 sheets you DON'T want the code to run on so change to suit > > > > > > Sub ReconsileStockCard() > > Dim Ws As Worksheet > > S = "Sheet1,Sheet2,Sheet3" > > V = Split(S, ",") > > For Each Ws In ThisWorkbook.Worksheets > > If IsError(Application.Match(Ws.Name, V, 0)) Then > > 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 > > Application.CutCopyMode = False > > Range("A7:A36,B8:B36").ClearContents > > Range("D3").ClearContents > > Range("D7:D36").ClearContents > > End If > > Next > > End Sub > > > > -- > > Mike > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > introduces the fewest assumptions while still sufficiently answering the > > question. > > > > > > "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
From: terilad on 5 Apr 2010 11:52 Hi Mike, None of them working maybe because I changed my original code. What do you think, my new code. 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 Many thanks Mark "Mike H" wrote: > While the revised version of my code will do what you want dave peterson has > a better solution, use that > -- > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "terilad" wrote: > > > Hi Mike, > > > > This doesn't seem to work for me, it is running the code on the worksheet I > > have a link to run the macro even although I state the sheet not to run the > > macro, any ideas, also I have a new code, I input the wrong one the last time. > > > > New Code > > > > Sub ReconsileStockCard() > > 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 > > > > "Mike H" wrote: > > > > > Hi, > > > > > > Try this. I tided your code up a bit. Note this line > > > > > > S = "Sheet1,Sheet2,Sheet3" > > > > > > Are the 3 sheets you DON'T want the code to run on so change to suit > > > > > > > > > Sub ReconsileStockCard() > > > Dim Ws As Worksheet > > > S = "Sheet1,Sheet2,Sheet3" > > > V = Split(S, ",") > > > For Each Ws In ThisWorkbook.Worksheets > > > If IsError(Application.Match(Ws.Name, V, 0)) Then > > > 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 > > > Application.CutCopyMode = False > > > Range("A7:A36,B8:B36").ClearContents > > > Range("D3").ClearContents > > > Range("D7:D36").ClearContents > > > End If > > > Next > > > End Sub > > > > > > -- > > > Mike > > > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > > introduces the fewest assumptions while still sufficiently answering the > > > question. > > > > > > > > > "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
From: terilad on 5 Apr 2010 12:02
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 > . > |