Customizable printing of the sheets in a workbook>>>>>

[Excel_123]

Hi There
I have a workbook that comprises 11 sheets in total..and its pretty flexible that i can add more sheets into it if i want. I have a numberig system in the workbook that takes care of the numbering of the sheets. For example if no additional sheet is added, then the total sheets would be 11, that number will be incremented as long as we keep adding the sheets. Now:

Problem: I want to print two versions of the workbook, i mean i want a kind of action on one of the sheets, that ask me whether you want to priont option 1 or option2. These options are explained below:

Option 1: If i hit option 1, it should print of only first 8 sheets by adjusting the number from 11 to 8 (i don't want the last three sheets to be printed, so numbering scheme should be acording to that, the last sheet should number 8 of 8, not 8 of 11). This is the case if there is no additional sheets are added, if there are some additional sheets then, for example if two more additional sheets, so then the total number of sheets would be 13, but i only want to print it till 10, so 10th sheet would be the last sheet and the last three sheets should not be printed.

Option 2: This is a little harder than the firs one. If i hit the second option then it means i don't want to print the first 8 sheets, but i want to print the last three sheets plus the 4 sheets in the first 8 sheets should also be included in thats. For example if there is no additional sheets in the book, and i hit the second option than it should print the sheets 9 to 11 (but by changing the numbering from 9 to 11 to 1 to 3 and plus the four sheets from the first 8 sheets with the numbering system 4 of 7, 5 of 7, 6 of 7, and 7 of 7. Similarly, if additional sheets are added then they should numbered accordingly.

I hope i explained my situation clearly, All thoughts and alternatives would be appreciated. Thanx in Advance if you help me out.

Regards
Ram

 

[Excel_123]

I know, i sound pretty complicated, but there has to be a way out of it. I am trying to think abt it, but haven't got any thing yet. I beleive someone has to know abt that............thanx in advance

Regards
Ram

 

[Joe Was]

I use this code in on of my applications, I uded Form Buttons and attach code like (below) to each, I have a button for each print option. See if this helps you any?

It's Excel 2000 and above code so it contains things not in "97" like Assistant code!

Sub PrintAll()
' This button prints all the sheets.
'
If Worksheets("INFO").Range("AB6") >= 9 Then GoTo myFull Else GoTo myShort
End
myFull:
With Assistant
.On = True
.Visible = True
If Not Sounds Then Sounds = True
.Animation = msoAnimationBeginSpeaking
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Related Form").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Factor Sheet").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Input Form").Select
Range("A2").Select
With Assistant
.On = True
.Visible = True
If Not Sounds Then Sounds = True
.Animation = msoAnimationPrinting
End With
End
myShort:
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Related Form").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Factor Sheet").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Input Form").Select
Range("A2").Select
End Sub

 

[Excel_123]

Thanx for the code, but i have excel97, what should i do to make it work for both 97 and 2000. Secondly, i have already a numbering system in my workbook, so would it take care of that numbering system or not or do i have to modify it in order to make it working. Thanx for your help

Regards
Ram

 

[Joe Was]

Sub PrintAll()
' This button prints all the sheets.
'
If Worksheets("INFO").Range("AB6") >= 9 Then GoTo myFull Else GoTo myShort
End
myFull:

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Related Form").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Factor Sheet").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Input Form").Select
Range("A2").Select
End
myShort:
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Related Form").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Factor Sheet").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Input Form").Select
Range("A2").Select
End Sub

This is the same code with the assistant code removed. Use this as your model.

Note: From the toolbar set the page print length and page setup for each page. Excel will remember each pages print setup, the above code is fast, if you use a recorded macro for each page setting each property, the code will run very slow. JSW

 

[Excel_123]

Hi there
It looks to me what you are trying to do is trying to print fixed number of sheets in both conditions. But my case is not fixed, i have to print all the first 8 sheets (Option 1) provided that no additional sheet is added, and by readjusting their numbering scheme from 1 to 8, which is initially set from 1 to 11 just like page 1 of 11, page 2 of 11,.......page 11 of 11(the i have my own functions for keeping track of the numbering system). Similarly if i any additional sheets are added then i have to print those as well. The way my sheets are set up is given below:

1)sheet A
2)sheet B
3)sheet C
4)sheet D
5)sheet E
6)sheet F
7)sheet G
sheet H
9)sheet I
10)sheet J
11)sheet K

Now sheets (C,D,E,F) are the ones which can be added into the book. I have the template sheets for these four sheets that can be added into the workbook after clicking a button on each of their parent sheets. Now as u can see if i want to add a new sheet based on sheet "C", i will click that button on parent sheet "C" and a new sheet will be added right after sheet "C", with the name Sheet C(2).The numbering scheme should be automatically re-adjusted, now the total would be 12. So for
OPTION 1: i want to print the sheets till "Sheet H". and FOR:

OPTION2: i want to print sheets from Sheet "I" to sheet "K" plus sheets "C" to Sheets "F" at the end of sheets "K", with the new fresh numbering scheme i-e from 1 to what the total sheets would be.

I hope now i presented my stuff clearly. Please guide me through this, i will really appreciate your time. Thanx in Advance.

Regards
Ram

 

[Excel_123]

Let me put in this way now. I want to assign a macro to a button on one of the sheets, which should do the following things for me:

1)Ask me which sheets you want to print(kind of action that prompts me to select the desired sheets)
2) Give a numbering system to the selected sheets(for example if i select four for print, it should number them sheet 1of 4, sheet 2 of 4......sheet 4 of 4)
3)Print the sheets for me.

I am really kind of stuck in that, please help me in this, many many thanx for this help.
Regards
Ram

 

[Excel_123]

Hi There
I am once again bringing this issue up....please guide me through this...i am really in deep problems. Some one out there can help me in that stuff. I really appreciate your time and favor.

Ram

 

[Excel_123]

I can't believe this nobody answered me for the last two days. I am still shaking in that. Please help me in it. Thanx a lot

Ram

 

[Excel_123]

Hi there
Some one got to know this...i know i am pretty close. The only problem i am having right now is how to automatically number the selected sheets for printing.

Regards
Ram