For Each...Dynamically formatting multiple worksheets
 

I am creating multiple worksheets in a single workbook.
The workbook is being created by another application (Data
Junction). Each worksheet has the same columns, however
the number of worksheets (and their corrsponding sheet
names) will vary on a monthly basis.

I have been able to succssfully format a single worksheet,
but am having trouble applying the same
formatting/functions to multiple sheets in the same
workbook.

Example code is provided below...How might I apply the For
Each...Next syntax around this so that all worksheets are
formatted the same?

Thanks

*******

'Define the Excel Application Object
Dim xlApp As Object

'Create the Excel Object
Set xlApp = CreateObject("Excel.Application")

'Open the desired Excel File
xlApp.Application.Workbooks.Open "c:sample.xls"

'Insert Rowsfor Control Totals
xlApp.Application.Rows("1:3").Select
xlApp.Application.Selection.Insert Shift = "xlDown"

'Calculate Totals
xlApp.Application.Range("K2").Select
xlApp.Application.ActiveCell.FormulaR1C1 = "=SUM(R[3]C:R
[65534]C)"

'Format Totals
xlApp.Application.Range("K2:AD2").Select
xlApp.Application.Selection.Style = "Comma"

'Freeze Pane
xlApp.Application.Range("D5").Select
xlApp.Application.ActiveWindow.FreezePanes = TRUE

'Format field to 3 decimal places
xlApp.Application.Columns("H:H").Select
xlApp.Application.Selection.NumberFormat

'Autofit Columns
xlApp.Application.Cells.Select

xlApp.Application.Cells.EntireColumn.AutoFit

For Each...Dynamically formatting multiple worksheets
 

To loop though all sheets in a workbook use something like the following

Sub MultiSheet()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
sht.Range("A1").Value = 10
Next sht
End Sub

Precede each command with sht. so that it is performed on each worksheet as
the code loops through all worksheets

For Each...Dynamically formatting multiple worksheets
 

Sub tester3()
'Define the Excel Application Object
Dim xlApp As Object

'Create the Excel Object
Set xlApp = CreateObject("Excel.Application")

'Open the desired Excel File
Set xlbk = xlApp.Workbooks.Open("c:sample.xls")

For Each sh In xlbk.Worksheets
'Insert Rowsfor Control Totals
With sh
.Activate
.Rows("1:3").Insert

'Calculate Totals
..Range("K2").FormulaR1C1 = _
"=SUM(R[3]C:R[65534]C)"

'Format Totals
..Range("K2:AD2").Style = "Comma"

'Freeze Pane
..Range("D5").Select
..Parent.Windows(1).FreezePanes = True

'Format field to 3 decimal places
..Columns("H:H").NumberFormat = "#,###.000"

'Autofit Columns
..Cells.EntireColumn.AutoFit
End With
Next sh
End Sub


--
Regards,
Tom Ogilvy

Robert wrote in message
...
I am creating multiple worksheets in a single workbook.
The workbook is being created by another application (Data
Junction). Each worksheet has the same columns, however
the number of worksheets (and their corrsponding sheet
names) will vary on a monthly basis.

I have been able to succssfully format a single worksheet,
but am having trouble applying the same
formatting/functions to multiple sheets in the same
workbook.

Example code is provided below...How might I apply the For
Each...Next syntax around this so that all worksheets are
formatted the same?

Thanks

*******

'Define the Excel Application Object
Dim xlApp As Object

'Create the Excel Object
Set xlApp = CreateObject("Excel.Application")

'Open the desired Excel File
xlApp.Application.Workbooks.Open "c:sample.xls"

'Insert Rowsfor Control Totals
xlApp.Application.Rows("1:3").Select
xlApp.Application.Selection.Insert Shift = "xlDown"

'Calculate Totals
xlApp.Application.Range("K2").Select
xlApp.Application.ActiveCell.FormulaR1C1 = "=SUM(R[3]C:R
[65534]C)"

'Format Totals
xlApp.Application.Range("K2:AD2").Select
xlApp.Application.Selection.Style = "Comma"

'Freeze Pane
xlApp.Application.Range("D5").Select
xlApp.Application.ActiveWindow.FreezePanes = TRUE

'Format field to 3 decimal places
xlApp.Application.Columns("H:H").Select
xlApp.Application.Selection.NumberFormat

'Autofit Columns
xlApp.Application.Cells.Select

xlApp.Application.Cells.EntireColumn.AutoFit