C
CB
Hello,
Members of this newsgroup have been instrumental in helping me get my code
as far as I’ve 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; I’m not sure how to fix it.
I’m thinking I need some type of loop but I’m 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
sensor’s 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 I’m thinking I need to do is
increment “Book#†each time the command button is clicked. I just don’t know
how.
I’m 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
Members of this newsgroup have been instrumental in helping me get my code
as far as I’ve 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; I’m not sure how to fix it.
I’m thinking I need some type of loop but I’m 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
sensor’s 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 I’m thinking I need to do is
increment “Book#†each time the command button is clicked. I just don’t know
how.
I’m 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