How to format multiple sheets in Excel?

[anoble1]

Hello - I have made something that opens an excel document that was just exported. The workbook has 10 tabs. I wish there was someway to edit all the tabs instead of doing it tab by tab in the code. Is there a way to do that? Right now I am only able to do "Sheet1" of course.

Private Sub OpenAndFormatExcel()
 
    Dim filePath As String
 
    filePath = "C:\Users\Andy\Documents\Andy\Test.xlsx"
 
    Dim XL As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
 
    Set XL = New Excel.Application
 
    Set xlBook = XL.Workbooks.Open(filePath)
 
    Set xlSheet = xlBook.Worksheets("Sheet1")
 
    XL.Visible = True
    FreezeRow
 
    With xlSheet
 
        With .Range("A1:L1")
            .HorizontalAlignment = Excel.xlCenter
            .Font.Bold = True
            .Columns.AutoFit
            .EntireColumn.AutoFit
        End With
 
    End With
 
    XL.DisplayAlerts = False
 
    xlBook.Save
    '*** uncomment to keep open
    'xlBook.Close True
 
    XL.DisplayAlerts = True
 
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set XL = Nothing
 
End Sub
 
Sub FreezeRow()
Rows("2:2").Select
ActiveWindow.FreezePanes = True
End Sub
 

[NeoPa]

Have you tried recording doing what you want in Excel first? You can Select multiple Worksheets in Excel, as you may know, so then you add your formatting. When you stop recording the macro you have some example code that is usually very poor code, TBF, but nevertheless gives you hints at the steps you will need in your proper code. Does that make sense?

Separately, when converting from Excel code into something that works from within Access you need to ensure any defaults that are used by Excel are specified explicitly when running from Access. Check out Application Automation.

[tmudgal16]

I want to format all the worksheets like Sheet1. Here's what I'll do:

1) Group the worksheets by selecting Sheet1, holding down the Shift key and selecting Sheet4. This will group all the worksheets together:
Notice in the header it now says "Group":

2) Go to Home > Editing > Fill > Across Worksheets…
3) A dialog box will pop up. Select "Formats" and click OK:

[NeoPa]

While that last post doesn't deal with the question directly, as it isn't code-related at all, it is valid as it does give an example of how to duplicate formatting across Worksheets that can be captured by the macro recorder.

It's not exactly the approach I had in mind as applying specific format commands to a selection of Worksheets makes more sense to me and I suspect it would be easier to control exactly which formats are included across the selected Worksheets.

[SioSio]

At the beginning of your code, insert the code to select all sheets.

    Dim selSht As Variant
    Dim i As Long
    ReDim selSht(1 To Sheets.Count)
    For i = 1 To Sheets.Count
        selSht(i) = Sheets(i).Name
    Next i
    Sheets(selSht).Select

[Varsha1285]

1.Ctrl + Click each sheet tab at the bottom of your worksheet (selected sheets will turn white).

2. While selected, any formatting changes you make will happen in all of the selected sheets.

3. Double-click each tab when you are done to un-select them.

[NeoPa]

Hi Varsha.

I won't delete your post as I assume it was intended to help - and there may be some that read it later who find it helpful.

However, it really is necessary to read the question before posting an answer. The question was clear about wanting to edit multiple tabs (Worksheets) using code (VBA).

No matter how good the intention, it's frustrating to ask questions and see an answer - only to find it has nothing really to do with your question (Like this response to you now, unfortunately).

@ANoble.
Perhaps you could update the thread with where you are with it now. Hopefully my post #2 led you to what you needed.