Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help to increment default WorkBook name in code
Hello,
Members of this newsgroup have been instrumental in helping me get my code as far as Ive gone. I have what I think is my final problem that I need help with. Users will be using my workbook to collect sensor data, save a copy of the data to another file using a command button, then use another command button to clear the data from the original file so they can test another sensor. The process can be repeated as often as necessary. I finally have my €śSave Data€ť command button working how I want it to. However, the button will NOT work properly a second or subsequent time. I think I know exactly why it is behaving this way; Im not sure how to fix it. Im thinking I need some type of loop but Im not sure which is the best method (Do€¦Loop, For€¦Next, For€¦Each€¦Next, If€¦Then€¦Else) and how to implement it. In a nutshell, my €śSave Data€ť command button does the following: - open a new workbook (i.e., €śBook1€ť) - copy two worksheets from the original workbook to the new workbook - save a COPY of the new workbook to the network with a new name - close the new workbook (i.e., €śBook1€ť) without saving changes The problem is that since the original workbook is NOT closed between each sensors test, the second time the €śSave Data€ť button is clicked (for the second sensor) the new workbook created is now €śBook2€ť. I then get a run-time error because the code contains €śBook1.€ť What Im thinking I need to do is increment €śBook#€ť each time the command button is clicked. I just dont know how. Im including the code for my €śSave Data€ť command button if it will help. Thanks in advance! Chris Private Sub SaveData_Click() 'The following code creates a new workbook and copies the worksheets from the template into the new workbook. Code isn't copied. Workbooks.Add Sheets("Sheet1").Select Sheets("Sheet1").Name = "Pre-Service" Sheets("Sheet2").Select Sheets("Sheet2").Name = "Post-Service" Sheets("Pre-Service").Select Windows("SR50_Test_Data_Form_v2.xls").Activate Sheets("Pre-Service").Select ActiveSheet.Cells.Select Selection.Copy Windows("Book1").Activate ActiveSheet.Paste Sheets("Post-Service").Select Windows("SR50_Test_Data_Form_v2.xls").Activate Sheets("Post-Service").Select ActiveSheet.Cells.Select Application.CutCopyMode = False Selection.Copy Windows("Book1").Activate ActiveSheet.Paste Windows("SR50_Test_Data_Form_v2.xls").Activate Windows("Book1").Activate Application.CutCopyMode = False 'The following code saves a COPY of the new workbook to the network and renames it. Before copying, it ensures a serial number was entered. If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then MsgBox ("You must enter a serial number.") Exit Sub Else Worksheets("Post-Service").Range("D3") = UCase(Worksheets("Post-Service").Range("D3")) If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & Range("d5") & "_" & ".xls" Else If MsgBox("Are you sure the serial number doesn't begin with C?", vbYesNo) = vbYes Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & Range("d5") & "_" & ".xls" Else MsgBox ("Please fix the serial number.") End If End If End If Windows("Book1").Close Savechanges:=False End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I increment tabs by date in a workbook? | Excel Discussion (Misc queries) | |||
Automatically increment numbers in a cell when you open workbook | Excel Discussion (Misc queries) | |||
increment number by code | Excel Programming | |||
increment number by code | Excel Programming | |||
increment date code | Excel Programming |