I have built some code asking the user how many worksheets he would like to
add to a workbook with 1 visible worksheet. It is attached to a button and I
now need to put that code in a property such as "On Open" (I'm better with
Access), so when the worksheet opens, the first question is "How many...".
I've read some of the other posts, but can't find the property/ies they refer
to.

So far, I have:
------------------------------------
Private Sub Worksheet_Open()
Dim NumSheets, x, y

y = Worksheets.Count
NumSheets = InputBox("How many sheets to add?")
If NumSheets = Empty Then
End
Else
For x = 1 To NumSheets
Sheets("1").Select
Sheets("1").Copy After:=Sheets(1)
y = y + 1
ActiveSheet.Name = y - 2
Next x
End If
End Sub
------------------------------------

The reason I have "y - 2" above is because I also have 2 hidden worksheets
(used for data validation). If not "Private Sub Worksheet_Open()", then
where?

Thanks in advance for any help.

go into the vbe and in the project explorer pick you project. Under you
project click on the ThisWorkbook entry and select view code. Place your
code there.

--
Regards,
Tom Ogilvy


"ReportSmith" wrote:

I have built some code asking the user how many worksheets he would like to
add to a workbook with 1 visible worksheet. It is attached to a button and I
now need to put that code in a property such as "On Open" (I'm better with
Access), so when the worksheet opens, the first question is "How many...".
I've read some of the other posts, but can't find the property/ies they refer
to.

So far, I have:
------------------------------------
Private Sub Worksheet_Open()
Dim NumSheets, x, y

y = Worksheets.Count
NumSheets = InputBox("How many sheets to add?")
If NumSheets = Empty Then
End
Else
For x = 1 To NumSheets
Sheets("1").Select
Sheets("1").Copy After:=Sheets(1)
y = y + 1
ActiveSheet.Name = y - 2
Next x
End If
End Sub
------------------------------------

The reason I have "y - 2" above is because I also have 2 hidden worksheets
(used for data validation). If not "Private Sub Worksheet_Open()", then
where?

Thanks in advance for any help.

Thanks for the tip. I copied the code into the project section, but the file
won't open correctly...it locks up after it opens and I can't do anything.

"Tom Ogilvy" wrote:

go into the vbe and in the project explorer pick you project. Under you
project click on the ThisWorkbook entry and select view code. Place your
code there.

--
Regards,
Tom Ogilvy


"ReportSmith" wrote:

I have built some code asking the user how many worksheets he would like to
add to a workbook with 1 visible worksheet. It is attached to a button and I
now need to put that code in a property such as "On Open" (I'm better with
Access), so when the worksheet opens, the first question is "How many...".
I've read some of the other posts, but can't find the property/ies they refer
to.

So far, I have:
------------------------------------
Private Sub Worksheet_Open()
Dim NumSheets, x, y

y = Worksheets.Count
NumSheets = InputBox("How many sheets to add?")
If NumSheets = Empty Then
End
Else
For x = 1 To NumSheets
Sheets("1").Select
Sheets("1").Copy After:=Sheets(1)
y = y + 1
ActiveSheet.Name = y - 2
Next x
End If
End Sub
------------------------------------

The reason I have "y - 2" above is because I also have 2 hidden worksheets
(used for data validation). If not "Private Sub Worksheet_Open()", then
where?

Thanks in advance for any help.

Sorry...I got it. I had accidentally placed the code in the General
Declarations section. But the locking up issue still exists after all of the
sheets have been copied. In other words, if I enter "9", 9 extra sheets
appear and then I'm locked up and can't work within any of the sheets.

"Tom Ogilvy" wrote:

go into the vbe and in the project explorer pick you project. Under you
project click on the ThisWorkbook entry and select view code. Place your
code there.

--
Regards,
Tom Ogilvy


"ReportSmith" wrote:

I have built some code asking the user how many worksheets he would like to
add to a workbook with 1 visible worksheet. It is attached to a button and I
now need to put that code in a property such as "On Open" (I'm better with
Access), so when the worksheet opens, the first question is "How many...".
I've read some of the other posts, but can't find the property/ies they refer
to.

So far, I have:
------------------------------------
Private Sub Worksheet_Open()
Dim NumSheets, x, y

y = Worksheets.Count
NumSheets = InputBox("How many sheets to add?")
If NumSheets = Empty Then
End
Else
For x = 1 To NumSheets
Sheets("1").Select
Sheets("1").Copy After:=Sheets(1)
y = y + 1
ActiveSheet.Name = y - 2
Next x
End If
End Sub
------------------------------------

The reason I have "y - 2" above is because I also have 2 hidden worksheets
(used for data validation). If not "Private Sub Worksheet_Open()", then
where?

Thanks in advance for any help.

Test it in a new workbook (with two hidden sheets) and a blank sheet named "1"

If it works there, then it must have something to do with the data on the
sheet being copied or something to do with recalculating.

--
Regards,
Tom Ogivly



"ReportSmith" wrote:

Sorry...I got it. I had accidentally placed the code in the General
Declarations section. But the locking up issue still exists after all of the
sheets have been copied. In other words, if I enter "9", 9 extra sheets
appear and then I'm locked up and can't work within any of the sheets.

"Tom Ogilvy" wrote:

go into the vbe and in the project explorer pick you project. Under you
project click on the ThisWorkbook entry and select view code. Place your
code there.

--
Regards,
Tom Ogilvy


"ReportSmith" wrote:

I have built some code asking the user how many worksheets he would like to
add to a workbook with 1 visible worksheet. It is attached to a button and I
now need to put that code in a property such as "On Open" (I'm better with
Access), so when the worksheet opens, the first question is "How many...".
I've read some of the other posts, but can't find the property/ies they refer
to.

So far, I have:
------------------------------------
Private Sub Worksheet_Open()
Dim NumSheets, x, y

y = Worksheets.Count
NumSheets = InputBox("How many sheets to add?")
If NumSheets = Empty Then
End
Else
For x = 1 To NumSheets
Sheets("1").Select
Sheets("1").Copy After:=Sheets(1)
y = y + 1
ActiveSheet.Name = y - 2
Next x
End If
End Sub
------------------------------------

The reason I have "y - 2" above is because I also have 2 hidden worksheets
(used for data validation). If not "Private Sub Worksheet_Open()", then
where?

Thanks in advance for any help.

I have a feeling you're right. The validation rules look up values on the 2
hidden sheets. The "locking" goes away after a bit (5-10 seconds?) and I am
able to work with the sheets. This makes me think that some background
calculation is taking place (the validation lookups - there are 2 of them
with one depending on the other).
Is there any way to speed this up other than copying the sheet and pasting
it into another workbook as values, without the validation lookup(s)?

Thanks again.

"Tom Ogilvy" wrote:

Test it in a new workbook (with two hidden sheets) and a blank sheet named "1"

If it works there, then it must have something to do with the data on the
sheet being copied or something to do with recalculating.

--
Regards,
Tom Ogivly



"ReportSmith" wrote:

Sorry...I got it. I had accidentally placed the code in the General
Declarations section. But the locking up issue still exists after all of the
sheets have been copied. In other words, if I enter "9", 9 extra sheets
appear and then I'm locked up and can't work within any of the sheets.

"Tom Ogilvy" wrote:

go into the vbe and in the project explorer pick you project. Under you
project click on the ThisWorkbook entry and select view code. Place your
code there.

--
Regards,
Tom Ogilvy


"ReportSmith" wrote:

I have built some code asking the user how many worksheets he would like to
add to a workbook with 1 visible worksheet. It is attached to a button and I
now need to put that code in a property such as "On Open" (I'm better with
Access), so when the worksheet opens, the first question is "How many...".
I've read some of the other posts, but can't find the property/ies they refer
to.

So far, I have:
------------------------------------
Private Sub Worksheet_Open()
Dim NumSheets, x, y

y = Worksheets.Count
NumSheets = InputBox("How many sheets to add?")
If NumSheets = Empty Then
End
Else
For x = 1 To NumSheets
Sheets("1").Select
Sheets("1").Copy After:=Sheets(1)
y = y + 1
ActiveSheet.Name = y - 2
Next x
End If
End Sub
------------------------------------

The reason I have "y - 2" above is because I also have 2 hidden worksheets
(used for data validation). If not "Private Sub Worksheet_Open()", then
where?

Thanks in advance for any help.

application.Calculation = xlManual
Application.EnableEvents = False
Application.ScreenUpdating = False

' code to copy the sheets

Application.EnableEvents = True
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

--
Regards,
Tom Ogilvy


"ReportSmith" wrote in message
...
I have a feeling you're right. The validation rules look up values on the
2
hidden sheets. The "locking" goes away after a bit (5-10 seconds?) and I
am
able to work with the sheets. This makes me think that some background
calculation is taking place (the validation lookups - there are 2 of them
with one depending on the other).
Is there any way to speed this up other than copying the sheet and pasting
it into another workbook as values, without the validation lookup(s)?

Thanks again.

"Tom Ogilvy" wrote:

Test it in a new workbook (with two hidden sheets) and a blank sheet
named "1"

If it works there, then it must have something to do with the data on the
sheet being copied or something to do with recalculating.

--
Regards,
Tom Ogivly



"ReportSmith" wrote:

Sorry...I got it. I had accidentally placed the code in the General
Declarations section. But the locking up issue still exists after all
of the
sheets have been copied. In other words, if I enter "9", 9 extra
sheets
appear and then I'm locked up and can't work within any of the sheets.

"Tom Ogilvy" wrote:

go into the vbe and in the project explorer pick you project. Under
you
project click on the ThisWorkbook entry and select view code. Place
your
code there.

--
Regards,
Tom Ogilvy


"ReportSmith" wrote:

I have built some code asking the user how many worksheets he would
like to
add to a workbook with 1 visible worksheet. It is attached to a
button and I
now need to put that code in a property such as "On Open" (I'm
better with
Access), so when the worksheet opens, the first question is "How
many...".
I've read some of the other posts, but can't find the property/ies
they refer
to.

So far, I have:
------------------------------------
Private Sub Worksheet_Open()
Dim NumSheets, x, y

y = Worksheets.Count
NumSheets = InputBox("How many sheets to add?")
If NumSheets = Empty Then
End
Else
For x = 1 To NumSheets
Sheets("1").Select
Sheets("1").Copy After:=Sheets(1)
y = y + 1
ActiveSheet.Name = y - 2
Next x
End If
End Sub
------------------------------------

The reason I have "y - 2" above is because I also have 2 hidden
worksheets
(used for data validation). If not "Private Sub Worksheet_Open()",
then
where?

Thanks in advance for any help.

Should I place this code in the Workbook Open property sheet? The
workbook hangs only when it opens. When I add a sheet, everything seems ok.
If so, the calculation will only take place if the user presses <Shift+F9 -
right?

"Tom Ogilvy" wrote:

application.Calculation = xlManual
Application.EnableEvents = False
Application.ScreenUpdating = False

' code to copy the sheets

Application.EnableEvents = True
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

--
Regards,
Tom Ogilvy


"ReportSmith" wrote in message
...
I have a feeling you're right. The validation rules look up values on the
2
hidden sheets. The "locking" goes away after a bit (5-10 seconds?) and I
am
able to work with the sheets. This makes me think that some background
calculation is taking place (the validation lookups - there are 2 of them
with one depending on the other).
Is there any way to speed this up other than copying the sheet and pasting
it into another workbook as values, without the validation lookup(s)?

Thanks again.

"Tom Ogilvy" wrote:

Test it in a new workbook (with two hidden sheets) and a blank sheet
named "1"

If it works there, then it must have something to do with the data on the
sheet being copied or something to do with recalculating.

--
Regards,
Tom Ogivly



"ReportSmith" wrote:

Sorry...I got it. I had accidentally placed the code in the General
Declarations section. But the locking up issue still exists after all
of the
sheets have been copied. In other words, if I enter "9", 9 extra
sheets
appear and then I'm locked up and can't work within any of the sheets.

"Tom Ogilvy" wrote:

go into the vbe and in the project explorer pick you project. Under
you
project click on the ThisWorkbook entry and select view code. Place
your
code there.

--
Regards,
Tom Ogilvy


"ReportSmith" wrote:

I have built some code asking the user how many worksheets he would
like to
add to a workbook with 1 visible worksheet. It is attached to a
button and I
now need to put that code in a property such as "On Open" (I'm
better with
Access), so when the worksheet opens, the first question is "How
many...".
I've read some of the other posts, but can't find the property/ies
they refer
to.

So far, I have:
------------------------------------
Private Sub Worksheet_Open()
Dim NumSheets, x, y

y = Worksheets.Count
NumSheets = InputBox("How many sheets to add?")
If NumSheets = Empty Then
End
Else
For x = 1 To NumSheets
Sheets("1").Select
Sheets("1").Copy After:=Sheets(1)
y = y + 1
ActiveSheet.Name = y - 2
Next x
End If
End Sub
------------------------------------

The reason I have "y - 2" above is because I also have 2 hidden
worksheets
(used for data validation). If not "Private Sub Worksheet_Open()",
then
where?

Thanks in advance for any help.