Macro to run on all files in a folder but exclude sheets with PivotTable, Pivot Charts and Charts
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 have recorded the following macro for sheet
Selection.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
I have seen the following macro which would open all files in a folder
- I am not sure whether it would work for all sheets (pardon my
Dim as long
Dim wkbk as Workbook
.LookIn = "C:\MyFolder"
.SearchSubFolders = False
.FileName = ".xls"
' .FileType = msoFileTypeAllFiles
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
set wkbk = Workbooks.Open(.Foundfiles(i))
' work with the wkbk reference
MsgBox "There were no files found."
I wish the macro run on all sheets in the workbook but to exclude
Pivot Table, Pivot Charts and Chart Sheet.
Can anyone help me with this please?
Thanks in advance