• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

Format Worksheets via VBA Array

Hello Experts,

Have the following code, but it is not working as I anticipated. Probably missed something obvious. Any thoughts?

Regards

LK
Sub Format_Sheet()
    Dim ws As Worksheet
        
    For Each ws In sheets(Array "Sheet1", "Sheet2", "Sheet3"))
    ws.Select

    'Formats entire worksheet with Courier New font
    Cells.Select
    With Selection.Font
        .Name = "Courier New"
        .Size = 9
    ActiveWindow.Zoom = 90
    
    Next ws
           
End Sub

Open in new window

0
lkirke
Asked:
lkirke
  • 2
  • 2
1 Solution
 
Saqib Husain, SyedEngineerCommented:
   For Each ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
0
 
Saqib Husain, SyedEngineerCommented:
use this code
Sub Format_Sheet()
    Dim ws As Worksheet
        
    For Each ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
    ws.Select

    'Formats entire worksheet with Courier New font
    Cells.Select
    With Selection.Font
        .Name = "Courier New"
        .Size = 9
    End With
    ActiveWindow.Zoom = 90
    
    Next ws
           
End Sub

Open in new window

0
 
broro183Commented:
a small modification to Ssaqibh's code will remove the "flicker" & may speed up the macro if you were running it over a larger array of sheets.
option Explicit
Sub Format_Sheet()
Dim ws As Worksheet
'to remove flicker
    Application.ScreenUpdating = False
    For Each ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
        ws.Select

        'Formats entire worksheet with Courier New font
        With ws.Cells.Font
            .Name = "Courier New"
            .Size = 9
        End With
        ActiveWindow.Zoom = 90

    Next ws
'to reset screenupdating
    Application.ScreenUpdating = True
End Sub

Open in new window



Ssaqibh, I'm sure I've seen code for formatting done on an array of sheets at some time in the past that doesn't loop through the sheets, but I can't replicate it tonight :(
Anyway, even if I could make it format all sheets without looping, we still need individual sheet activation for the "activewindow.zoom" line to work.

Rob
0
 
lkirkeAuthor Commented:
Thank you Experts for the prompt reponse. :)
0
 
broro183Commented:
hi Lk,

Thanks for the points but I don't think I deserve them all. After all, I only changed 3-4 lines of Ssaqibh's working code...

Rob
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now