"Nasir" wrote in message
...
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 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.

Enjoy!

Option Explicit

Private xSaveChanges As Boolean


Sub FormatWorksheet(sh As Worksheet)
'
sh.Rows("1:1").Font.Bold = True

With sh.Cells.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 sh.Cells.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With

sh.Cells.EntireColumn.AutoFit
sh.Range("A1").Select

End Sub


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)

Do
'Debug.Print filename
ProcessWorkbook ActiveWorkbook.Path & "\" & filename
filename = Dir()
Loop Until filename = ""

Application.DisplayStatusBar = saveStatusBar

End Sub

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

With ActiveWorkbook
' Skip chart sheets
For Each s In .Worksheets
If s.ChartObjects.Count 0 Then
Exit For ' bypass sheets that contain embedded charts
End If
If s.PivotTables.Count 0 Then
Exit For ' bypass sheets that contain pivot tables
End If

Application.StatusBar = .Name & "!" & s.Name
FormatWorksheet s

Next s 'In ActiveWorkbook.Sheets

Application.StatusBar = False
.Close SaveChanges:=xSaveChanges
End With

End If 'Open LongName

End Sub


--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)