Looping Macro

[Stringt]

I have a simple macro (cut, copy, paste etc) that I want to run on every sheet of my workbook, without having to go to each sheet and run it each time.

How do I get a macro to run it through every sheet?
This message was edited by Stringt on 2002-05-02 02:48

 

[Tom Urtis]

Does this help? Insert this in your macro:

Dim sht As Worksheet
For Each sht In ThisWorkbook.Sheets
'Your copy paste code
Next sht

 

[gareth]

Hi Stringt.

Why dont you just copy the macro into your personal.xls folder and have excel open it everytime you open a new workbook.If you dont know where to place this email me at garethl@ij.co.za

 

[Stringt]

This code does not seem to work. It just does the current sheet you are in then stops... any ideas?

 

[Tom Urtis]

Try a couple other options, depending maybe on what you are trying to do:

''''''''''''''''
for x = 1 to sheets.count
sheets(x).Select
'Your code
next x
''''''''''''''''

Or this, posted on this board sometime ago:

ShCount = Application.Sheets.Count
Sheets(1).Select
For i = 1 To ShCount
'Your code
If i = ShCount Then
End
ActiveSheet.Next.Select
End If
Next i


Any help?

 

[Robb__]

Stringt

You haven't posted your code but, if it refers to any the first sheet or any range name on that sheet, the code will not loop successfully.

If it does refer to, say, Sheet1 as the object, change it to sh to pick up the current sheet instead.

e.g. instead of Worksheets("Sheet1").Range("a1").copy

use

sh.Range("a1").copy

Not very good code, but I hope you'll get what I mean.

Any help?

Regards

Robb__

 

[Stringt]

it doesn't seem to recognise the sheets - do i need to replace x's with my sheet names or what?