Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box value saves wrong WorkBook file & AUTO Sheet Numbering (1,2,3,4...)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box value saves wrong WorkBook file & AUTO Sheet Numbering (1,2,3,4...)
Got this bit right now:
sName = ActiveWorkbook.Worksheets.Count ' Will name each preceding sheet value+1 Corey.... "Corey" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel saves to wrong folder | Excel Discussion (Misc queries) | |||
execute auto invoice numbering in excel file | Excel Discussion (Misc queries) | |||
Auto numbering in each sheet of a workbook | Excel Discussion (Misc queries) | |||
auto numbering an exel work book sheet everytime it opens or print | Excel Discussion (Misc queries) | |||
AUTO PAGE NUMBERING DIFFERENTLY FOR A WORKBOOK | Excel Worksheet Functions |