select visible cells when printing
 

have worked it out, no worries

change the 9 in Range(MyStart, MyEnd(1, 9)).Select to 35

R

-----Original Message-----
I have a spreadsheet that is sorted by column "AF". I
have
written a macro that prints each section sorted by column
AF. In the first part of the macro I have hidden some
columns that i don't want printed but when then data is
selected i.e. the first nine visible columns the macro
only goes up to way the hidden cells start and no
further.
Please help. Below is the macro I have written.

Private Sub CommandButton1_Click()

Application.ScreenUpdating = True

Columns("A:DZ").EntireColumn.Hidden = False

Columns("B:B").EntireColumn.Hidden = True
Columns("D:K").EntireColumn.Hidden = True
Columns("N:Q").EntireColumn.Hidden = True

Rows("1:1000").EntireRow.Hidden = False

Range("AF2").End(xlDown).Select

lastrow = ActiveCell.Row

Range("AF3").Select

For a = 1 To lastrow - 1

ActiveCell.Offset(0, -31).Select

Set MyStart = Selection
ActiveCell.Offset(0, 31).Select

Mynumber = ActiveCell.Value
Set Myloc = Selection

Do Until ActiveCell.Offset(1, 0).Value < Mynumber
ActiveCell.Offset(1, 0).Select
Loop

ActiveCell.Offset(0, -31).Select

Set MyEnd = Selection

Range("A1").Select
ActiveCell.FormulaR1C1 = "=vlookup(" & Mynumber & ",Table!
C:C[1],2,0)"
myheader = ActiveCell.Value
ActiveCell.ClearContents

'Change the 9 to represent the numberof columns on the
data sheet

Range(MyStart, MyEnd(1, 9)).Select

With ActiveSheet.PageSetup
.PrintTitleRows = "$2:$2"
.CenterHeader = myheader
.LeftHeader = "Portfolio"
End With

Selection.PrintOut copies:=1, collate:=True
MyEnd.Offset(1, 31).Select

If ActiveCell.Value = "" Then a = lastrow
Next a

End Sub
.