Macro to run on all files in a folder but exclude sheets with Pivot Table, Pivot Charts and Charts
"Nasir" wrote in message
I am using Excel 2007 and have the following problem.
I have many excel files in a folder C:\MyData.
I want to format the data in all sheets in all excel files in MyData
Folder as follows:
1) Whole sheet will be Font Size 11, Trebuchet MS.
2) First Row would be bold and with cell color in yellow
3) Columns to be Autofit for the whole sheet
4) Cell pointer to be in A1 before saving the file.
Each workbook has many sheets which includes pivot table, charts and
I want to run the macro to open all Excel files in MyData folder and
run on all sheets (excluding the pivot table, chart and Pivot Chart
I wish the macro run on all sheets in the workbook but to exclude
Pivot Table, Pivot Charts and Chart Sheet.
I renamed your Macro1 as FormatWorksheet and modified it to run faster
by removing all the .Selects and .Activates except the last.
ScanWorkbooks and ProcessWorkbook are taken from existing routines that
I use on a regular basis ... I added code to use the status bar to
display the workbook and sheet names while the macro is running.
xOpenWorkbook is a function that returns false if it cannot open the
requested workbook; I leave that code for you as an exercise.
Private xSaveChanges As Boolean
Sub FormatWorksheet(sh As Worksheet)
sh.Rows("1:1").Font.Bold = True
.Name = "Trebuchet MS"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
Private Sub ScanWorkbooks()
Dim filename As String
Dim saveStatusBar As Boolean
saveStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
' Get list of workbook files in "current" folder
filename = ActiveWorkbook.Path & "\*.xl??"
filename = Dir(filename)
ProcessWorkbook ActiveWorkbook.Path & "\" & filename
filename = Dir()
Loop Until filename = ""
Application.DisplayStatusBar = saveStatusBar
Sub ProcessWorkbook(LongName As String)
Dim s As Worksheet
If xOpenWorkbook(LongName:=LongName) Then 'do nothing if not open
'ActiveWorkbook is now LongName
xSaveChanges = True ' default is to save changes to workbook
' Skip chart sheets
For Each s In .Worksheets
If s.ChartObjects.Count 0 Then
Exit For ' bypass sheets that contain embedded charts
If s.PivotTables.Count 0 Then
Exit For ' bypass sheets that contain pivot tables
Application.StatusBar = .Name & "!" & s.Name
Next s 'In ActiveWorkbook.Sheets
Application.StatusBar = False
End If 'Open LongName
(clare reads his mail with moe, nomail feeds the bit bucket :-)