Print Button and Lookup

[Big_OT]

Hello,

I have an excel document with a sheet for each month, I want to put a print macro button on each sheet which when pressed prints a certain range of the sheet, the range I want to print starts at A1 and finishes in column I, the row is based on how many entries there have been that month (and varies every month) and the last row of the range always has the word Total in column H.

I have tried this code but obviously the range changes and would be good to have it automatically update.

Sub PrintRange()
Range("A1:I50").PrintOut
End Sub

What would the best way to approach this be.
TIA

 

[cooper645]

The following should work for you;

Sub PrintRange()
Dim sht As Worksheet
Dim LastRow As Long
Set sht = ActiveSheet
LastRow = sht.Cells.Find("Total", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
Range("A1:I" & LastRow).PrintOut
End Sub

 

[Big_OT]

Thank you so much - exactly what I was looking for.

 

[Big_OT]

Am I able to have a confirmation window come up after I have pressed the Macro saying "are you sure you want to print" , "yes" , "no"

TIA

 

[cooper645]

Sub PrintRange()
Dim sht As Worksheet
Dim LastRow As Long
Dim answer As Integer
Set sht = ActiveSheet
LastRow = sht.Cells.Find("Total", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

answer = MsgBox("Are you sure you wish to print?", vbYesNo + vbQuestion, "Print Out")
If answer = vbYes Then
Range("A1:I" & LastRow).PrintOut
Else
'do nothing
End If
End Sub

Here you go

 

[Big_OT]

Works perfect. Thank you

 

[cooper645]

My pleasure.