Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Run macro on selected sheets
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 141
Default Run macro on selected sheets
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Run macro on selected sheets
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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,565
Default Run macro on selected sheets
You can use an If ... Then statement to eliminate the three sheets you do
not want to run the macro on. The For ... Next statement will test each
sheet in the workbook.

Sub ReconsileStockCard()
For Each sh In ThisWorkbook.Worksheets
If sh.Name < "Sheet1" And sh.Name < "Sheet2" And _
sh.Name < "Sheet3" Then '<<<Sub actual sheet names
'Your code here
End If
Next
End Sub



"terilad" wrote in message
...
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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 71
Default Run macro on selected sheets
Try this:

Sub RunManyTimes()
Dim wS As Worksheet
For Each wS In ActiveWorkbook.Worksheets
If wS.Name = "sheet1" Or _
wS.Name = "some name" Or _
wS.Name = "some other name" Then
'do nothing
Else
wS.Select
ReconsileStockCard
End If
Next
End Sub

You can also clean up your code a bit by using

Range("D7:D36").ClearContents

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"terilad" wrote in message
...
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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,501
Default Run macro on selected sheets
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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,501
Default Run macro on selected sheets
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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 141
Default Run macro on selected sheets
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
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,501
Default Run macro on selected sheets
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
Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 141
Default Run macro on selected sheets
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
Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 141
Default Run macro on selected sheets
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
.

Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Run macro on selected sheets
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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to run on selected sheets terilad Excel Discussion (Misc queries) 18 April 7th 10 03:14 PM
Need some help with a macro for printing selected sheets Constance Excel Programming 1 September 17th 09 06:52 PM
How to run macro on selected sheets with one go? Harshad[_2_] Excel Discussion (Misc queries) 10 October 21st 08 10:35 AM
Protecting Macro and selected sheets ST Excel Discussion (Misc queries) 1 June 6th 06 03:03 PM
run macro on selected sheets only AmyTaylor[_29_] Excel Programming 4 September 21st 05 10:20 AM


All times are GMT +1. The time now is 02:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement