For Each...Dynamically formatting multiple worksheets

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

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
 
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
 
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
 
Format Copier Tool

The free Format Copier tool from excelcampus.com automates the process of applying formatting to multiple worksheets in multiple workbooks. It stores your formatted template sheets which you use to apply formatting to unformatted sheets. So all you have to do is open your workbook and press a few buttons, and your worksheets will be formatted exactly like the template. This is great if you're formatting worksheets on a daily basis.

It copies page layout options, page breaks, cell and conditional formatting, outlines, groups, freeze panes, column widths, row heights, and more. And allows you to specify which formatting options you want to copy.

Download it free at http://excelcampus.com/tools/format-copier and check out the video tutorial for more info.
 
Back
Top