Debug VBA for dynamic print range

[jrc999]

I am putting together a template file that users will copy sheets into, and run macros to create reports. I'm trying to use VBA to set the print range for a pivot table, when the number of rows and columns can be different for each report created (i.e., each time the template is used).

I'm getting a Run-time error '1004' The formula you typed contains an error on this line:

ActiveSheet.PageSetup.PrintArea = "BHPivot!R1C1:R" & lrow& "C" & lcol

The code is as follows:

        Dim lcol As Long
        Dim lrow As Long
        lrow =Sheets("BHPivot").Range("a" & Rows.Count).End(xlUp).Row
        lcol =Sheets("BHPivot").Cells(lastrow, Columns.Count).End(xlToLeft).Column
       ActiveSheet.PageSetup.PrintArea ="BHPivot!R1C1:R" & lrow & "C" & lcol

I adapted this from code I used to set a dynamic pivot table range, and it works perfectly for that use. Just can't figure out why it's giving an error here.

Thanks so much!


<strike></strike>

 

[JustynaMK]

Hi,

Try converting your print range into "A1" reference style:

    Dim lcol            As Long
    Dim lrow            As Long
    Dim sPrint          As String
    
    lrow = Sheets("BHPivot").Range("a" & Rows.Count).End(xlUp).Row
    lcol = Sheets("BHPivot").Cells(lrow, Columns.Count).End(xlToLeft).Column
    sPrint = Application.ConvertFormula("R1C1:R" & lrow & "C" & lcol, xlR1C1, xlA1)
    ActiveSheet.PageSetup.PrintArea = "BHPivot!" & sPrint

I also noticed that in your lcol command you are referring to "lastrow", instead of "lrow".

I hope it helps.

 

[jrc999]

Hugely helpful--that's a great solution! And thank you so much for catching that error.

Appreciate your help so much!!!

 

[Legacy 433886]

jrc,
Re: setting print areas with VBA

Print area requires an A! style reference. Also, "lastrow" looks suspicious.
This works for Sheet1...
'---
Sub Revised()
Dim lcol As Long
Dim lrow As Long
Dim strWhere As String

With Sheets("Sheet1") ' or "BHPivot"
lrow = .Range("a" & .Rows.Count).End(xlUp).Row
lcol = .Cells(lrow, .Columns.Count).End(xlToLeft).Column
strWhere = .Range(.Cells(1, 1), .Cells(lrow, lcol)).Address
.PageSetup.PrintArea = strWhere
End With
End Sub
'---
Jim Cone