Macro to run on selected sheets

[terilad]

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

 

[ozgrid.com]

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

 

[Mike H]

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]

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

 

[Stefi]

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:

 

[terilad]

They are the last 100

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:

 

[terilad]

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

Mark

 

[Stefi]

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:

 

[terilad]

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

 

[terilad]

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]

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:

 

[terilad]

Hi Stefi,

I am running the macro on a smaller workbook for testing before placing into
the proper one as I dont want data deleted that I have on the full workbook,
so I am trying this on 3 sheets on a test workbook.

Cells that are merged are A11:F11, but these are merged on worksheets I dont
want the macro to run on.

The error line is: Range("A7:A36,B8:B36").ClearContents

Many thanks for your help.

Mark

 

[Stefi]

For testing:
If your tabs are in this order:
Sheet1 Sheet2 Sheet3 Sheet4

and merged cells are on Sheet1 then

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

Loop For wi = 2 To 4
shall work on
Sheet2 Sheet3 Sheet4

The index number of a sheet (in the above example wi) is specified by its
position in the tab series and not by the number in sheet name: if your tab
series is, e.g.

Master Detail1 Detail2 Detail3

then
Worksheets(1).name: Master
Worksheets(2).name: Detail1
Worksheets(3).name: Detail2
Worksheets(4).name: Detail3

Loop For wi = 2 To 4
shall work on
Detail1 Detail2 Detail3

--
Regards!
Stefi



„terilad†ezt írta:

 

[terilad]

Many thanks, I have noticed that all my sheet numbers are all mixed up, I had
to rename 10 of them in my test, is there a way to do this quick for 103
sheets.

Many thanks


Mark

 

[Dave Peterson]

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.

 

[Stefi]

Once again:

Name of the worksheet is neutral in this case, its position number in the
series of tabs which is important.

if your series of tabs is, e.g.

Abrakadabra, Sheet1, Sheet2,

then worksheet(1) shall be Abrakadabra,
worksheet(2) shall be Sheet1,
worksheet(3) shall be Sheet2.

But if you change the order of tabs, e.g. like below

Sheet1, Sheet2, Abrakadabra

then worksheet(1) shall be Sheet1,
worksheet(2) shall be Sheet2,
worksheet(3) shall be Abrakadabra.

You need not to change any sheet name, you only have to group the 3 sheets
you dont't want to run the macro on in the first 3 position of your series of
tabs, e.g. if names of these 3 sheets are Merged1, Merged2, Merged3, then
your series of tabs must look like this:

Merged1, Merged2, Merged3, Unmerged1, Unmerged2, ... , Unmerged100

--
Regards!
Stefi



„terilad†ezt írta:

 

[terilad]

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

 

[terilad]

Stefi,

I think through time the tabs have been moved around, thus the first 3 sheet
tabs which i wish to exclude are named but have the default Sheet4, Sheet7
and Sheet101 in the sheet number in VBA, I renamed these and the code worked,
can I rename all sheets quickly with vba or is this not possible?

Mark

 

[Stefi]

If your tab order is
Sheet4, Sheet7, Sheet101, other sheets
then this sub shall work only on sheets Sheet4, Sheet7, Sheet101 and you
don't need to change any sheet names.
Sub test()
For wi = 4 To 103
Worksheets(wi).Select
Call ClearStockCards
Next wi
End Sub

But if you insist on changing a lot of sheet names via a macro, you can do
that if you can specify the old names and the new names assigned to them,
either by a rule or a lookup array or range.

E.g. if old names are in range("A2:A5") and new names in range("B2:B5")
respectively then this sub renames sheets the name of which is found in
range("A2:A5") :


Sub rentest()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
wsnamerow = ""
On Error Resume Next
wsnamerow = Range("A1:A5").Find(ws.Name, Range("A1"), xlValues).Row
On Error GoTo 0
If wsnamerow <> "" Then ws.Name = Range("B" & wsnamerow)
Next ws
End Sub

--
Regards!
Stefi



„terilad†ezt írta: