On Mar 5, 2:31*pm, Nasir wrote:
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

As I recall, file seach does not work in xl2007 so I suggest looking
in the vba help index ( or Google excel:DIR )
http://www.google.com/#sclient=psy&h...b51816635ef892
and incorporate your sheet macro (modify to only change what is
needed), excluding sheet names desired.
"If desired, send your file to dguillett1 @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."