I have a macro that I want to run on 100 of my 103 worksheets, is there a way
this can be done.

My code is:

Sub ClearStockCards()
Range("A7:A36,B8:B36").ClearContents
Range("D3").ClearContents
End Sub

Many thanks

Mark

Try;

Sub ClearStockCards()
Dim ws As Worksheet

For Each ws In Worksheets
Select Case UCase(ws.Name)
Case "SHEET101", "SHEET102", "SHEET103"
'Do nothing
Case Else
ws.Range("A7:A36,B8:B36,D3").ClearContents
End Select
Next ws
End Sub



--
Regards
Dave Hawley
www.ozgrid.com
"terilad" wrote in message
...
I have a macro that I want to run on 100 of my 103 worksheets, is there a
way
this can be done.

My code is:

Sub ClearStockCards()
Range("A7:A36,B8:B36").ClearContents
Range("D3").ClearContents
End Sub

Many thanks

Mark

Hi,

I cant get this code to work it has error run time error cant change part of
a merged cell, the merged cells are on the sheets I dont want to run the
macro, when I unmerge the cells it runs the macro on the sheets that I dont
want it to run on.

Any ideas?

Many thanks

Mark

"ozgrid.com" wrote:

Try;

Sub ClearStockCards()
Dim ws As Worksheet

For Each ws In Worksheets
Select Case UCase(ws.Name)
Case "SHEET101", "SHEET102", "SHEET103"
'Do nothing
Case Else
ws.Range("A7:A36,B8:B36,D3").ClearContents
End Select
Next ws
End Sub



--
Regards
Dave Hawley
www.ozgrid.com
"terilad" wrote in message
...
I have a macro that I want to run on 100 of my 103 worksheets, is there a
way
this can be done.

My code is:

Sub ClearStockCards()
Range("A7:A36,B8:B36").ClearContents
Range("D3").ClearContents
End Sub

Many thanks

Mark

Hi,

Try this.

Change this line
S = "Sheet1,Sheet2,Sheet3"
To the name of the sheets you DON'T want the code to run on

Sub ClearStockCards()
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,A7:A36,B8:B36").ClearContents
End If
Next Ws
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:

I have a macro that I want to run on 100 of my 103 worksheets, is there a way
this can be done.

My code is:

Sub ClearStockCards()
Range("A7:A36,B8:B36").ClearContents
Range("D3").ClearContents
End Sub

Many thanks

Mark

If the 100 worksheets are the first 100 ones in the order of the worksheet
tabs, then

Sub test()
For wi = 1 To 100
Worksheets(wi).Select
Call ClearStockCards
Next wi
End Sub


--
Regards!
Stefi



€žterilad€� ezt Ã*rta:

I have a macro that I want to run on 100 of my 103 worksheets, is there a way
this can be done.

My code is:

Sub ClearStockCards()
Range("A7:A36,B8:B36").ClearContents
Range("D3").ClearContents
End Sub

Many thanks

Mark

They are the last 100

Mark

"Stefi" wrote:

If the 100 worksheets are the first 100 ones in the order of the worksheet
tabs, then

Sub test()
For wi = 1 To 100
Worksheets(wi).Select
Call ClearStockCards
Next wi
End Sub


--
Regards!
Stefi



€žterilad€� ezt Ã*rta:

I have a macro that I want to run on 100 of my 103 worksheets, is there a way
this can be done.

My code is:

Sub ClearStockCards()
Range("A7:A36,B8:B36").ClearContents
Range("D3").ClearContents
End Sub

Many thanks

Mark

tried editing but not working for me, trying to run macro on sheets I dont
want it to.

Mark

"Stefi" wrote:

If the 100 worksheets are the first 100 ones in the order of the worksheet
tabs, then

Sub test()
For wi = 1 To 100
Worksheets(wi).Select
Call ClearStockCards
Next wi
End Sub


--
Regards!
Stefi



€žterilad€� ezt Ã*rta:

I have a macro that I want to run on 100 of my 103 worksheets, is there a way
this can be done.

My code is:

Sub ClearStockCards()
Range("A7:A36,B8:B36").ClearContents
Range("D3").ClearContents
End Sub

Many thanks

Mark

If your code doesn't work, please post it next time!

For the last 100 sheets:

Sub test()
For wi = 4 To 103
Worksheets(wi).Select
Call ClearStockCards
Next wi
End Sub


--
Regards!
Stefi



€žterilad€� ezt Ã*rta:

tried editing but not working for me, trying to run macro on sheets I dont
want it to.

Mark

"Stefi" wrote:

If the 100 worksheets are the first 100 ones in the order of the worksheet
tabs, then

Sub test()
For wi = 1 To 100
Worksheets(wi).Select
Call ClearStockCards
Next wi
End Sub


--
Regards!
Stefi



€žterilad€� ezt Ã*rta:

I have a macro that I want to run on 100 of my 103 worksheets, is there a way
this can be done.

My code is:

Sub ClearStockCards()
Range("A7:A36,B8:B36").ClearContents
Range("D3").ClearContents
End Sub

Many thanks

Mark

Hi Stefi,

No Joy.

Here's my code.

Sub MyMacroToClear()
For wi = 5 To 7
Worksheets(wi).Select
Call ClearStockCards
Next wi
End Sub
Sub ClearStockCards()
Range("A7:A36,B8:B36").ClearContents
Range("D3").ClearContents
End Sub

Thanks

Mark

"Stefi" wrote:

If your code doesn't work, please post it next time!

For the last 100 sheets:

Sub test()
For wi = 4 To 103
Worksheets(wi).Select
Call ClearStockCards
Next wi
End Sub


--
Regards!
Stefi



€žterilad€� ezt Ã*rta:

tried editing but not working for me, trying to run macro on sheets I dont
want it to.

Mark

"Stefi" wrote:

If the 100 worksheets are the first 100 ones in the order of the worksheet
tabs, then

Sub test()
For wi = 1 To 100
Worksheets(wi).Select
Call ClearStockCards
Next wi
End Sub


--
Regards!
Stefi



€žterilad€� ezt Ã*rta:

I have a macro that I want to run on 100 of my 103 worksheets, is there a way
this can be done.

My code is:

Sub ClearStockCards()
Range("A7:A36,B8:B36").ClearContents
Range("D3").ClearContents
End Sub

Many thanks

Mark

I cant get this code to work it has error run time error cant change part of
a merged cell, the merged cells are on the sheets I dont want to run the
macro, when I unmerge the cells it runs the macro on the sheets that I dont
want it to run on.

Mark

"Stefi" wrote:

If your code doesn't work, please post it next time!

For the last 100 sheets:

Sub test()
For wi = 4 To 103
Worksheets(wi).Select
Call ClearStockCards
Next wi
End Sub


--
Regards!
Stefi



€žterilad€� ezt Ã*rta:

tried editing but not working for me, trying to run macro on sheets I dont
want it to.

Mark

"Stefi" wrote:

If the 100 worksheets are the first 100 ones in the order of the worksheet
tabs, then

Sub test()
For wi = 1 To 100
Worksheets(wi).Select
Call ClearStockCards
Next wi
End Sub


--
Regards!
Stefi



€žterilad€� ezt Ã*rta:

I have a macro that I want to run on 100 of my 103 worksheets, is there a way
this can be done.

My code is:

Sub ClearStockCards()
Range("A7:A36,B8:B36").ClearContents
Range("D3").ClearContents
End Sub

Many thanks

Mark

Which sheets do you want to run the macro on? You wrote that they are the
last 100 while your macro run on sheets 5,6,7 (3 sheets). Which is true?

Which cells are merged?

Which line causes the error? (Which line is highlighted in yellow while
debugging?

--
Regards!
Stefi



€žterilad€� ezt Ã*rta:

I cant get this code to work it has error run time error cant change part of
a merged cell, the merged cells are on the sheets I dont want to run the
macro, when I unmerge the cells it runs the macro on the sheets that I dont
want it to run on.

Mark

"Stefi" wrote:

If your code doesn't work, please post it next time!

For the last 100 sheets:

Sub test()
For wi = 4 To 103
Worksheets(wi).Select
Call ClearStockCards
Next wi
End Sub


--
Regards!
Stefi



€žterilad€� ezt Ã*rta:

tried editing but not working for me, trying to run macro on sheets I dont
want it to.

Mark

"Stefi" wrote:

If the 100 worksheets are the first 100 ones in the order of the worksheet
tabs, then

Sub test()
For wi = 1 To 100
Worksheets(wi).Select
Call ClearStockCards
Next wi
End Sub


--
Regards!
Stefi



€žterilad€� ezt Ã*rta:

I have a macro that I want to run on 100 of my 103 worksheets, is there a way
this can be done.

My code is:

Sub ClearStockCards()
Range("A7:A36,B8:B36").ClearContents
Range("D3").ClearContents
End Sub

Many thanks

Mark

First, it's never a good idea to multipost the same question to different
newsgroups. And it's not a good idea to start a new thread without some
indication in the old thread.

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("a7:a36,b8:b36,d3,d7:d36").value = ""
End With
End Select
Next wks
End Sub

I changed the range back to what you used in the other thread. Change it if you
need to.

terilad wrote:

I have a macro that I want to run on 100 of my 103 worksheets, is there a way
this can be done.

My code is:

Sub ClearStockCards()
Range("A7:A36,B8:B36").ClearContents
Range("D3").ClearContents
End Sub

Many thanks

Mark

--

Dave Peterson

I only reposted this as I posted the wrong code initialy and the thread was
getting realy confused and difficult to put the issue across, thanks for your
input Dave.

Regards

Mark

"Dave Peterson" wrote:

First, it's never a good idea to multipost the same question to different
newsgroups. And it's not a good idea to start a new thread without some
indication in the old thread.

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("a7:a36,b8:b36,d3,d7:d36").value = ""
End With
End Select
Next wks
End Sub

I changed the range back to what you used in the other thread. Change it if you
need to.

terilad wrote:

I have a macro that I want to run on 100 of my 103 worksheets, is there a way
this can be done.

My code is:

Sub ClearStockCards()
Range("A7:A36,B8:B36").ClearContents
Range("D3").ClearContents
End Sub

Many thanks

Mark

--

Dave Peterson
.