1

I've 4 different worksheet & different table. I trying to change the formatting using loop for all the worksheet but the range defined below is giving an error -

Method 'Range' of object '_Global' failed.

Data I have already in table form.

Dim WSHT As Worksheet
    For Each WSHT In ActiveWorkbook.Worksheets
    WSHT.Select
    ActiveSheet.UsedRange.Select
    Selection.RowHeight = 50
    Selection.VerticalAlignment = xlCenter
    Selection.WrapText = True
    Selection.Font.Name = "GE Inspira Sans"
    Selection.Font.Size = 9
    Dim ALLTAB As ListObject
    For Each ALLTAB In WSHT.ListObjects
    Range("[[#All],[Customer Name]]").ColumnWidth = 20
    Range("[[#All],[Customer Name]]").HorizontalAlignment = xlLeft
    Next ALLTAB
Next WSHT

was using the below for formatting but I've 26 columns for update for each worksheet.

Range("OSD_TABLE_1[[#All],[Customer Name]]").ColumnWidth = 20
Range("OSD_TABLE_1[[#All],[Customer Name]]").HorizontalAlignment = xlLeft
braX
  • 11,506
  • 5
  • 20
  • 33
Jeroho One
  • 11
  • 1
  • Your `Range` objects are not qualified, so they assume `ActiveSheet`. Those lines are not aware they are in a loop. – braX Feb 07 '23 at 09:46

1 Answers1

0

Rewrote the lines as

Dim WSHT As Worksheet

For Each WSHT In ActiveWorkbook.Worksheets

WSHT.UsedRange.RowHeight = 50

WSHT.UsedRange.VerticalAlignment = xlCenter

WSHT.UsedRange.WrapText = True

WSHT.UsedRange.Font.Name = "GE Inspira Sans"

WSHT.UsedRange.Font.Size = 9

Dim ALLTAB As ListObject

For Each ALLTAB In WSHT.ListObjects

ALLTAB.ListColumns("Customer Name").Range.ColumnWidth = 20

ALLTAB.ListColumns("Customer Name").Range.HorizontalAlignment = xlLeft

Next ALLTAB

Next WSHT

Jeroho One
  • 11
  • 1