macro for all open worksheets?
 

i have 1500 worksheets all the same format
i have recorded a macro for workbook 1
i want to run the macro the other 1499 sheets

is there a quick way to run the macro without pressing run macro key
and select the macro and..............., (like grouping worksheets) ?


--
barkiny
------------------------------------------------------------------------
barkiny's Profile: http://www.excelforum.com/member.php...o&userid=20397
View this thread: http://www.excelforum.com/showthread...hreadid=490136

macro for all open worksheets?
 

Something like the following will do what you want. I named your macro
"YourMacro". The code can be written to run faster but that would involve
making changes in your macro and you didn't post your macro. Note that this
code will run your macro on every sheet in your workbook. No exceptions.
You would have to add some exclusionary code if you want it to skip some of
the sheets. HTH Otto



Sub NewMacroName()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

ws.Select
Call YourMacro
Next ws
End Sub
"barkiny" wrote in
message ...

i have 1500 worksheets all the same format
i have recorded a macro for workbook 1
i want to run the macro the other 1499 sheets

is there a quick way to run the macro without pressing run macro key
and select the macro and..............., (like grouping worksheets) ?


--
barkiny
------------------------------------------------------------------------
barkiny's Profile:
http://www.excelforum.com/member.php...o&userid=20397
View this thread: http://www.excelforum.com/showthread...hreadid=490136

macro for all open worksheets?
 

the worksheets is not in the same excel file..


i have 1500 excel file

like
01-01-2001.xls and 01-02-2001.xls

all these excel files are the same format

(the thing is i am trying to form a pivot table in another file called
datasheet.xls)

the macro simply copies the data in 01-01-2001.xls and paste the data
in datasheet.xls and offsets 1 row then copies the data in
01-02-2001.xls then paste to datasheet.xls and so on ...

is it possible


--
barkiny
------------------------------------------------------------------------
barkiny's Profile: http://www.excelforum.com/member.php...o&userid=20397
View this thread: http://www.excelforum.com/showthread...hreadid=490136

macro for all open worksheets?
 

Well, that certainly changes things. Try something like the following.
Note that your macro will operate on the active workbook (file) in turn.
Also note that this macro will run through every file in the folder
specified by "MyPath". No exceptions. You would have to add some
exclusionary code if you want it to skip some of the workbooks. HTH Otto
Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
ChDir MyPath
TheFile = Dir("*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
wb.Activate
Call YourMacro
wb.Save
wb.Saved = True
wb.Close
TheFile = Dir
Loop
End Sub
"barkiny" wrote in
message ...

the worksheets is not in the same excel file..


i have 1500 excel file

like
01-01-2001.xls and 01-02-2001.xls

all these excel files are the same format

(the thing is i am trying to form a pivot table in another file called
datasheet.xls)

the macro simply copies the data in 01-01-2001.xls and paste the data
in datasheet.xls and offsets 1 row then copies the data in
01-02-2001.xls then paste to datasheet.xls and so on ...

is it possible


--
barkiny
------------------------------------------------------------------------
barkiny's Profile:
http://www.excelforum.com/member.php...o&userid=20397
View this thread: http://www.excelforum.com/showthread...hreadid=490136