Macro help with sorting & printing

[Paul at GTS]

Hi

I have a worksheet with 60 Columns. Col A contains product groups. Working across the sheet all subsequent cols on row1 have customer name and then running down the col various discounts that customer receives for the different product groups. The cols contain some blanks as not all customers get all discounts.

So What I am trying to do - and can do manually is :

1. Sort each customers discount to remove all blank cells - This I have been doing with the Filter - Do not show blanks
2. Print col A Product groups with subsequent customer discounts - this I have been doing by selectin A + B cols, setting print area and printing - All good
3. From col just sorted - re select all - so blanks are again shown
4. Hide customer col just sorted
5. Go to next customer col and repeat from set 1

It all works fine when I do it manually but when recording macro it never prints the cols as I progress across the sheet. My lack of VB knowledge stops me from sorting it.

Here is a print of the recorded macro

Sub CDP_1()
'
' CDP_1 Macro
'
' Keyboard Shortcut: Ctrl+p
'
ActiveSheet.Range("$A$1:$BH$159").AutoFilter Field:=2, Criteria1:="<>"
ActiveCell.Columns("A:B").EntireColumn.Select
ActiveSheet.PageSetup.PrintArea = "$A:$B"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveSheet.Range("$A$1:$BH$159").AutoFilter Field:=2
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Selection.EntireColumn.Hidden = True
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveSheet.PageSetup.PrintArea = ""
End Sub

Any ideas ?

 

[Fluff]

Try

Sub CDP_1()
'
' CDP_1 Macro
'
' Keyboard Shortcut: Ctrl+p

    Dim UsdCols As Long
    Dim Cnt As Long
    
    UsdCols = Cells(1, Columns.Count).End(xlToLeft).Column
    For Cnt = 2 To UsdCols
        ActiveSheet.Range("$A$1:$BH$159").AutoFilter Field:=Cnt, Criteria1:="<>"
        ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(1, Cnt)).EntireColumn.Address
        ActiveWindow.SelectedSheets.PrintOut Copies:=1
        ActiveSheet.ShowAllData
        Columns(Cnt).Hidden = True
    Next Cnt
    ActiveSheet.PageSetup.PrintArea = ""
End Sub