vba excel set dynamic named ranges

Question

I want to set all the named range in one variable and export to PDF. I am able to do when I manually enter all the named range. My problem is named range are variable, sometime it will have one some time it will have more than 10. Please advice...I tried with following codes.

Dim wbBook As Workbook
Dim nName As Name

Set wbBook = ActiveWorkbook
Set nName=wbBook.Names

Set rs = wbBook.Range(nNames)
rs.Select
Selection.ExportAsFixedFormat xlTypePDF, strPath, , , False

But below code works for me, when I enter range name manually..

Set rs = wbBook.Range("Page_1,Page_2,Page_3")
rs.Select
Selection.ExportAsFixedFormat xlTypePDF, strPath, , , False

you setup Dim nName As Name but in your line you have Set rs = wbBook.Range(nNames) , nNames instead of nName — Shai Rado
– Shai Rado, Commented Oct 4, 2016 at 6:32
really Set rs = wbBook.Range("Page_1,Page_2,Page_3") works? — user3598756
– user3598756, Commented Oct 4, 2016 at 6:33
Yes, That worked for me and gave me output in three pages in single PDF file. — Manish
– Manish, Commented Oct 6, 2016 at 4:45

vacip · Accepted Answer · 2016-10-04 07:48:34Z

4

You can't pass a collection (many objects) to an object variable (single object). You need to loop through the collection's objects. Usually we do this with a For Each loop, but in this case, as I needed to set the first element separately because of the Union, I have used a simple For loop.

Also, avoid using .Select if you can. Just use your objects directly.

Also note that you are talking about all the named ranges in the Workbook, and this might cause problems if you have named ranges in multiple worksheets. I haven't tested, but I doubt it would work.

Dim wbBook As Workbook
Dim i As Integer
Dim rs As Range

Set wbBook = ActiveWorkbook
Set rs = wbBook.Names(1).RefersToRange
For i = 2 To wbBook.Names.Count
   Set rs = Union(rs, wbBook.Names(i).RefersToRange)
Next

rs.ExportAsFixedFormat xlTypePDF, strPath, , , False

answered Oct 4, 2016 at 7:48

vacip

5,4563 gold badges29 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user3598756 Over a year ago

this assumes that all workbook named ranges reside in the same worksheet, otherwise Union() would error out.

vacip Over a year ago

@user3598756 true, I have mentioned this. I assumed they were all in the same sheet, otherwise OP's original code would be very far from a solution.

user3598756 Over a year ago

you're right: I only looked at your code and didn't see the initial caveat. As to the OP's initial code, well 'wbBook.Range' is what I'd call a "very far from solution" start...

Manish Over a year ago

Its working; however all the named range are printed in single page, whereas I want each named range in single page. I dynamically do the page setting to fit single page (.FitToPagesWide = 1 , .FitToPagesTall = 1) for each named range. When I name the range individually, it works. I think, we need to keep the original page settings in "rs". Will you assist me on this ?, I am very new to vba.My thanks in advance

vacip Over a year ago

Hi @Manish! We will gladly help, but since it is a new question, please ask a new question. Also, if my answer answered your question, please mark it as accepted by clicking on the green tick mark on the left.

|

Collectives™ on Stack Overflow

vba excel set dynamic named ranges

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related