Do Until Loop w Print Area Errors

[aeddipa]

I think this can be done, but need some assistance. I have two sheets. 1) MbrsbyRegion 2) GRPBR Essentially, I have many named ranges called "Area A", "Area B" "Area H" etc... that are in the "GRPBR" sheet. The members by region sheet lists all members associated with each region/area. I want to create a loop print macro that will print out only the named ranges that have a member in that area (i.e. > 0). My issue is starting with the PageSetup.PrintArea line, but I'm sure there may be other issues besides this. Basically, in my "MbrsbyRegion" sheet, I have an if function stating if Cell B2 (column where member data is kept) > 0 then list "Area [insert letter here]". The named ranges in the "GRPBR" sheet are the same as the "MbrsbyRegion" sheet so I want to reference the value within that cell as my Range for the "GRPBR" sheet.

Hopefully this isn't too confusing. Please let me know if more clarity is needed. In short, named ranges of grpbr need to be referenced by formula in mbrsbyregion so range is correctly selected and printed.

The Do until = 16 because there are 16 different regions/areas.

Sub Print_Areas()


Dim FirstCell As Range
Dim FirstTargetCell As Range
Dim i As Integer


Set FirstCell = Sheets("mbrsbyregion").Range("B2")
i = 0


Do
FirstCell.Offset(i, 0).Select
If FirstCell.Offset(i, 0).Value > 0 Then
[B]Sheets("GRPBR").PageSetup.PrintArea = Sheets("GRPBR").Range(Sheets("mbrsbyregion").Range(FirstCell.Offset(i, 0).Value)).Address[/B]
Sheets("GRPBR").Activate
ActiveSheets.PrintOut Copies:=1, Collate:=True
i = i + 1
End If
Loop Until FirstCell.Offset(i, 0).Value = 16




End Sub

 

[patel45]

please upload a sample file on a share site then paste the link here

 

[aeddipa]

please upload a sample file on a share site then paste the link here

I can't while at work. The "GRPBR" sheet is lets say the same amount of rows for each Area. So A1:J30 is Area A; A31:J60 is Area B; A61:J90 is Area C, etc...
So my loop should do the following based on the below table of mbrsbyregion:

Named Ranges
-----------------
A1:J30 = Area A
A31:J60 = Area B
A61:J90 = Area C

Set Print Area Range("Area A")
Print
Set Print Area Range("Area C")
Print
*** There is no print for Area B as no members are found in the table below***

This is how mbrsbyregion appears

Area Members If Function
----- ---------- ------------
A 1 Area A
B 0
C 3 Area C
D
E
F
G
H
I
J
K
L
M
N
O
P