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

 

[Peter_SSs]

Not with a "formula" but with vba code, along the lines of my code in your other thread, is this what you mean?

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CountSheetRange()<br>    <SPAN style="color:#00007F">Dim</SPAN> indx1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, indx2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, Num <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> FirstSh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "80"<br>    <SPAN style="color:#00007F">Const</SPAN> LastSh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "500"<br>    <br>    indx1 = Sheets(FirstSh).Index<br>    indx2 = Sheets(LastSh).Index<br>    Num = indx2 - indx1 + 1<br>    MsgBox "Number of sheets (inclusive) from sheet '" & _<br>        FirstSh & "' to sheet '" & LastSh & "' = " & Num<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Smitty]

I'll beg to ask why you have thousands of sheets in the first place?

Just because Excel can do something doesn't mean that it should. Have you thought about a database application?

 

[Peter_SSs]

I'll beg to ask why you have thousands of sheets in the first place?

Smitty, when I saw that you had posted to this thread, I guessed that your post was along these lines. Good question though.

 

[spilner]

I dont know about database application. Can u explain to me? I dont have good knowledge in this. Is it from microsoft too?

 

[Smitty]

I dont know about database application. Can u explain to me? I dont have good knowledge in this. Is it from microsoft too?

Microsoft Access comes to mind, although Peter is much better at it than I.

Access is a companion product to Excel, and inasmuch as Excel is great for analysis, Access is a tool that allows you to store thousands, if not millions of records, then spit the data to Excel in more manageable chunks. If you have Office Professional then you have Access.

There is also an Access forum here if you need help getting started with it, and you'll find everyone there as friendly as here.

 

[Peter_SSs]

Microsoft Access comes to mind, although Peter is much better at it than I.

That must be another Peter as I don't use Access at all.

 

[spilner]

Can i convert my excel to access? Its too lot of work to do again. Does access formula same as excel? Ive much formula in my excel.

 

[spilner]

Not with a "formula" but with vba code, along the lines of my code in your other thread, is this what you mean?


Sub CountSheetRange()
Dim indx1 As Long, indx2 As Long, Num As Long

Const FirstSh As String = "80"
Const LastSh As String = "500"

indx1 = Sheets(FirstSh).Index
indx2 = Sheets(LastSh).Index
Num = indx2 - indx1 + 1
MsgBox "Number of sheets (inclusive) from sheet '" & _
FirstSh & "' to sheet '" & LastSh & "' = " & Num
End Sub

thanks,this works perfect too.

but i mean can we make excel to help us find from which range its 500 sheets.
example we create a code.i key in the first sheet,lets say sheet5.then it will automatically tell me from sheet5 to sheet300 is 500.
u got what i mean?

,for the print code.can u make the code print it without it go to each sheet and print it?
i want it done fast cause ive other range to print.got idea?

 

[Peter_SSs]

but i mean can we make excel to help us find from which range its 500 sheets.
example we create a code.i key in the first sheet,lets say sheet5.then it will automatically tell me from sheet5 to sheet300 is 500.
u got what i mean?

,for the print code.can u make the code print it without it go to each sheet and print it?
i want it done fast cause ive other range to print.got idea?

See if this is what you mean. I certainly haven't tested it with a workbook with so many sheets and I haven't tested with actually printing the pages. I suggest that you test on a much smaller workbook and with PrintPreview too. If you think that is doing what you want, then try changing the ".PrintPreview" to ".PrintOut", still in a small workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> PrintGroupsOfSheets()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, FirstShIdx <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Tot <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><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> FirstShName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "Sheet5" <SPAN style="color:#007F00">'<- First sheet to print</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> NumSheets <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 3 <SPAN style="color:#007F00">'<- No. of sheets to print each time</SPAN><br>    <br>    FirstShIdx = Sheets(FirstShName).Index<br>    Tot = Sheets.Count<br>    <SPAN style="color:#00007F">For</SPAN> i = FirstShIdx <SPAN style="color:#00007F">To</SPAN> Tot <SPAN style="color:#00007F">Step</SPAN> NumSheets<br>        ThisGrp = IIf(i + NumSheets > Tot, <SPAN style="color:#00007F">To</SPAN>t - i + 1, NumSheets)<br>        <SPAN style="color:#00007F">ReDim</SPAN> ShNames(0 <SPAN style="color:#00007F">To</SPAN> ThisGrp - 1)<br>        <SPAN style="color:#00007F">For</SPAN> j = 0 To ThisGrp - 1<br>                ShNames(j) = Sheets(i + j).Name<br>        <SPAN style="color:#00007F">Next</SPAN> j<br>        Sheets(ShNames).PrintPreview <SPAN style="color:#007F00">'.PrintOut</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>