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

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
0
AndreasHermle
Asked:
AndreasHermle
  • 3
  • 3
1 Solution
 
kgerbChief EngineerCommented:
We can probably do this at the same time we are searching and replacing :-)

Kyle
0
 
kgerbChief 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
 
AndreasHermleAuthor 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
AndreasHermleAuthor 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
 
AndreasHermleAuthor 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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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