Apply Table Style To Range Of Worksheets

[Al Del]

Would like to apply a table style to a range of worksheets, I get a "Compile error: no sub or function not defined." error when I run this code.

The tables have identical number of columns with varying rows. The all start at A1.

Sub ChangeStyle()
Dim x As Integer

 For x = 6 To Worksheets.Count
    With Workheets(x)
      .ListObjects.Add(xlSrcRange, .Range("$A$1"), , xlYes).Name = "Table" & x
      .ListObjects("Table" & x).TableStyle = "TableStyleMedium10"

    End With
 Next x

End Sub

 

[kpasa]

change

Worksheets(x)

to

Sheets(x)

 

[Al Del]

Thanks, I still get an error, "A table can't ovrelap another table." on line:

.ListObjects.Add(xlSrcRange, .Range("$A$1"), , xlYes).Name = "Table" & x

 

[My Aswer Is This]

Try this:

Sub Table_Style()
'Modified 6/28/18 7:15 PM EDT
Dim lb As ListObject
Dim i As Long
For i = 6 To Sheets.Count
    For Each lb In Sheets(i).ListObjects
        lb.TableStyle = "TableStyleMedium10"
    Next
Next
End Sub

This assumes you only want to change the Table Style

 

[kpasa]

Thanks, I still get an error, "A table can't ovrelap another table." on line:

that error is because you have to delete the table first.

 

[Al Del]

Worked great, thank you.

Trying to make the procedure more user friendly, by allowing the user to type the table style into cell B20, sheet5 (Merge).

Go from this,

lb.TableStyle = "TableStyleMedium10"

To something like this (this doesn't work), ideally I would like to only type "Medium10" into B20
and concatenate it to the "TableStyle" statement.

lb.TableStyle = ThisWorkbook.Sheet5.Range("B20")

 

[My Aswer Is This]

Why not try this:
You will get a Inputbox wanting to know the style you want

Sub Table_Style()
'Modified 6/29/18 12:23 AM EDT
Dim lb As ListObject
On Error GoTo M
Dim i As Long
Dim ans As String
ans = InputBox("Enter style you want", "TableStyle", "TableStyle")
For i = 6 To Sheets.Count
    For Each lb In Sheets(i).ListObjects
        lb.TableStyle = ans
    Next
Next
Exit Sub
M:
MsgBox "Your Table Style of  " & ans & vbNewLine & "Does not Exist"
End Sub

 

[Al Del]

Works good, can the cursor on the message box be move to the end of the TableStyle text, so it doesn't accidentally get deleted.

I also noticed there is no style designation for table style "none" is there something that can be entered to show no table style?

 

[My Aswer Is This]

I know of no way to move cursor to end of line.
To clear all table formatting press the Cancel Key.
You will see this note in the Message box Title menu.

Sub Table_Style()
'Modified 6/29/18 1:05 AM EDT
Dim lb As ListObject
On Error GoTo M
Dim i As Long
Dim ans As String
ans = InputBox("Enter style you want", "To clear table style press Cancel", "TableStyle")
For i = 6 To Sheets.Count
    For Each lb In Sheets(i).ListObjects
        lb.TableStyle = ans
    Next
Next
Exit Sub
M:
MsgBox "Your Table Style of  " & ans & vbNewLine & "Does not Exist"
End Sub

 

[My Aswer Is This]

Try this:

TableStyle will not be in Input box:

Just enter Dark3 or what ever you want.
Pressing Cancel or entering nothing will set Table style to nothing.

Sub Table_Style()
'Modified 6/29/18 1:35 AM EDT
Dim lb As ListObject
On Error GoTo M
Dim i As Long
Dim ans As String
ans = InputBox("Enter style you want", "To clear table style press Cancel")
For i = 6 To Sheets.Count
    For Each lb In Sheets(i).ListObjects
     If ans <> "" Then
     lb.TableStyle = "TableStyle" & ans
    Else
    lb.TableStyle = ""
    End If
    
    Next
Next
Exit Sub
M:
MsgBox "Your Table Style of  " & ans & vbNewLine & "Does not Exist"
End Sub