Create separate print button for each worksheet in Excel

[Qazi Sharifullah]

I HAVE DIFFERENT WORKSHEET, AND I WANT TO CREATE PRINT BUTTON IN EACH WORKSHEET. THANKS IN ADVANCE

 

[Qazi Sharifullah]

I HAVE THREE WORKSHEET S1, S2 AND S3, AND I WANT PRINT S1 ON BUTTON ONE, S2 ON BUTTON TWO, AND S3 ON BUTTON THREE. CAN ANY ONE HELP ME ABOUT THIS. THANK IN ADVANCE.

 

[frabulator]

I believe what you are looking for is the PrintOut command.

Sub PrintTest()
Sheets("S1").PrintOut
End Sub

 

[John_w]

I HAVE DIFFERENT WORKSHEET, AND I WANT TO CREATE PRINT BUTTON IN EACH WORKSHEET. THANKS IN ADVANCE

Run this top macro. It adds a button to cell F2 on each worksheet and assigns the Print_Sheet macro to print that sheet when clicked.

Public Sub Add_Print_Button_To_All_Worksheets()

    Dim ws As Worksheet
    Dim printButton As Button
    
    For Each ws In ThisWorkbook.Worksheets
        Set printButton = ws.Buttons.Add(ws.Range("F2").Left, ws.Range("F2").Top, 80, 20)
        printButton.Name = "Print sheet"
        printButton.Caption = "Print " & ws.Name
        printButton.OnAction = "Print_Sheet"
    Next
    
End Sub


Public Sub Print_Sheet()
    ActiveSheet.PrintOut Copies:=1
End Sub

 

[Qazi Sharifullah]

thanks for your kind reply, but sir in each worksheet there is diffrent ranges e.g starting from A2 to M2, and it print only the range where there is text/values diplayed by formula not the formulas. thanks in advance

 

[John_w]

I don't understand your last reply, but maybe change Print_Sheet to:

Public Sub Print_Sheet()
    ActiveSheet.UsedRange.PrintOut Copies:=1
End Sub

 

[Qazi Sharifullah]

I HAVE THREE WORKSHEET S1, S2 AND S3, AND I WANT PRINT S1 ON BUTTON ONE, S2 ON BUTTON TWO, AND S3 ON BUTTON THREE. CAN ANY ONE HELP ME ABOUT THIS. THANK IN ADVANCE.

sir here the report may vary according to the selection in the drop box ............. therefore i want to print only report not extra blank rows i.e. here i want to print it from A2 TO J14 ONLY. THANKS

 

[John_w]

Thanks for clarifying. Replace the Print_Sheet code with:

Public Sub Print_Sheet()
    Dim lastRow As Long
    With ActiveSheet
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("A2:J" & lastRow).PrintOut Copies:=1
    End With
End Sub