Input Box value saves wrong WorkBook file & AUTO Sheet Numbering (1,2,3,4...)

C

Corey

Thanks 1st to all that have assisted me with these codes thus far.

RE : Below Code/s.
I have a macro (macro11) that when a designated number of sheets is reached,
a box states this and prompts a value to SAVE the NEW wb as "???"

1). If the amount of sheets IS reached(macro11) it runs a macro (macro20),
it RENAMES the WorkBook and asks for a New File Name for a COPY of the
WorkBook. {1/2 way to what i need.}
What i want it(macro20) to do is:
Let the user know the sheet limit is reached(does this in Input Box),
CREATE a COPY of the First 2 Sheets ONLY of the WorkBook(Currently all
sheets COPIED), Ask for a Value to Save the NEW Workbook COPY as(Currently
it saves the OLD wb as this Value, then asks for the New WorkBook Name),
then Close off the OLD WorkBook and Run macro11, to create a New WorkSheet
in the NEW COPY.


2). If NOT reached(macro11) it runs a maco(macro2) which Creates a New
WorkSheet in the current WorkBook and prompts for a Job Number and pastes it
to a Cell[V3].
I have had a change of thinking and require the New WorkSheet to be AUTO
numbered (1,2,3,4....).
Therefore if the preceding Sheet was 2, then the New Sheet to be named 3,
and so on....
But i still want current value entered for the Job Number and it placed into
the [V3] cell.


The actual code:

Sub Macro11()
' This macro runs (1) macro if number of sheets is = to (x), or if < (x)
then runs another macro
With ThisWorkbook
If .Worksheets.Count = 3 Then ' <===================== Runs macro20 if
sheets count = 3
Call Macro20
Exit Sub
End If
If .Worksheets.Count < 3 Then ' <===================== Runs macor2 if sheets
are less than 3
Call Macro2
End If
End With
End Sub

Sub Macro20()
' This macro prompts you that MAX sheets is reached and to Name the New Copy
(sheet1,sheet2)
Range("A4").Select ' <=========================== Not sure what this did,
if anything usefull now....
Selection.ClearContents ' <====================== This either....
res = InputBox("MAXIMUM File SIZE REACHED, What do you want
to NAME the NEW file ? ", "Company Name...")
If res = "" Then Exit Sub
ThisWorkbook.SaveAs res ' <===================== Want this to SAVE the
NEW WorkBook Not RENAME the existing one
' <========================================= Step to CLOSE off OLD WorekBook
here
' <========================================= Also need a step here to then
Delete ALL Sheets except for first 2 Sheets in NEW COPY.
Application.Dialogs(xlDialogSaveAs).Show ' <=========== Not Sure if this
would be needed then???
ActiveWindow.DisplayWorkbookTabs = True
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = False
Call Macro2 ' <=============================== Then to run Macro, to create
a New Sheet in the New WorkBook....
End With
End Sub

Sub Macro2()
' This macro prompts for a Job number
Dim sh As Worksheet
Dim msg As String, sName As String
msg = "Enter the Job No...."
Do
sName = InputBox(msg) ' <=======================Want to have New Sheet
named(preceding sheet name+1) (((Maybe WorkSheets.Count + 1))) ???
If sName = "" Then Exit Sub
On Error Resume Next
Set sh = Worksheets(sName)
On Error GoTo 0
msg = "ST Job Number has been used, try again: "
Loop While Not sh Is Nothing
With ActiveWorkbook
.Worksheets("1").Copy After:=.Worksheets(.Worksheets.Count)
End With
ActiveSheet.Name = sName ' <================== (1,2,3,4,5.....) not the
Input Box Value
[V3].Select ' <============================== Cell where Input Box value is
placed
ActiveCell.Value = sName ' <==================== Value to be placed in
Selected Cell [V3]
End Sub


Any help appreciated

Regards

Corey
 
C

Corey

Got this bit right now:

sName = ActiveWorkbook.Worksheets.Count ' Will name each preceding sheet
value+1


Corey....

Corey said:
Thanks 1st to all that have assisted me with these codes thus far.

RE : Below Code/s.
I have a macro (macro11) that when a designated number of sheets is
reached, a box states this and prompts a value to SAVE the NEW wb as "???"

1). If the amount of sheets IS reached(macro11) it runs a macro (macro20),
it RENAMES the WorkBook and asks for a New File Name for a COPY of the
WorkBook. {1/2 way to what i need.}
What i want it(macro20) to do is:
Let the user know the sheet limit is reached(does this in Input Box),
CREATE a COPY of the First 2 Sheets ONLY of the WorkBook(Currently all
sheets COPIED), Ask for a Value to Save the NEW Workbook COPY as(Currently
it saves the OLD wb as this Value, then asks for the New WorkBook Name),
then Close off the OLD WorkBook and Run macro11, to create a New WorkSheet
in the NEW COPY.


2). If NOT reached(macro11) it runs a maco(macro2) which Creates a New
WorkSheet in the current WorkBook and prompts for a Job Number and pastes
it to a Cell[V3].
I have had a change of thinking and require the New WorkSheet to be AUTO
numbered (1,2,3,4....).
Therefore if the preceding Sheet was 2, then the New Sheet to be named 3,
and so on....
But i still want current value entered for the Job Number and it placed
into the [V3] cell.


The actual code:

Sub Macro11()
' This macro runs (1) macro if number of sheets is = to (x), or if < (x)
then runs another macro
With ThisWorkbook
If .Worksheets.Count = 3 Then ' <===================== Runs macro20 if
sheets count = 3
Call Macro20
Exit Sub
End If
If .Worksheets.Count < 3 Then ' <===================== Runs macor2 if
sheets are less than 3
Call Macro2
End If
End With
End Sub

Sub Macro20()
' This macro prompts you that MAX sheets is reached and to Name the New
Copy (sheet1,sheet2)
Range("A4").Select ' <=========================== Not sure what this did,
if anything usefull now....
Selection.ClearContents ' <====================== This either....
res = InputBox("MAXIMUM File SIZE REACHED, What do you
want to NAME the NEW file ? ", "Company Name...")
If res = "" Then Exit Sub
ThisWorkbook.SaveAs res ' <===================== Want this to SAVE the
NEW WorkBook Not RENAME the existing one
' <========================================= Step to CLOSE off OLD
WorekBook here
' <========================================= Also need a step here to then
Delete ALL Sheets except for first 2 Sheets in NEW COPY.
Application.Dialogs(xlDialogSaveAs).Show ' <=========== Not Sure if this
would be needed then???
ActiveWindow.DisplayWorkbookTabs = True
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = False
Call Macro2 ' <=============================== Then to run Macro, to
create a New Sheet in the New WorkBook....
End With
End Sub
sName = ActiveWorkbook.Worksheets.Count ' <===== This is GOOD now.....
Sub Macro2()
' This macro prompts for a Job number
Dim sh As Worksheet
Dim msg As String, sName As String
msg = "Enter the Job No...."
Do
If sName = "" Then Exit Sub
On Error Resume Next
Set sh = Worksheets(sName)
On Error GoTo 0
msg = "ST Job Number has been used, try again: "
Loop While Not sh Is Nothing
With ActiveWorkbook
.Worksheets("1").Copy After:=.Worksheets(.Worksheets.Count)
End With
ActiveSheet.Name = sName ' <================== (1,2,3,4,5.....) not the
Input Box Value
[V3].Select ' <============================== Cell where Input Box value
is placed
ActiveCell.Value = sName ' <==================== Value to be placed in
Selected Cell [V3]
End Sub


Any help appreciated

Regards

Corey
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

msg Box Value to be Saved File Name ?? 5
Create new worksheet and use a number as its name.. 1
Name the range AFTER copied to new sheet 6
Help with Index formula 2
rename a copied sheet 3
Copy cell value from one worksheet to matching worksheet in another workbook 4
Sheet copy 2
Copy worksheet, trying to paste to new workbook error assistance. 1

Top