On Dec 8, 6:24*am, yogi wrote:
On Dec 8, 1:22*pm, Walter Briscoe wrote:





In message
s.com of Tue, 7 Dec 2010 23:17:11 in microsoft.public.excel.programming
, yogi writes

Dear all,
i have reorded macro for formating data , i have more than 150 files
in which i have to run this macro. Is there is any code by which i can
run my recorded macro in 150files at a time.

It might be nice if you said which version of Excel you use. The
solution might be different for different versions.

I assume running a macro in 150 files, one at a time is acceptable.
I assume a folder object will give you the names of files which include
those you want to run. help folder in Visual Basic Window will help.

I would construct pseudo code like:
set folder = root folder
call tree folder

sub tree(folder)
for each file in folder
* * if relevant file then Call Macro file
next file
for each subfolder in folder
* * if relevant subfolder then call tree subfolder
next subfolder
end sub

I run a given macro on up to about 10 files in one folder by selecting
the names in Windows Vista and opening the files in Excel 2003.
I then run code like this:

Dim wq As Workbook

call initialisation
For Each wq In Workbooks
* * SingleFileAction
Next wq

I am sorry not to be more helpful.
If you can expand your requirement, you may get more specific help.
Please say how you get on.
--
Walter Briscoe

Dear Walter Briscoe

My excel version is 2003.

I want to run below recorded macro in several worksheet in a folder at
a time . I will be very thank full to you, if you add vba language
for running macro in all worksheet around below vba code.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 26/07/2010
'
' Keyboard Shortcut: Ctrl+y
'
* * Sheets("Sheet1").Copy After:=Sheets(1)
* * Sheets("Sheet1 (2)").Name = "Policy Annexure"
* * Columns("A:D").Select
* * Selection.Delete Shift:=xlToLeft
* * Columns("B:B").Select
* * Selection.Delete Shift:=xlToLeft
* * Columns("C:D").Select
* * Selection.Delete Shift:=xlToLeft
* * Columns("E:E").Select
* * Selection.Delete Shift:=xlToLeft
* * Columns("I:AH").Select
* * Selection.Delete Shift:=xlToLeft
* * Range("H1").Select
* * Selection.End(xlToLeft).Select
* * Selection.End(xlToLeft).Select
* * Selection.End(xlToLeft).Select
* * Cells.Select
* * With Selection.Font
* * * * .Size = 8
* * * * .Strikethrough = False
* * * * .Superscript = False
* * * * .Subscript = False
* * * * .OutlineFont = False
* * * * .Shadow = False
* * * * .Underline = xlUnderlineStyleNone
* * End With
* * With Selection.Font
* * * * .Name = "Arial"
* * * * .Size = 8
* * * * .Strikethrough = False
* * * * .Superscript = False
* * * * .Subscript = False
* * * * .OutlineFont = False
* * * * .Shadow = False
* * * * .Underline = xlUnderlineStyleNone
* * End With
* * With Selection.Font
* * * * .Name = "Arial"
* * * * .Size = 9
* * * * .Strikethrough = False
* * * * .Superscript = False
* * * * .Subscript = False
* * * * .OutlineFont = False
* * * * .Shadow = False
* * * * .Underline = xlUnderlineStyleNone
* * End With
* * Cells.Select
* * With Selection.Font
* * * * .Name = "Arial"
* * * * .Size = 9
* * * * .Strikethrough = False
* * * * .Superscript = False
* * * * .Subscript = False
* * * * .OutlineFont = False
* * * * .Shadow = False
* * * * .Underline = xlUnderlineStyleNone
* * End With
* * With Selection.Font
* * * * .Name = "Arial"
* * * * .Size = 9
* * * * .Strikethrough = False
* * * * .Superscript = False
* * * * .Subscript = False
* * * * .OutlineFont = False
* * * * .Shadow = False
* * * * .Underline = xlUnderlineStyleNone
* * End With
* * With Selection.Font
* * * * .Name = "Arial"
* * * * .Size = 8
* * * * .Strikethrough = False
* * * * .Superscript = False
* * * * .Subscript = False
* * * * .OutlineFont = False
* * * * .Shadow = False
* * * * .Underline = xlUnderlineStyleNone
* * End With
* * Range("A1").Select
* * Columns("A:A").Select
* * Range("A2").Activate
* * Selection.RowHeight = 26.25
* * Range("H2").Select
* * Selection.End(xlDown).Select
* * Range("G71").Select
* * Selection.End(xlToLeft).Select
* * Selection.End(xlToLeft).Select
* * Rows("1:1").Select
* * Selection.Replace What:="_", Replacement:=" ", LookAt:=xlPart, _
* * * * SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
* * * * ReplaceFormat:=False
* * Columns("A:A").Select
* * With Selection
* * * * .HorizontalAlignment = xlCenter
* * * * .VerticalAlignment = xlBottom
* * * * .WrapText = False
* * * * .Orientation = 0
* * * * .AddIndent = False
* * * * .IndentLevel = 0
* * * * .ShrinkToFit = False
* * * * .ReadingOrder = xlContext
* * * * .MergeCells = False
* * End With
* * Range("B2").Select
* * Range(Selection, Selection.End(xlDown)).Select
* * Range(Selection, Selection.End(xlDown)).Select
* * With Selection
* * * * .HorizontalAlignment = xlGeneral
* * * * .VerticalAlignment = xlBottom
* * * * .WrapText = False
* * * * .Orientation = 0
* * * * .AddIndent = False
* * * * .IndentLevel = 0
* * * * .ShrinkToFit = False
* * * * .ReadingOrder = xlContext
* * * * .MergeCells = False
* * End With
* * Selection.End(xlUp).Select
* * Range("C2").Select
* * Range(Selection, Selection.End(xlDown)).Select
* * Columns("C:G").Select
* * Range("C2").Activate
* * With Selection
* * * * .HorizontalAlignment = xlCenter
* * * * .VerticalAlignment = xlBottom
* * * * .WrapText = False
* * * * .Orientation = 0
* * * * .AddIndent = False
* * * * .IndentLevel = 0
* * * * .ShrinkToFit = False
* * * * .ReadingOrder = xlContext
* * * * .MergeCells = False
* * End With
* * Selection.End(xlUp).Select
* * Range("H2").Select
* * Range(Selection, Selection.End(xlDown)).Select
* * Range(Selection, Selection.End(xlDown)).Select
* * Selection.NumberFormat = "#,##0"
* * With Selection
* * * * .HorizontalAlignment = xlRight
* * * * .VerticalAlignment = xlBottom
* * * * .WrapText = False
* * * * .Orientation = 0
* * * * .AddIndent = False
* * * * .ShrinkToFit = False
* * * * .ReadingOrder = xlContext
* * * * .MergeCells = False
* * End With
* * Range("A2").Select
* * Selection.End(xlDown).Select
* * Range("A71").Select
* * Selection.ColumnWidth = 8.71
* * Range("B71").Select
* * Selection.Columns.AutoFit
* * Range("B72").Select
* * Selection.Columns.AutoFit
* * Selection.ColumnWidth = 32.71
* * Range("C72").Select
* * Selection.ColumnWidth = 20
* * Range("D72").Select
* * Selection.ColumnWidth = 11.57
* * Range("E72").Select
* * Selection.ColumnWidth = 10.57
* * Range("F72").Select
* * Selection.ColumnWidth = 22.71
* * Range("G72").Select
* * Selection.ColumnWidth = 10.86
* * Range("H72").Select
* * Selection.ColumnWidth = 11.4
* * Range("A1").Select
* * Range(Selection, Selection.End(xlToRight)).Select
* * With Selection
* * * * .VerticalAlignment = xlBottom
* * * * .WrapText = True
* * * * .Orientation = 0
* * * * .AddIndent = False
* * * * .ShrinkToFit = False
* * * * .ReadingOrder = xlContext
* * * * .MergeCells = False
* * End With
* * Range("A1").Select
* * Rows("1:1").RowHeight = 32.25
* * ActiveCell.FormulaR1C1 = "Under Writter Off Cd"
* * With ActiveCell.Characters(Start:=1, Length:=20).Font
* * * * .Name = "Arial"
* * * * .FontStyle = "Bold"
* * * * .Size = 8
* * * * .Strikethrough = False
* * * * .Superscript = False
* * * * .Subscript = False
* * * * .OutlineFont = False
* * * * .Shadow = False
* * * * .Underline = xlUnderlineStyleNone
* * * * .ColorIndex = 1
* * End With
* * Range("B1").Select
* * ActiveCell.FormulaR1C1 = "GROUP NAME"
* * With ActiveCell.Characters(Start:=1, Length:=10).Font
* * * * .Name = "Arial"
* * * * .FontStyle = "Bold"
* * * * .Size = 8
* * * * .Strikethrough = False
* * * * .Superscript = False
* * * * .Subscript = False
* * * * .OutlineFont = False
* * * * .Shadow = False
* * * * .Underline = xlUnderlineStyleNone
* * * * .ColorIndex = 1
* * End With
* * Range("C1").Select
* * ActiveCell.FormulaR1C1 = "POLICY NO"
* * With ActiveCell.Characters(Start:=1, Length:=9).Font
* * * * .Name = "Arial"
* * * * .FontStyle = "Bold"
* * * * .Size = 8
* * * * .Strikethrough = False
* * * * .Superscript = False
* * * * .Subscript = False
* * * * .OutlineFont = False
* * * * .Shadow = False
* * * * .Underline = xlUnderlineStyleNone
* * * * .ColorIndex = 1
* * End With
* * Range("D1").Select
* * ActiveCell.FormulaR1C1 = "COMMENCEMENT DT"
* * With ActiveCell.Characters(Start:=1, Length:=15).Font
* * * * .Name = "Arial"
* * * * .FontStyle = "Bold"
* * * * .Size = 8
* * * * .Strikethrough = False
* * * * .Superscript = False
* * * * .Subscript = False
* * * * .OutlineFont = False
* * * * .Shadow = False
* * * * .Underline = xlUnderlineStyleNone
* * * * .ColorIndex = 1
* * End With
* * Range("E1").Select
* * ActiveCell.FormulaR1C1 = "VALID UPTO"
* * With ActiveCell.Characters(Start:=1, Length:=10).Font
* * * * .Name = "Arial"
* * * * .FontStyle = "Bold"
* * * * .Size = 8
* * * * .Strikethrough = False
* * * * .Superscript = False
* * * * .Subscript = False
* * * * .OutlineFont = False
* * * * .Shadow = False
* * * * .Underline = xlUnderlineStyleNone
* * * * .ColorIndex = 1
* * End With
* * Range("F1").Select
* * ActiveCell.FormulaR1C1 = "ENDORSEMENT NO"
* * With ActiveCell.Characters(Start:=1, Length:=14).Font
* * * * .Name = "Arial"
* * * * .FontStyle = "Bold"
* * * * .Size = 8
* * * * .Strikethrough = False
* * * * .Superscript = False
* * * * .Subscript = False
* * * * .OutlineFont = False
* * * * .Shadow = False
* * * * .Underline = xlUnderlineStyleNone
* * * * .ColorIndex = 1
* * End With
* * Range("G1").Select
* * ActiveCell.FormulaR1C1 = "ENDORSEMENT DT"
* * With ActiveCell.Characters(Start:=1, Length:=14).Font
* * * * .Name = "Arial"
* * * * .FontStyle = "Bold"
* * * * .Size = 8
* * * * .Strikethrough = False
* * * * .Superscript = False
* * * * .Subscript = False
* * * * .OutlineFont = False
* * * * .Shadow = False
* * * * .Underline = xlUnderlineStyleNone
* * * * .ColorIndex = 1
...

read more »- Hide quoted text -

- Show quoted text -

First, your macro is obviously only a recorded macro that should be
cleaned up to remove selections, etc. Look in the vba help index for
DIR function to see how to open each file and run the macro. If all
else fails, send your master and at least one source file with a
complete explanation. Send to