Macro

Marika

New Member
Joined
Jul 1, 2004
Messages
38
I have a macro that's running on 1 worksheet, but would like to include certain other worksheets as well (Other worksheets has same data only diff. values)
TIA
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Maybe I should be more clear on the sub.
I browsed the help file and only found;
With Application.WorksheetFunction
For Each Sht In Workbook
Is there another way to specify only certain worksheets and not all of them?
 
Upvote 0
Hi, you can loop through the sheets collection and look for particular sheet names or you can put the specific sheets you want into an array & loop through the array. Heres an example of the array option where Im wanting to change the value of cell A5 to 9 in sheets Sheet1 and Sheet3.

Code:
Sub PickSheets()
Dim ShArr(), Sh
'Create an array of the sheets you want
ShArr = Array(Sheets("Sheet1"), Sheets("Sheet3"))
' Use a ForEach statement to loop though the sheets in the array
For Each Sh In ShArr
'Change value in A5 to 9. Note the use of "Sh." to represent the current sheet in the array
Sh.Range("A5") = 9
Next Sh

End Sub
 
Upvote 0
hitchhiking again!

i have tried to adapt Parry's code above to delete certain words on two sheets but get a compile error: "Can't assign to array". It comes up when the code hits " the third line : "ShArr = ...."

How do i fix this?

Sub DeleteWords()
'delete "Total", "Promo" & "Group" from Col A
Dim ShArr(), Sh
'Create an array of the sheets you want
ShArr = Array(Sheets("Coles"), Sheets("Woolworths"))
' Use a ForEach statement to loop though the sheets in the array
For Each Sh In ShArr
With Range("A:a")
.Replace What:="Total ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace What:=" promo group", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Next Sh
End With

End Sub
 
Upvote 0
Hi, I havent tried the code but try changing the line With Range("A:a") to With Sh.Range("A:a")

The reason for this is that a range that does not have a sheet name included in the line is assumed to be the active sheet. This will probably not be the case as both of these sheets cant be active. :)

Similarly, Excel assumes the workbook is the Activeworkbook as well so if you are dealing with lots of books open at once then use the Workbooks object as well.

Note that the Sh in the Dim statement is just a variable name (you could have called this MySheets for example), so when you get to your For Each use the variable name to describe the range object as this represents the particluar sheet its up to in the loop.
 
Upvote 0
parry,

just tried your code as it is written in a new workbook. it returns the same error message.

??
 
Upvote 0
Hi All

ajm, try this:
Code:
Sub DeleteWords()
Dim ShArr(), Sh
ShArr = Array(Sheets("Blad1"), Sheets("Blad3"))
For Each Sh In ShArr
    With Range("A1:A10")
        .Replace What:="Total ", Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
        .Replace What:="promo group", Replacement:="Replaced!", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    End With
Next Sh
End Sub

/Roger

Edit:

I changed this part in my test:
Code:
 Replacement:="Replaced!"

Don't forget to change it back...
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top