return the number of sheets in a workbook

rogernz

Board Regular
Joined
Jul 21, 2004
Messages
93
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

rogernz

Board Regular
Joined
Jul 21, 2004
Messages
93
ok, got that bit, but how do I get it to return on the sheet? Do I need to return it in the sheet etc? Each time I insert the next sheet I don't want the previous count altered for the last sheet. I can accomplish this by returning the value a cell then copying and pasting the value, however if there is a better way I am open to suggestions.
 

rogernz

Board Regular
Joined
Jul 21, 2004
Messages
93
This is what I have so far, what is missing from the blank area?

Sub worksheet_count()
ActiveSheet.Select
Range("I21").Select
ActiveWorkbook.Sheets.Count

Selection.Copy
Range("I21").Select
Selection.PasteSpecial Paste:=xlValues, OPERATION:=xlNone, SKIPBLANKS:= _
False, Transpose:=False
End Sub
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

do you mean you want to store the sheetscount at the start of the macro and then use it again later on during the same "run" ?
then store it in a variable

Code:
Option Explicit

Sub test()
Dim shCnt as Integer

shCnt = Sheets.Count
'do some operations
MsgBox shCnt
'as you saw the value is still "alive"
End Sub
(typed off-hand typos possible)

best regards,
Erik
 

rogernz

Board Regular
Joined
Jul 21, 2004
Messages
93
'....continuing from the rest of the existing macro, this did the trick. The job named ranges already existed, so all I had to do was figure out how to integrate the variable to the worksheet. Thanks Eric, much apprecated.

Range("I4").Select
ActiveCell.FormulaR1C1 = "=Job1"
Range("I5").Select
ActiveCell.FormulaR1C1 = "=Job2"
Range("I6").Select
ActiveCell.FormulaR1C1 = "=Job3"
Range("I7").Select
ActiveCell.FormulaR1C1 = "=Job4"
Dim shCnt As Integer
shCnt = Sheets.Count
ActiveSheet.Select
Range("I3") = shCnt
Range("F6").Select
ActiveCell.FormulaR1C1 = "=INDEX(R[-2]C[3]:R[1]C[3],R[-2]C[3])"
'... macro continues
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

suggestion
Code:
Range("I4") = "=Job1"
Range("I5") = "=Job2"
Range("I6") = "=Job3"
Range("I7") = "=Job4"
Dim shCnt As Integer
shCnt = Sheets.Count
ActiveSheet.Select
Range("I3") = shCnt
Range("F6").FormulaR1C1 = "=INDEX(R[-2]C[3]:R[1]C[3],R[-2]C[3])"
or if you have more of those cells to fill with "jobs"
Code:
Dim i As Long
    For i = 1 To 4
    Range("I" & i + 3) = "=Job" & i
    Next i
 

rogernz

Board Regular
Joined
Jul 21, 2004
Messages
93
thanks, actually that will be usefull - can remove half text in my maros with that!
I have another problem or two that I am working on now that is when there are multiple jobs to set up from the job register (which is what this macro does) I want information from the following lines in the register to come through to the header (1st) page. However I need the macro to check the following lines (in the register) to confirm that the vehicle identication number is the same, otherwise it will copy information not relevant to the particular vehicle.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

with this kinda abstract explanations, I'm very bad when it comes to understand what you mean
an example would help a lot
tools:
Colo's htmlmaker: see bottom page
Table-It: see my signature

kind regards,
Erik
 

rogernz

Board Regular
Joined
Jul 21, 2004
Messages
93
thanks, that previous tip makes the macro for sheet set up run much faster! The screen used to blink and took a few seconds to run, now it is instant! Short question - I am just beginning to experiment with userforms. I have a series of "buttons" on the header sheet which determine which sheets are installed. The problem I have is I am running out of space for more buttons. Hence I want to run a userform with additional selection buttons. I have got far enough that I can get the userform to display when the button is pushed on the header page - however I cannot get the macros to then activate on the userform, and I want the user form to go away once the button is pushed. Userform macros attached
Private Sub CommandButton1_Click()
section.OnAction = "'pds macros2.xls'!Chassis_CLICK"
End Sub

Private Sub CommandButton2_Click()
OnAction = "'pds macros2.xls'!Chassis_Repair_CLICK"
End Sub

Private Sub CommandButton3_Click()
OnAction = "'pds macros2.xls'!Chassis_Rust_Repair_CLICK"
End Sub
 

Forum statistics

Threads
1,137,302
Messages
5,680,710
Members
419,929
Latest member
Atlas Quinn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top