Range Macro

[imi123]

I have a spreadsheet, where I am trying to set a range via macro to print, but every month a new column is added. The rows remain the same. Can you suggest a macro I can create to capture any new columns.

 

[VoG]

Perhaps like this

Sub prt()
With ActiveSheet
    .PageSetup.PrintArea = .UsedRange.Address
    .PrintOut
End With
End Sub

 

[imi123]

Hi,

Your macro works for one sheet but I have mulitple sheets, If I copy paste the coding and change the name of the sheets accordingly, the macro stops working:

Here is what I have:

Sub prt()
Sheets("3. Summary of Results 1Q2011").Select
.PageSetup.PrintArea = .UsedRange.Address
.PrintOut
Sheets("5. Accrued Claims").Select
.PageSetup.PrintArea = .UsedRange.Address
.PrintOut
Sheets("6. Mix History").Select
.PageSetup.PrintArea = .UsedRange.Address
.PrintOut
Sheets("14. Data Updated").Select
.PageSetup.PrintArea = .UsedRange.Address
.PrintOut
Sheets("1Q2011").Select
.PageSetup.PrintArea = .UsedRange.Address
.PrintOut
End With
End Sub

 

[imi123]

The error that I get is on the .UsedRange ...saying Invalid or unqualified reference..

 

[VoG]

Try

Sub prt()
With Sheets("3. Summary of Results 1Q2011")
    .PageSetup.PrintArea = .UsedRange.Address
    .PrintOut
End With
With Sheets("5. Accrued Claims")
    .PageSetup.PrintArea = .UsedRange.Address
    .PrintOut
End With
With Sheets("6. Mix History")
    .PageSetup.PrintArea = .UsedRange.Address
    .PrintOut
End With
With Sheets("14. Data Updated")
    .PageSetup.PrintArea = .UsedRange.Address
    .PrintOut
End With
With Sheets("1Q2011")
    .PageSetup.PrintArea = .UsedRange.Address
    .PrintOut
End With
End Sub

 

[VoG]

Or, better

Sub prt()
Dim ws As Worksheet
For Each ws In Worksheets(Array("3. Summary of Results 1Q2011", "5. Accrued Claims", "6. Mix History", "14. Data Updated", "1Q2011"))
    With ws
        .PageSetup.PrintArea = .UsedRange.Address
        .PrintOut
    End With
Next ws
End Sub

 

[imi123]

Thanks it worked....

 

[imi123]

Hey- What if for one of the tabs you dont want to print to a certain amount of rows...I have some comments and cells with data that I dont want to see on the print out...I want to restrict the macro to a certain range of rows but at the same time recognize any new columns being added...

 

[VoG]

Try recording a macro whilst setting the print area and posting the recorded code here.

 

[imi123]

' Printtest Macro
'
'
Sheets("4. 80% Reserves 1Q2011").Select
Range("A1:CC37").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$CC$37"
Sheets("5. Accrued Claims").Select
Range("B1:BD21").Select
ActiveSheet.PageSetup.PrintArea = "$B$1:$BD$21"
Sheets("6. Mix History").Select
Sheets("7. 1Q Paid & Accrued").Select
Range("A1:BD219").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$BD$219"
End Sub