Apply a user-defined table style to named ranges in one go

Dear Experts:

I got a whole bunch of named ranges where I need to apply a user-defined table style in one go.

The ranges to which the user-defined table style has to be applied are named range_01, range_02, range_03 and so forth.

So, how can I apply this user-defined table style in one go to all these named ranges (workbook level scope names) ?

Thank you very much in advance.

I have attached a sample file with sample ranges / data tables along with user-defined table styles for your convenience.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

Regards, Andreas
ApplyTableStyle.xlsx
Andreas HermleTeam leaderAsked:
Who is Participating?
 
kgerbConnect With a Mentor Chief EngineerCommented:
Try this.  I think it will apply the formatting like you want.
Sub StyleAllNamedRanges()
Dim n As Name, sht As String
For Each n In ThisWorkbook.Names
    sht = Replace(Mid(n, 2, InStr(1, n, "!") - 2), "'", "")
    Sheets(sht).ListObjects.Add(xlSrcRange, Range(n), , xlYes).TableStyle = "styl_red_grey"
Next n
End Sub

Open in new window

Kyle

Correction:
Since your tables have headers I modified the final parameter to be xlYes instead of xlNo.
0
 
kgerbChief EngineerCommented:
We can probably do this at the same time we are searching and replacing :-)

Kyle
0
 
Andreas HermleTeam leaderAuthor Commented:
Hi Kyle,

this seems to work just fine. I'll do some more testing and then let you know.

Thank you very much for your swift and professional support.

Regards, Andreas
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Andreas HermleTeam leaderAuthor Commented:
Hi Kyle,

I slightly changed/tweaked the code so that only specific named ranges are taken care of

Sub StyleAllNamedRanges()
Dim n As Name, sht As String
For Each n In ThisWorkbook.Names

If InStr(n.Name, "range_") > 0 Then
    sht = Replace(Mid(n, 2, InStr(1, n, "!") - 2), "'", "")
    Sheets(sht).ListObjects.Add(xlSrcRange, Range(n), , xlYes).TableStyle = "styl_red_grey"
End If
Next n
End Sub
0
 
Andreas HermleTeam leaderAuthor Commented:
Concise, sophisticated coding.

Thank you very much for your professional help.

Regards, Andreas
0
 
kgerbChief EngineerCommented:
You're welcome.  Glad to help.
Kyle
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.