count sheets

[spilner]

hi,ive thousands of sheets and its all not sorted.
i want to print 1000 sheets but i want to divide it to 500 500.
is there any formula can tell me the range from first sheet to which sheet is 500 sheets and so on?
within from sheet1 to 800 probably has sheet1200,1300 etc.its not sorted.
example from sheet1 to sheet 800 is 500 sheets but i dont know.
atm,i need to count sheets myself and print it.
can someone help?thanks a lot

 

[spilner]

i get runtime error 9.
where do i insert the range?

 

[spilner]

excel still go to each sheet and print it
maybe need to turn off screenupdate to = FALSE.

 

[Peter_SSs]

i get runtime error 9.

Is there more to the error message than that?
What line does the code give that error on?

where do i insert the range?

The number of sheets to print each time is given by this line

Const NumSheets As Long = 3 '<- No. of sheets to print each time

excel still go to each sheet and print it

The code does not 'go' to any sheet. It selects a group of sheets and then prints them - just like if you select a group of sheet manually and then click print. What else did you have in mind?

 

[Peter_SSs]

maybe need to turn off screenupdate to = FALSE.

You could certainly do that if you want.

 

[spilner]

i confused,is this true if i want to print range 1:50

Sub PrintGroupsOfSheets()
    Dim i As Long, j As Long, FirstShIdx As Long
    Dim Tot As Long, ThisGrp As Long
    Dim ShNames() As String
    
    Const FirstShName As String = "1" '<- First sheet to print
    Const NumSheets As Long = 50 '<- No. of sheets to print each time
    
    FirstShIdx = Sheets(FirstShName).Index
    Tot = Sheets.Count
    For i = FirstShIdx To Tot Step NumSheets
        ThisGrp = IIf(i + NumSheets > Tot, Tot - i + 1, NumSheets)
        ReDim ShNames(0 To ThisGrp - 1)
        For j = 0 To ThisGrp - 1
                ShNames(j) = Sheets(i + j).Name
        Next j
        Sheets(ShNames).PrintOut '.PrintOut
    Next i
End Sub

 

[Peter_SSs]

i confused,is this true if i want to print range 1:50

Sub PrintGroupsOfSheets()
    Dim i As Long, j As Long, FirstShIdx As Long
    Dim Tot As Long, ThisGrp As Long
    Dim ShNames() As String
    
    Const FirstShName As String = "1" '<- First sheet to print
    Const NumSheets As Long = 50 '<- No. of sheets to print each time
    
    FirstShIdx = Sheets(FirstShName).Index
    Tot = Sheets.Count
    For i = FirstShIdx To Tot Step NumSheets
        ThisGrp = IIf(i + NumSheets > Tot, Tot - i + 1, NumSheets)
        ReDim ShNames(0 To ThisGrp - 1)
        For j = 0 To ThisGrp - 1
                ShNames(j) = Sheets(i + j).Name
        Next j
        Sheets(ShNames).PrintOut '.PrintOut
    Next i
End Sub

No, that will start printing at a sheet called "1" (which may not be the first sheet in the workbook) and it will print a group of 50 sheets, then it will print another group of 50 sheets etc until it comes to the end of the worrkbook. (The final group printed could be less than 50 sheets.)

 

[spilner]

No, that will start printing at a sheet called "1" (which may not be the first sheet in the workbook) and it will print a group of 50 sheets, then it will print another group of 50 sheets etc until it comes to the end of the worrkbook. (The final group printed could be less than 50 sheets.)

i got what you mean now but it still doesnt met what i needs.im sorry.

ive thousand of sheets and im not gonna print it all.
the first code u created for me is perfect.

Sub PrintSheetRange()
    Dim i As Long
    
    Const FirstSh As String = "80"
    Const LastSh As String = "500"
    
    For i = Sheets(FirstSh).Index To Sheets(LastSh).Index
        Sheets(i).PrintOut
    Next i
End Sub

i just want this above code to print like i select a group and print it.
so,it wont go each sheet and print.

the other code that i asked is can we make excel to help us to find from what range to what range its 500 sheets.
example we create a code.when i run the code,it will come out a box and ask me to key in the first sheet,lets say sheet5.then it will automatically tell me from sheet5 to sheet300 is 500.
this mean i know from sheet5 to sheet300 there are 500.then i will use the range to print.Thanks a lot Peter for patient with me.

 

[Peter_SSs]

i just want this above code to print like i select a group and print it.
so,it wont go each sheet and print.

Try this

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> PrintSheetRange()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, ThisGrp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, StartShI <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, EndShI <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> ShNames() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> FirstShNm <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "80"<br>    <SPAN style="color:#00007F">Const</SPAN> LastShNm <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "500"<br>    <br>    StartShI = Worksheets(FirstShNm).Index<br>    EndShI = Worksheets(LastShNm).Index<br>    ThisGrp = EndShI - StartShI + 1<br>    <SPAN style="color:#00007F">ReDim</SPAN> ShNames(0 <SPAN style="color:#00007F">To</SPAN> ThisGrp - 1)<br>    <SPAN style="color:#00007F">For</SPAN> i = 0 <SPAN style="color:#00007F">To</SPAN> ThisGrp - 1<br>        ShNames(i) = Worksheets(StartShI + i).Name<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    Worksheets(ShNames).PrintPreview <SPAN style="color:#007F00">'.PrintOut</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

the other code that i asked is can we make excel to help us to find from what range to what range its 500 sheets.
example we create a code.when i run the code,it will come out a box and ask me to key in the first sheet,lets say sheet5.then it will automatically tell me from sheet5 to sheet300 is 500.
this mean i know from sheet5 to sheet300 there are 500.then i will use the range to print.

Well, I think you are likely to run into trouble with that when there are less than 500 sheets to print, but I think this is what you are asking for.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Last_Sheet_Name_In_Group()<br>    <SPAN style="color:#00007F">Dim</SPAN> FirstShNm <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> wsExists <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Idx1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, Idx2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> GroupSize <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 500<br>    <br>    FirstShNm = InputBox("Enter name of first sheet")<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    wsExists = <SPAN style="color:#00007F">Not</SPAN> Worksheets(FirstShNm) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN><br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>    <SPAN style="color:#00007F">If</SPAN> wsExists <SPAN style="color:#00007F">Then</SPAN><br>        Idx1 = Worksheets(FirstShNm).Index<br>        Idx2 = Idx1 + GroupSize - 1<br>        <SPAN style="color:#00007F">If</SPAN> Idx2 > Worksheets.Count <SPAN style="color:#00007F">Then</SPAN><br>            MsgBox GroupSize & " sheets starting with sheet '" _<br>                & FirstShNm & "' not possible"<br>        <SPAN style="color:#00007F">Else</SPAN><br>            MsgBox GroupSize & " sheets starting at sheet '" & FirstShNm _<br>                & "' ends at sheet '" & Worksheets(Idx2).Name & "'"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>        MsgBox "'" & FirstShNm & _<br>            "' is not a valid worksheet name for this workbook"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[spilner]

oh WOW,i so surprise.this both vba code works PERFECT AND FAST.
and it also tell me how much pages its goin to print in the preview.THIS IS AWESOME.
THANK YOU SO MUCH PETER.THANKS ONCE AGAIN FOR YOUR EFFORT.YOUR THE BEST MAN.

if u have time,can u look at this thread.
http://www.mrexcel.com/forum/showthread.php?t=575066

Thanks a lot