Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy worksheets and save files dynamically | Excel Discussion (Misc queries) | |||
Dynamically linking subserviant worksheets to a master worksheet | Excel Worksheet Functions | |||
formatting multiple worksheets | Excel Worksheet Functions | |||
Linking and formatting multiple worksheets | Excel Worksheet Functions | |||
Eliminate formatting dulication in multiple worksheets | Excel Worksheet Functions |