how to make macro button to print page that has the active cell only?

i have multiple pages at the same worksheet and i need to put macro button for each page
how to make this macro print the page which has the active cell
thanks in advance.

Originally Posted by Arkadi

Hi meonly,

Try this, I have to admit I didn't test it well since my free time is limited today, but I believe this should work? You will need to adjust the sheet name in the code to the name of the sheet you are working in.

Sub meonly123()

Dim ws As Worksheet
Dim rng As Range
Dim hbr As Long
Dim vbr As Long
Dim mypage As Integer

Set rng = ActiveCell
Set ws = Worksheets("Sheet1") 'change to the sheet name you need to or use set ws = activesheet is probably better
ws.DisplayPageBreaks = True

For i = 1 To ws.HPageBreaks.Count
    If ws.HPageBreaks(i).Location.Row <= rng.Row Then hbr = i
Next i

For j = 1 To ws.VPageBreaks.Count
    If ws.VPageBreaks(j).Location.Column <= rng.Column Then vbr = j
Next j

If ws.PageSetup.Order = xlDownThenOver Then
            mypage = ((ws.HPageBreaks.Count + 1) * vbr) + hbr + 1
Else
            mypage = ((ws.VPageBreaks.Count + 1) * hbr) + vbr + 1
End If

ws.PrintOut from:=mypage, to:=mypage

End Sub

thanks for your time i am really gratefuller to you
i use active sheet instead of sheet name coz i have alot of pages
but it show error on this line :
If ws.HPageBreaks(i).Location.Row <= rng.Row Then
any idea!!!

Edit: nevermind, read post incorrectly.

Originally Posted by Arkadi

stnkynts, I assumed he wanted only a specific page within the worksheet's pages? I really overcomplicated if not, lol.

yes it is , a specific page (which has active cell)

I don't get an error with Arkadi's code, it seems to be fairly sound. Here is another way of doing the same (Arkadi's code is actually a little more encompassing but may be more than you need)

Sub Print_ActiveCell_Sheet()
Dim x As Integer

With ActiveSheet
    Do
        x = x + 1
    Loop Until x = .HPageBreaks.Count Or ActiveCell.Row < .HPageBreaks(x).Location.Row
End With

ActiveSheet.PrintOut From:=x, to:=x

End Sub

Originally Posted by stnkynts

I don't get an error with Arkadi's code, it seems to be fairly sound. Here is another way of doing the same (Arkadi's code is actually a little more encompassing but may be more than you need)

Sub Print_ActiveCell_Sheet()
Dim x As Integer

With ActiveSheet
    Do
        x = x + 1
    Loop Until x = .HPageBreaks.Count Or ActiveCell.Row < .HPageBreaks(x).Location.Row
End With

ActiveSheet.PrintOut From:=x, to:=x

End Sub

this good is working great but it doesn't work on last page (that has the last page break line) its print the upper page instead of the current page
i wonder if u could edited
I really appreciate everything you've done to help me

Originally Posted by Arkadi

Thanks stnkynts
I could not figure out the reason for the error, I don't get it either when I try. I did not fully test, but did run the macro a few times without error. That said, I don't think I tried it when the sheet is completely empty...

sorry your code now working very well without any inefficient ,its my mistake i didnt setup page brakes
thank you very much for helping me and wish you all the best