**HELP NEEDED*** VBA / Macro For Print with Command Button

[iouu2]

Took basics in Undergrad but don't even remember how to create a command button any more .

What I need is a command button that can make it easy to print various tabs within the excel workbook and make them print ready (meaning click of a button and you can select which tabs you want to print) and make sure the view for them are print to one page.

The tab in which the command button will sit can be called "Header".
The other tabs in the workbook will be Tab 1, Tab 2, Tab 3, etc. etc..

Thanks!

 

[chemebabak]

1. The VBA command for printing is Sheets(tab_string).PrintOut; where tab_string is "Tab 1", "Tab 2", etc.
2. The command button should be an ActiveX control.
Step 1.
From the Developer menu, click "Design Mode" and click insert ActiveX control, command button. Right click on the button, select Properties menu and rename the button, "PrintTabs".
Step 2.
Add radio buttons from ActiveX controls. Right click on the button, select Properties menu and rename the buttons Tab1, Tab2, etc. Mark the Value field of Tab1 button as true. As you click on any other radio button, the value will cycle through the different buttons.
Step 3.
Double click on the command button and add the code,
Private Sub PrintTabs_Click()
If Tab1.Value = True Then
Sheets("Tab 1").PrintOut
ElseIf Tab2.Value = True Then
Sheets("Tab 2").PrintOut
End If
End Sub

Done.

 

[iouu2]

This is a great start but here is what I am thinking...

This excel document may have up to 20 - 30 tabs, so having radio buttons might be too excessive. All the tabs will have to be printed out, starting at Tab1 - how every many tabs are in this sheet.

And by print ready, is there a way to make it so that all the tabs are printed to 1 page?

How can I make the below code applicable for your code you mentioned above?

Private Sub Print_Macro()


ActiveWindow.View = xlPageBreakPreview
ActiveSheet.ResetAllPageBreaks
With ActiveSheet.PageSetup
.FooterMargin = Application.InchesToPoints(0.45)
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftFooter = "&""EYInterstate Light""&10 Monthly Financial update" & Chr(10)
.RightFooter = "Page: &P of &N "
.CenterFooter = "&R"
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
'.FirstPageNumber = xlAutomatic
'.Order = xlDownThenOver
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False

End With
ActiveWindow.View = xlNormalView
Range("A1").Select
End Sub

 

[chemebabak]

So then from your header tab mark in cell A1 the tab number and click on the command button...

Private Sub Print_Macro()

'cell A1 contains the number of the tab
tab_name = "Tab" & Range("A1").value
Sheets(tab_name).Select

ActiveWindow.View = xlPageBreakPreview
ActiveSheet.ResetAllPageBreaks
.
.
.
.