return the number of sheets in a workbook

[rogernz]

I want to be able to return the number of sheets in a workbook during a macro operation. My sheet uses macros to insert new sheets from templates, each sheet represents a seqential job number from a list on the original first sheet. Hence I want to use the count function to count number of sheets and use it in a lookup function as the worksheet is inserted. I can manipulate the basic part of the function into my existing macro - just don't know the exact wording of the code to use for this.

Roger W

 

[erik.van.geit]

The problem I have is I am running out of space for more buttons

consider using a listbox
code to feed the box

Dim sh As Worksheet
ListBox1.Clear
For Each sh In Sheets
    ListBox1.AddItem (sh.Name)
Next sh

samplecode when listbox is clicked

Private Sub ListBox1_Click()
    Sheets(ListBox1.Text).Select
End Sub

best regards,
Erik

 

[rogernz]

A sample of the job register attached will explain after

  A     B        C    D                     E            F            G            H                 
1 DATE  JOB      CODE CLIENT                MAKE         MODEL        REGO / FRAME VIN / SERIAL NO.  
2 06/09 0609-037 4520 Fruehauf Trailers Ltd Freightliner Argosy 8x4   -            1FVMAWBG56LV90798 
3 06/09 0609-038 3010 Fruehauf Trailers Ltd Fruehauf     FBC R4 14.0  -            7A9FBC4SD6F007010 
4 06/09 0609-039 4550 Fruehauf Trailers Ltd Fruehauf     FBC R4 14.0  -            7A9FBC4SD6F007010 
5 06/09 0609-040 4510 Fruehauf Trailers Ltd Fruehauf     RBC S2S2 9.1 -            7A9RBC4SX6F007012 
6 06/09 0609-041 3010 Fruehauf Trailers Ltd Fruehauf     RBC S2S2 9.1 -            7A9RBC4SX6F007012 
7 06/09 0609-042 4510 Fruehauf Trailers Ltd Fruehauf     RBC S2S2 9.1 -            7A9RBC4SX6F007011 
8 06/09 0609-043 3010 Fruehauf Trailers Ltd Fruehauf     RBC S2S2 9.1 -            7A9RBC4SX6F007011 

sample sheet
[Table-It] version 06 by Erik Van Geit

 

[rogernz]

This is similar to the register in use. In this sample as you can see, the jobs are all for the same customer, however different vehicles are involved. In this case only two certs per machine (only 1 for the first). To run my job setup macro at the moment, you select the cell containing the job number for the first job, and push the macro run button, which goes through the process of creating a new header sheet from a template, and from there you can select any number of different certificates. This macro only works for the first line of the job set up, so if there are two jobs on the same machine, the second (small) amount of information has to be manually entered. I want to be able to check if the vin or serial number in the following rows is the same as the current one, and if that is the case, then the macro will also copy that information across. I can probably work out how to make it work, all I need is a push in the right direction, ie how to compare if the values are the same and how to stop / start or ignore a section of the macro if the result is negative etc. (up to a maximum of 4 jobs per header sheet).

 

[rogernz]

With regards to the listbox, the macro that is run does more than just install the sheets, it also contains the info to fill it in, hence I would like to use the macro buttons in a userform - unless the listbox also does this. I have a userform all ready to go...

 

[erik.van.geit]

tired as I am now, this is a try to help, my last "job" before sleeping

not 100% clear of what you want to do
if you want to copy rows with the same contents in column H, you can use filter

a similar question is here
http://www.mrexcel.com/board2/viewtopic.php?t=232253
this code will create a new worksheet when the value in A changes, you can edit the code to check for changes in column H
(I think - not tested but almost sure - you can replace every "A" with "H")
run the code to see if it's similar to what you want

kind regards,
Erik

 

[erik.van.geit]

With regards to the listbox, the macro that is run does more than just install the sheets, it also contains the info to fill it in, hence I would like to use the macro buttons in a userform - unless the listbox also does this. I have a userform all ready to go...

my code was only a sample to show how the value of the listbvox can be retrieved

do you have a different macro for each sheet?
perhaps those macros are very similar
if yes, adding some variables could make them run
if not similar you could use

Select Case ListBox.Text
Case "sheetname1": macro1
Case "sheetname2": macro2


End Select

so this will be enough for some hours to experiment, I guess
bye !
Erik

 

[PaddyD]

userform examples:

http://www.theofficeexperts.com/downloads.htm#ExcelDownloads