Dynamic Print Range

[bgonen]

I am trying to:

First, make column A as the column to repeat (page setup,,)

Second; Highlight the range A1 to the last used cell (the row should be 59)

Third; add page breaks every 15 columns

Fourth; Print preview

 

[bgonen]

As a start
I am trying to use my dynamic range as belowBut of course I get error/s)

Sub PrintPreview()

Dim LC As Long
With Sheets("TMHP-PL")
LC = .Cells(A1, Columns.Count).End(xlToLeft).Column
.PrintArea = Range("a1" & LC).SpecialCells(xlCellTypeVisible).Address
End With
End Sub

 

[VoG]

Maybe like this (and please stop sending me PMs when I am trying to answer your questions!)

Sub PrintPreview()

Dim LC As Long
With Sheets("TMHP-PL")
    LC = .Cells(1, Columns.Count).End(xlToLeft).Column
    PageSetup.PrintArea = .Range(.Cells(1, 1), .Cells(59, LC)).SpecialCells(xlCellTypeVisible).Address
End With
End Sub

 

[VoG]

Missed a dot

Sub PrintPreview()

Dim LC As Long
With Sheets("TMHP-PL")
    LC = .Cells(1, Columns.Count).End(xlToLeft).Column
    .PageSetup.PrintArea = .Range(.Cells(1, 1), .Cells(59, LC)).SpecialCells(xlCellTypeVisible).Address
End With
End Sub

 

[djreiswig]

Here is a fix for your last post. I'm not sure how this fits in with your first request, but this sets the print area to the used columns. Also, you should refrain from naming a sub PrintPreview, I think it is a reserved word.

Sub PPV()
    Dim LC As String
    With Sheets("TMHP-PL")
        LC = Left(.Cells(1, .Cells(1, .Columns.Count).End(xlToLeft).Column).Address(1, 0), _
            InStr(1, .Cells(1, Cells(1, .Columns.Count).End(xlToLeft).Column).Address(1, 0), "$") - 1)
        .PrintArea = .Range("A:" & LC).SpecialCells(xlCellTypeVisible).Address
    End With
End Sub

 

[bgonen]

That's a progress thanks a lot

Now, what if I want to fit to multiple ranges/pages;

For example Cell A1:O59 will be the first page

Then P1:AC59 will be the second page
(I am trying to make column A as the column to repeat)

In other words, Column A will repeat on the left and then the following 14 columns together will compose a page,,

Also, I try to Landscape each page

 

[djreiswig]

This will repeat column A on every page. If you have your column widths set so that only 16 columns will fit on a page then you should get the desired results.

    With Sheets("TMHP-PL")
        .PageSetup.PrintTitleColumns = "$A:$A"
    End With

 

[bgonen]

Thanks
How do I set my column widths to 14 columns?
(btw, I use Excel 2007)

 

[djreiswig]

Not sure on 2007. I usually just play with the widths until the page break shows up in the right spot. BTW, I use 2003.