run macro on all closed workbooks in folder and subfolders
 

I would like to be able to run the following macro on a large group of
workbooks without having to open them individually. Any help would be
appreciated. Thanks.

spence

Sub COLAcontracts()
'
' COLAcontracts Macro
' Macro recorded 5/1/2007 by inclusion
'

'
Sheets("IFIS").Select
ActiveSheet.Unprotect Password:="santaclaus"
Rows("7:7").Select
Selection.Delete Shift:=xlUp
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("B1:B5").Select
Selection.Copy
Range("K5").Select
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("C1:K5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
Application.CutCopyMode = False
ActiveSheet.Protect Password:="santaclaus", DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

run macro on all closed workbooks in folder and subfolders
 

Hi spence

You must open them

You can look at this example that copy a range to each workbook
http://www.rondebruin.nl/copy4.htm
You can replace the copy code with your code


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"spence" wrote in message ...
I would like to be able to run the following macro on a large group of
workbooks without having to open them individually. Any help would be
appreciated. Thanks.

spence

Sub COLAcontracts()
'
' COLAcontracts Macro
' Macro recorded 5/1/2007 by inclusion
'

'
Sheets("IFIS").Select
ActiveSheet.Unprotect Password:="santaclaus"
Rows("7:7").Select
Selection.Delete Shift:=xlUp
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("B1:B5").Select
Selection.Copy
Range("K5").Select
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("C1:K5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
Application.CutCopyMode = False
ActiveSheet.Protect Password:="santaclaus", DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

run macro on all closed workbooks in folder and subfolders
 

Ron,

Thanks. I can live with that. Your website is very informative by the way. I
have a third party add-on that compiles ranges from closed workbooks but it's
nice to see how to build some of this stuff myself. Your site is a resource I
think I'll find myself going back to many times. I appreciate it.

spence

"Ron de Bruin" wrote:

Hi spence

You must open them

You can look at this example that copy a range to each workbook
http://www.rondebruin.nl/copy4.htm
You can replace the copy code with your code


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"spence" wrote in message ...
I would like to be able to run the following macro on a large group of
workbooks without having to open them individually. Any help would be
appreciated. Thanks.

spence

Sub COLAcontracts()
'
' COLAcontracts Macro
' Macro recorded 5/1/2007 by inclusion
'

'
Sheets("IFIS").Select
ActiveSheet.Unprotect Password:="santaclaus"
Rows("7:7").Select
Selection.Delete Shift:=xlUp
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("B1:B5").Select
Selection.Copy
Range("K5").Select
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("C1:K5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
Application.CutCopyMode = False
ActiveSheet.Protect Password:="santaclaus", DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

run macro on all closed workbooks in folder and subfolders
 

Hi spence

I changing all pages on this moment on my site and update the code so that it is also working in 2007.

have a third party add-on that compiles ranges from closed workbooks
I have upload a new add-in to merge
http://www.rondebruin.nl/merge.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"spence" wrote in message ...
Ron,

Thanks. I can live with that. Your website is very informative by the way. I
have a third party add-on that compiles ranges from closed workbooks but it's
nice to see how to build some of this stuff myself. Your site is a resource I
think I'll find myself going back to many times. I appreciate it.

spence

"Ron de Bruin" wrote:

Hi spence

You must open them

You can look at this example that copy a range to each workbook
http://www.rondebruin.nl/copy4.htm
You can replace the copy code with your code


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"spence" wrote in message ...
I would like to be able to run the following macro on a large group of
workbooks without having to open them individually. Any help would be
appreciated. Thanks.

spence

Sub COLAcontracts()
'
' COLAcontracts Macro
' Macro recorded 5/1/2007 by inclusion
'

'
Sheets("IFIS").Select
ActiveSheet.Unprotect Password:="santaclaus"
Rows("7:7").Select
Selection.Delete Shift:=xlUp
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("B1:B5").Select
Selection.Copy
Range("K5").Select
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("C1:K5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
Application.CutCopyMode = False
ActiveSheet.Protect Password:="santaclaus", DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveWorkbook.Save
ActiveWindow.Close
End Sub