Hi, I just wondering that are there anyway to format multiple excel files at once? I don't mine using 3rd party software or some vba script.

Hi, Are all the workbooks identical in structure, number of worksheets, worksheet names and the addresses of cells to be formatted? How do you specify what formatting is to be applied? Is the formatting information in a text file, are you copying formatting from an existing file, or are you hoping to format on the fly, and if so how. Regards

Yes. 100% identical, but only with different names. I only want all the files have the same format and page setup.

Hi, Here is a macro that will copy formating from a master workbook to a series of other, named workbooks. I haven't tested if page settings are copied with this, so more code may be needed to setup pages for printing. Anyway see if this works for the formatting. As noted, all workbooks are identical (same worksheets in each). The names are all hardcoded in this example code. The array holding the names will have to be re-sized appropriate to the number of files you have, only 4 in this example. I didn't use the zero element of the array : strWBNames(0). Dim strWBNames(4) The path to the files is hardcoded : strPath = "C:\temp\" Change as required - don't forget the closing "\" The following code is entered in a standard module in the master workbook. Named "Copy_Master.xls" for this example. Option Explicit Public Sub WBFormat() Dim strMasterFile As String Dim strWBNames(4) Dim strPath As String Dim n As Integer Dim objWS As Worksheet On Error Resume Next 'set master file name strMasterFile = "Copy_Master.xls" 'set path to files (note closing "\") strPath = "C:\temp\" 'setup file names strWBNames(1) = "Copy_1.xls" strWBNames(2) = "Copy_2.xls" strWBNames(3) = "Copy_3.xls" strWBNames(4) = "Copy_4.xls" 'Copy formatting from each worksheet in Copy_Master.xls to 'each worksheet in the named workbooks For n = 1 To 4 Workbooks.Open (strPath & strWBNames(n)) For Each objWS In Workbooks(strMasterFile).Worksheets objWS.Cells.Copy Workbooks(strWBNames(n)).Worksheets(objWS.Name).Range("A1") _ .PasteSpecial Paste:=xlPasteFormats Next objWS Workbooks(strWBNames(n)).Save Next n End Sub To enter the subroutine code: From your workbook enter Alt + f11 (both the Alt key and the #11 function key at the same time), or Tools-Macro-Visual Basic editor. This opens up the VBA Editor In the left side Project Explorer pane, look for the name of your master workbook (all workbooks are preceded by VBAProject). Right click on the name and select Insert. Insert Module (not class module) A new module (Module1) will be added. Double click on it. Now in the main window paste the code, then Save the project (File - Save) This code is pretty basic. I didn't include any specific error checking, - just let it continue even if an error occurs. You could comment out the On Error Resume Next and the code should stop if an error occurs. The reformatted files are saved, but not closed - you could add a close file to each loop once you have the whole thing up and running properly. Finally link the subroutine code to a button on a toolbar to make it easy to run. For testing just place cursor inside the code on the VBA page. Hit f8 to start single stepping and f5 to run. Regards

Hi, As I thought, page setup does not get copied with the PasteSpecial command. If you add this code to the routine I posted before, you will be able to copy specific page setups. I just included headers and footers in the example. You can see where this fits in the existing code from the 1st and last lines of the new code. For Each objWS In Workbooks(strMasterFile).Worksheets objWS.Cells.Copy With Workbooks(strWBNames(n)).Worksheets(objWS.Name) .Range("A1").PasteSpecial Paste:=xlPasteFormats With .PageSetup .LeftHeader = objWS.PageSetup.LeftHeader .CenterHeader = objWS.PageSetup.CenterHeader .RightHeader = objWS.PageSetup.RightHeader .LeftFooter = objWS.PageSetup.LeftFooter .CenterFooter = objWS.PageSetup.CenterFooter .RightFooter = objWS.PageSetup.RightFooter End With End With Next objWS If you record a macro when you setup page formating you will be able to look at the recorded macro and identify the formating items that you need to include, such as .CenterVertically .Orientation .LeftMargin The macro recording will include lots of default settings which you don't need to copy over. HTH Regards