Hello All,
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
pivot charts.

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
sheets.)

I have recorded the following macro for sheet

Sub Macro1()
'
Rows("1:1").Select
Selection.Font.Bold = True
Cells.Select

With Selection.Font
.Name = "Trebuchet MS"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select

End Sub


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
knowledge)

Sub WorkWithFiles()
Dim as long
Dim wkbk as Workbook
With Application.FileSearch
.NewSearch
.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
' macro1
wkbk.Close SaveChanges:=False
Else
MsgBox "There were no files found."
End If
End With
End Sub

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

Nasir