Trying to set print area dynamically

[Ted]

I have a file that I use to enter data and then generate a page that
can be cut into little slips of paper, each of which contains content
based on one row of data. I use VBA to pull data from the top part of
the worksheet and generate the content below it on the same worksheet.

I don't need to print the data entry table but I do need to print the
content that is generated. The number of rows with data changes from
day to day and I would like to be able to use VBA to automatically set
the print area instead of doing it by hand each day. Some of the
content that is generated contains blank rows (to make it look nice),
so I can't use a method that relies on finding a blank row to set the
print area.

I had done some searching and thought I had found a way to do this,
but it's not working -- the print area ends up being 2 rows X 1
column. I need it to be (x*11) rows X 10 columns, where x is the
number of forms I am printing.

I'm pretty new to VBA (and macros in general), and a lot of this was
done by using the macro recorder, so it may be a little clumsy. Here
is what I have so far:
______________________________________________________________________
Sub GenerateSheet()
LoadArray
' The LoadArray sub goes through the data-entry table and loads the
data into an array

' We are printing two columns of forms, so we need a variable to track
which column we're on.
Dim ColCounter As Integer
ColCounter = 1

' Counter to keep track of how many rows of forms we've made
Dim SlipCounter As Integer
SlipCounter = 0

Range("A40").Select

For RowCounter = 1 To 30
' We only want to print a form if there is data to put in it.
If Stats(RowCounter, 5) > 0 Then
MakeCell 'This is the sub that generates the content for the
form.
If ColCounter = 1 Then
' We are in the first column, so we will just move to the
right.
ActiveCell.Offset(0, 5).Range("A1").Select
' Increment SlipCounter because we have just created
another row --
SlipCounter = SlipCounter + 1
Else
' We are in the second column, so we will move down and
left.
ActiveCell.Offset(11, -5).Range("A1").Select
End If

' Toggle the column counter
If ColCounter = 1 Then
ColCounter = 0
Else: ColCounter = 1
End If
End If
Next RowCounter

' Get the number of rows we need to print - each slip is 11 rows high.
SlipCounter = SlipCounter * 11

Range("A39").Select ' Although the sub starts at A40, the printable
area starts at A39.
ActiveCell.CurrentRegion.Resize(SlipCounter, 10).Select
ActiveSheet.PageSetup.PrintArea = ActiveCell.CurrentRegion.Address

End Sub
______________________________________________________________________________


I have done some tests and it seems like all of the variables end up
with the correct values by the time the print area is set, so I can't
figure out what the problem is. I would appreciate any help that
anyone can offer.

Thanks,

Ted
(e-mail address removed)

 

[Barb Reinhardt]

Your code seems fairly complicated. I generally use something like this to
define a range on a worksheet

Sub ResizeRange()
Dim lCol As Long
Dim lRow As Long
Dim aWS As Worksheet
Dim aWB As Workbook

Set aWS = ActiveSheet

lCol = aWS.Cells(1, aWS.Columns.Count).End(xlToLeft).Column
Set myrange = aWS.Cells(1, 1).Resize(1, lCol)
Debug.Print myrange.Address
lRow = aWS.Cells(aWS.Rows.Count, myrange.Column).Resize(1,
myrange.Columns.Count).End(xlUp).Row
Set myrange = myrange.Resize(lRow, myrange.Columns.Count)
Debug.Print myrange.Address
Set aWB = ActiveSheet.Parent

Rangeaddress = "=" & aWS.Name & "!" & myrange.Address
Debug.Print Rangeaddress
aWB.Names.Add Name:="Print_Area", RefersTo:=Rangeaddress

End Sub

I've left some DEBUG.PRINT statements in there so you can see what's
happening. I'm not sure if this is what you want. Let me know.

Barb Reinhardt

 

[Barb Reinhardt]

I didn't need to reference aWB at all.

Sub ResizeRange()
Dim lCol As Long
Dim lRow As Long
Dim aWS As Worksheet

Set aWS = ActiveSheet

lCol = aWS.Cells(1, aWS.Columns.Count).End(xlToLeft).Column
Set myrange = aWS.Cells(1, 1).Resize(1, lCol)
Debug.Print myrange.Address
lRow = aWS.Cells(aWS.Rows.Count, myrange.Column).Resize(1,
myrange.Columns.Count).End(xlUp).Row
Set myrange = myrange.Resize(lRow, myrange.Columns.Count)
Debug.Print myrange.Address

Rangeaddress = "=" & aWS.Name & "!" & myrange.Address
Debug.Print Rangeaddress
aWS.Names.Add Name:="Print_Area", RefersTo:=Rangeaddress

End Sub