|
Subscript Out of Range Error
The workbooks and macros used are being created in Excel 2007, but saved in the
97-2003 format. I have a macro that the boss will use to enter employees and place the names, date added, and count (sequential) into a worksheet. It loops in case there is more than one name to be added. NOTE: The boss will be in workbook(QA Master.xls) Once the boss is through entering names, the macro will then loop and create a workbook, copying the Workbook("QA Template.xls") and renaming it with the employee name that was entered. I don't know why, but I am getting the following error on the line that copies and names the new workbook: Runtime Error 9 Subscript Out of Range The macro is below: Option Base 1 'Add Employee to list and create workbook for employee Private Sub CommandButton2_Click() Dim Employee(1 To 200), Msg, Title As String 'Array may not reach 200 entries Dim Config, num, k As Integer Dim Ans1, Ans2, cnt As Integer Dim rng As Range num = 1 'Tracks number of employees entered. Becomes actual UBound of array myPath = ThisWorkbook.Path ADDEMP: Employee(num) = InputBox("Enter Employee's Name (First or Middle, Last Name)", "ADD EMPLOYEE") If Employee(num) = "" Or Left(Employee(num), 1) = " " Then MsgBox ("Invalid Entry. Please enter employee's name.") Employee(num) = "" GoTo ADDEMP End If Msg = "Is the employee's name correctly entered?" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & Employee(num) Config = vbYesNo + vbQuestion Title = "VERIFY ENTRY" Ans1 = MsgBox(Msg, Config, Title) If Ans1 = vbNo Then Employee(num) = "" GoTo ADDEMP End If 'Validation of Entry Complete 'Proceed to place data in cells Set rng = Range("I65536").End(xlUp).Offset(1, 0) rng.Select rng.Value = Employee(num) rng.Offset(0, -1).Value = Date 'Place DATE employee added into cell If rng.Offset(-1, -2).Value = "NUM" Then 'Determine if this is first line in list cnt = 1 'If YES, cnt = 1 Else cnt = rng.Offset(-1, -2).Value 'Pick up last number entered (total employees to date) cnt = cnt + 1 'Add one to last count in employee list End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. Msg = "Do you want to enter another employee?" Config = vbYesNo + vbQuestion Title = "CONTINUE" Ans2 = MsgBox(Msg, Config, Title) If Ans2 = vbYes Then num = num + 1 GoTo ADDEMP End If 'Create workbook for each employee entered For k = 1 To num Workbooks("QA Template.xls").SaveAs Filename:=myPath & "\" & Employee(k) & ".xls" Next k End Sub I appreciate any help with this. Les |
Subscript Out of Range Error
Subscript out of range means that there is nothing by that name in the
collection that you're looking at. This set of lines: For k = 1 To num Workbooks("QA Template.xls").SaveAs _ Filename:=myPath & "\" & Employee(k) & ".xls" Next k will save the "QA template.xls" workbook with a new name. That means that there is no longer an open workbook with that name. Without any testing at all, I'd try: For k = 1 To num Workbooks("QA Template.xls").SaveCOPYAs _ Filename:=myPath & "\" & Employee(k) & ".xls" Next k ..SaveCopyAs won't touch the original workbook's name. WLMPilot wrote: The workbooks and macros used are being created in Excel 2007, but saved in the 97-2003 format. I have a macro that the boss will use to enter employees and place the names, date added, and count (sequential) into a worksheet. It loops in case there is more than one name to be added. NOTE: The boss will be in workbook(QA Master.xls) Once the boss is through entering names, the macro will then loop and create a workbook, copying the Workbook("QA Template.xls") and renaming it with the employee name that was entered. I don't know why, but I am getting the following error on the line that copies and names the new workbook: Runtime Error 9 Subscript Out of Range The macro is below: Option Base 1 'Add Employee to list and create workbook for employee Private Sub CommandButton2_Click() Dim Employee(1 To 200), Msg, Title As String 'Array may not reach 200 entries Dim Config, num, k As Integer Dim Ans1, Ans2, cnt As Integer Dim rng As Range num = 1 'Tracks number of employees entered. Becomes actual UBound of array myPath = ThisWorkbook.Path ADDEMP: Employee(num) = InputBox("Enter Employee's Name (First or Middle, Last Name)", "ADD EMPLOYEE") If Employee(num) = "" Or Left(Employee(num), 1) = " " Then MsgBox ("Invalid Entry. Please enter employee's name.") Employee(num) = "" GoTo ADDEMP End If Msg = "Is the employee's name correctly entered?" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & Employee(num) Config = vbYesNo + vbQuestion Title = "VERIFY ENTRY" Ans1 = MsgBox(Msg, Config, Title) If Ans1 = vbNo Then Employee(num) = "" GoTo ADDEMP End If 'Validation of Entry Complete 'Proceed to place data in cells Set rng = Range("I65536").End(xlUp).Offset(1, 0) rng.Select rng.Value = Employee(num) rng.Offset(0, -1).Value = Date 'Place DATE employee added into cell If rng.Offset(-1, -2).Value = "NUM" Then 'Determine if this is first line in list cnt = 1 'If YES, cnt = 1 Else cnt = rng.Offset(-1, -2).Value 'Pick up last number entered (total employees to date) cnt = cnt + 1 'Add one to last count in employee list End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. Msg = "Do you want to enter another employee?" Config = vbYesNo + vbQuestion Title = "CONTINUE" Ans2 = MsgBox(Msg, Config, Title) If Ans2 = vbYes Then num = num + 1 GoTo ADDEMP End If 'Create workbook for each employee entered For k = 1 To num Workbooks("QA Template.xls").SaveAs Filename:=myPath & "\" & Employee(k) & ".xls" Next k End Sub I appreciate any help with this. Les -- Dave Peterson |
Subscript Out of Range Error
For k = 1 To num
Workbooks("QA Template.xls").SaveAs Filename:=myPath & "\" & Employee(k) & ".xls" Next k Les, if this is where you are getting the error, then be sure that your workbook name, including the file extension is correct. You mentioned that you are working in xl07 and saving as xl97 - xl03. If so, your QA Template file could have .xlsx or .xlsm file extension which would throw the error. "WLMPilot" wrote in message ... The workbooks and macros used are being created in Excel 2007, but saved in the 97-2003 format. I have a macro that the boss will use to enter employees and place the names, date added, and count (sequential) into a worksheet. It loops in case there is more than one name to be added. NOTE: The boss will be in workbook(QA Master.xls) Once the boss is through entering names, the macro will then loop and create a workbook, copying the Workbook("QA Template.xls") and renaming it with the employee name that was entered. I don't know why, but I am getting the following error on the line that copies and names the new workbook: Runtime Error 9 Subscript Out of Range The macro is below: Option Base 1 'Add Employee to list and create workbook for employee Private Sub CommandButton2_Click() Dim Employee(1 To 200), Msg, Title As String 'Array may not reach 200 entries Dim Config, num, k As Integer Dim Ans1, Ans2, cnt As Integer Dim rng As Range num = 1 'Tracks number of employees entered. Becomes actual UBound of array myPath = ThisWorkbook.Path ADDEMP: Employee(num) = InputBox("Enter Employee's Name (First or Middle, Last Name)", "ADD EMPLOYEE") If Employee(num) = "" Or Left(Employee(num), 1) = " " Then MsgBox ("Invalid Entry. Please enter employee's name.") Employee(num) = "" GoTo ADDEMP End If Msg = "Is the employee's name correctly entered?" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & Employee(num) Config = vbYesNo + vbQuestion Title = "VERIFY ENTRY" Ans1 = MsgBox(Msg, Config, Title) If Ans1 = vbNo Then Employee(num) = "" GoTo ADDEMP End If 'Validation of Entry Complete 'Proceed to place data in cells Set rng = Range("I65536").End(xlUp).Offset(1, 0) rng.Select rng.Value = Employee(num) rng.Offset(0, -1).Value = Date 'Place DATE employee added into cell If rng.Offset(-1, -2).Value = "NUM" Then 'Determine if this is first line in list cnt = 1 'If YES, cnt = 1 Else cnt = rng.Offset(-1, -2).Value 'Pick up last number entered (total employees to date) cnt = cnt + 1 'Add one to last count in employee list End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. Msg = "Do you want to enter another employee?" Config = vbYesNo + vbQuestion Title = "CONTINUE" Ans2 = MsgBox(Msg, Config, Title) If Ans2 = vbYes Then num = num + 1 GoTo ADDEMP End If 'Create workbook for each employee entered For k = 1 To num Workbooks("QA Template.xls").SaveAs Filename:=myPath & "\" & Employee(k) & ".xls" Next k End Sub I appreciate any help with this. Les |
Subscript Out of Range Error
The problem is with the SaveAs code. You have
Workbooks("QA Template.xls").SaveAs Filename:=myPath & "\" & Employee(k) On the first iteration of the loop, the code will save "QA Template.xls" under the new file name. After SaveAs, there is no workbook named "QA Template.xls". The workbook that was initially named "QA Tempalte.xls" is now named Employee(1), so on the second iteration of the loop, the code will blow up when you attempt to reference "QA Template.xls", since there is no longer a workbook with that name. A better way is to use SaveCopyAs rather than SaveAs. This will save "QA Template.xls" to the new name, but leave the workbook named "QA Template.xls" in place. With SaveCopyAs, the workbook is saved but is not open in Excel after the save. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Tue, 11 May 2010 07:05:01 -0700, WLMPilot wrote: The workbooks and macros used are being created in Excel 2007, but saved in the 97-2003 format. I have a macro that the boss will use to enter employees and place the names, date added, and count (sequential) into a worksheet. It loops in case there is more than one name to be added. NOTE: The boss will be in workbook(QA Master.xls) Once the boss is through entering names, the macro will then loop and create a workbook, copying the Workbook("QA Template.xls") and renaming it with the employee name that was entered. I don't know why, but I am getting the following error on the line that copies and names the new workbook: Runtime Error 9 Subscript Out of Range The macro is below: Option Base 1 'Add Employee to list and create workbook for employee Private Sub CommandButton2_Click() Dim Employee(1 To 200), Msg, Title As String 'Array may not reach 200 entries Dim Config, num, k As Integer Dim Ans1, Ans2, cnt As Integer Dim rng As Range num = 1 'Tracks number of employees entered. Becomes actual UBound of array myPath = ThisWorkbook.Path ADDEMP: Employee(num) = InputBox("Enter Employee's Name (First or Middle, Last Name)", "ADD EMPLOYEE") If Employee(num) = "" Or Left(Employee(num), 1) = " " Then MsgBox ("Invalid Entry. Please enter employee's name.") Employee(num) = "" GoTo ADDEMP End If Msg = "Is the employee's name correctly entered?" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & Employee(num) Config = vbYesNo + vbQuestion Title = "VERIFY ENTRY" Ans1 = MsgBox(Msg, Config, Title) If Ans1 = vbNo Then Employee(num) = "" GoTo ADDEMP End If 'Validation of Entry Complete 'Proceed to place data in cells Set rng = Range("I65536").End(xlUp).Offset(1, 0) rng.Select rng.Value = Employee(num) rng.Offset(0, -1).Value = Date 'Place DATE employee added into cell If rng.Offset(-1, -2).Value = "NUM" Then 'Determine if this is first line in list cnt = 1 'If YES, cnt = 1 Else cnt = rng.Offset(-1, -2).Value 'Pick up last number entered (total employees to date) cnt = cnt + 1 'Add one to last count in employee list End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. Msg = "Do you want to enter another employee?" Config = vbYesNo + vbQuestion Title = "CONTINUE" Ans2 = MsgBox(Msg, Config, Title) If Ans2 = vbYes Then num = num + 1 GoTo ADDEMP End If 'Create workbook for each employee entered For k = 1 To num Workbooks("QA Template.xls").SaveAs Filename:=myPath & "\" & Employee(k) & ".xls" Next k End Sub I appreciate any help with this. Les |
Subscript Out of Range Error
Well, back to square one. I have made a couple of changes:
1) Changed SaveAs to SaveCopyAs 2) Removed the need of an array by creating the workbook for the employee at the point the name is placed in the spreadsheet. Workbook("QA Template.xls") does have the file ext .xls. It is in the same directory as the QA Master.xls workbook which is where mypath gets the path. FYI...The explanation previously given that sts the error may have been caused because of the SaveAs renaming the template workbook apparently was not true in this case. The QA Template workbook was never renamed on the first pass of the loop. Here is the section of the macro that contains the SaveCopyAs code now so you can see the new placement: End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" Thanks for your efforts. I await other options. Les "JLGWhiz" wrote: For k = 1 To num Workbooks("QA Template.xls").SaveAs Filename:=myPath & "\" & Employee(k) & ".xls" Next k Les, if this is where you are getting the error, then be sure that your workbook name, including the file extension is correct. You mentioned that you are working in xl07 and saving as xl97 - xl03. If so, your QA Template file could have .xlsx or .xlsm file extension which would throw the error. "WLMPilot" wrote in message ... The workbooks and macros used are being created in Excel 2007, but saved in the 97-2003 format. I have a macro that the boss will use to enter employees and place the names, date added, and count (sequential) into a worksheet. It loops in case there is more than one name to be added. NOTE: The boss will be in workbook(QA Master.xls) Once the boss is through entering names, the macro will then loop and create a workbook, copying the Workbook("QA Template.xls") and renaming it with the employee name that was entered. I don't know why, but I am getting the following error on the line that copies and names the new workbook: Runtime Error 9 Subscript Out of Range The macro is below: Option Base 1 'Add Employee to list and create workbook for employee Private Sub CommandButton2_Click() Dim Employee(1 To 200), Msg, Title As String 'Array may not reach 200 entries Dim Config, num, k As Integer Dim Ans1, Ans2, cnt As Integer Dim rng As Range num = 1 'Tracks number of employees entered. Becomes actual UBound of array myPath = ThisWorkbook.Path ADDEMP: Employee(num) = InputBox("Enter Employee's Name (First or Middle, Last Name)", "ADD EMPLOYEE") If Employee(num) = "" Or Left(Employee(num), 1) = " " Then MsgBox ("Invalid Entry. Please enter employee's name.") Employee(num) = "" GoTo ADDEMP End If Msg = "Is the employee's name correctly entered?" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & Employee(num) Config = vbYesNo + vbQuestion Title = "VERIFY ENTRY" Ans1 = MsgBox(Msg, Config, Title) If Ans1 = vbNo Then Employee(num) = "" GoTo ADDEMP End If 'Validation of Entry Complete 'Proceed to place data in cells Set rng = Range("I65536").End(xlUp).Offset(1, 0) rng.Select rng.Value = Employee(num) rng.Offset(0, -1).Value = Date 'Place DATE employee added into cell If rng.Offset(-1, -2).Value = "NUM" Then 'Determine if this is first line in list cnt = 1 'If YES, cnt = 1 Else cnt = rng.Offset(-1, -2).Value 'Pick up last number entered (total employees to date) cnt = cnt + 1 'Add one to last count in employee list End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. Msg = "Do you want to enter another employee?" Config = vbYesNo + vbQuestion Title = "CONTINUE" Ans2 = MsgBox(Msg, Config, Title) If Ans2 = vbYes Then num = num + 1 GoTo ADDEMP End If 'Create workbook for each employee entered For k = 1 To num Workbooks("QA Template.xls").SaveAs Filename:=myPath & "\" & Employee(k) & ".xls" Next k End Sub I appreciate any help with this. Les . |
Subscript Out of Range Error
Well, I worked up this little test case and it seemed to work OK.
Option Base 1 Sub multWB() Dim i As Long, myPath As String, myNames myPath = ThisWorkbook.Path myNames = Array("Joe", "George", "Ralph") For i = 1 To UBound(myNames) Workbooks("TestBook.xls").SaveCopyAs _ Filename:=myPath & "\" & myNames(i) & ".xls" Next End Sub Chip pointed out something that I overlooked in my earlier post, and that is that using just SaveAs will change the name of the active workbook and effectively closes the original workbook that was copied. But using the SaveCopyAS method leaves the original workbook intact and saves the new workbooks as a closed file. The setup above captures the essence of what your code did to build the array of names, using option base 1, so that when the For ... Next loop runs, it is using the same parameters that your code was using and it worked. I don't know if it was a typo when you made the last post, but in this: Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" the (x) is missing from Employee, to indicate which item of the array. Your actual code probably has it included, but if it doesn't that would keep it from working. "WLMPilot" wrote in message ... Well, back to square one. I have made a couple of changes: 1) Changed SaveAs to SaveCopyAs 2) Removed the need of an array by creating the workbook for the employee at the point the name is placed in the spreadsheet. Workbook("QA Template.xls") does have the file ext .xls. It is in the same directory as the QA Master.xls workbook which is where mypath gets the path. FYI...The explanation previously given that sts the error may have been caused because of the SaveAs renaming the template workbook apparently was not true in this case. The QA Template workbook was never renamed on the first pass of the loop. Here is the section of the macro that contains the SaveCopyAs code now so you can see the new placement: End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" Thanks for your efforts. I await other options. Les "JLGWhiz" wrote: For k = 1 To num Workbooks("QA Template.xls").SaveAs Filename:=myPath & "\" & Employee(k) & ".xls" Next k Les, if this is where you are getting the error, then be sure that your workbook name, including the file extension is correct. You mentioned that you are working in xl07 and saving as xl97 - xl03. If so, your QA Template file could have .xlsx or .xlsm file extension which would throw the error. "WLMPilot" wrote in message ... The workbooks and macros used are being created in Excel 2007, but saved in the 97-2003 format. I have a macro that the boss will use to enter employees and place the names, date added, and count (sequential) into a worksheet. It loops in case there is more than one name to be added. NOTE: The boss will be in workbook(QA Master.xls) Once the boss is through entering names, the macro will then loop and create a workbook, copying the Workbook("QA Template.xls") and renaming it with the employee name that was entered. I don't know why, but I am getting the following error on the line that copies and names the new workbook: Runtime Error 9 Subscript Out of Range The macro is below: Option Base 1 'Add Employee to list and create workbook for employee Private Sub CommandButton2_Click() Dim Employee(1 To 200), Msg, Title As String 'Array may not reach 200 entries Dim Config, num, k As Integer Dim Ans1, Ans2, cnt As Integer Dim rng As Range num = 1 'Tracks number of employees entered. Becomes actual UBound of array myPath = ThisWorkbook.Path ADDEMP: Employee(num) = InputBox("Enter Employee's Name (First or Middle, Last Name)", "ADD EMPLOYEE") If Employee(num) = "" Or Left(Employee(num), 1) = " " Then MsgBox ("Invalid Entry. Please enter employee's name.") Employee(num) = "" GoTo ADDEMP End If Msg = "Is the employee's name correctly entered?" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & Employee(num) Config = vbYesNo + vbQuestion Title = "VERIFY ENTRY" Ans1 = MsgBox(Msg, Config, Title) If Ans1 = vbNo Then Employee(num) = "" GoTo ADDEMP End If 'Validation of Entry Complete 'Proceed to place data in cells Set rng = Range("I65536").End(xlUp).Offset(1, 0) rng.Select rng.Value = Employee(num) rng.Offset(0, -1).Value = Date 'Place DATE employee added into cell If rng.Offset(-1, -2).Value = "NUM" Then 'Determine if this is first line in list cnt = 1 'If YES, cnt = 1 Else cnt = rng.Offset(-1, -2).Value 'Pick up last number entered (total employees to date) cnt = cnt + 1 'Add one to last count in employee list End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. Msg = "Do you want to enter another employee?" Config = vbYesNo + vbQuestion Title = "CONTINUE" Ans2 = MsgBox(Msg, Config, Title) If Ans2 = vbYes Then num = num + 1 GoTo ADDEMP End If 'Create workbook for each employee entered For k = 1 To num Workbooks("QA Template.xls").SaveAs Filename:=myPath & "\" & Employee(k) & ".xls" Next k End Sub I appreciate any help with this. Les . |
Subscript Out of Range Error
Oops! In my last post, I failed to mention one important item. I am still
getting the same error. As I indicated, I removed the neccessity for an array by moving the SaveCopyAs line to the section in the code just prior to asking if another name needs to be entered. This way, everything that needs to be done with an employee's name is done prior to either exiting or looping back to get another name. Here is the section that holds the code as it currently is: Here is the section of the macro that contains the SaveCopyAs code now so you can see the new placement: End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" The error is indicated as occurring at the point of trying to copy and rename the new workbook. Les "JLGWhiz" wrote: Well, I worked up this little test case and it seemed to work OK. Option Base 1 Sub multWB() Dim i As Long, myPath As String, myNames myPath = ThisWorkbook.Path myNames = Array("Joe", "George", "Ralph") For i = 1 To UBound(myNames) Workbooks("TestBook.xls").SaveCopyAs _ Filename:=myPath & "\" & myNames(i) & ".xls" Next End Sub Chip pointed out something that I overlooked in my earlier post, and that is that using just SaveAs will change the name of the active workbook and effectively closes the original workbook that was copied. But using the SaveCopyAS method leaves the original workbook intact and saves the new workbooks as a closed file. The setup above captures the essence of what your code did to build the array of names, using option base 1, so that when the For ... Next loop runs, it is using the same parameters that your code was using and it worked. I don't know if it was a typo when you made the last post, but in this: Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" the (x) is missing from Employee, to indicate which item of the array. Your actual code probably has it included, but if it doesn't that would keep it from working. "WLMPilot" wrote in message ... Well, back to square one. I have made a couple of changes: 1) Changed SaveAs to SaveCopyAs 2) Removed the need of an array by creating the workbook for the employee at the point the name is placed in the spreadsheet. Workbook("QA Template.xls") does have the file ext .xls. It is in the same directory as the QA Master.xls workbook which is where mypath gets the path. FYI...The explanation previously given that sts the error may have been caused because of the SaveAs renaming the template workbook apparently was not true in this case. The QA Template workbook was never renamed on the first pass of the loop. Here is the section of the macro that contains the SaveCopyAs code now so you can see the new placement: End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" Thanks for your efforts. I await other options. Les "JLGWhiz" wrote: For k = 1 To num Workbooks("QA Template.xls").SaveAs Filename:=myPath & "\" & Employee(k) & ".xls" Next k Les, if this is where you are getting the error, then be sure that your workbook name, including the file extension is correct. You mentioned that you are working in xl07 and saving as xl97 - xl03. If so, your QA Template file could have .xlsx or .xlsm file extension which would throw the error. "WLMPilot" wrote in message ... The workbooks and macros used are being created in Excel 2007, but saved in the 97-2003 format. I have a macro that the boss will use to enter employees and place the names, date added, and count (sequential) into a worksheet. It loops in case there is more than one name to be added. NOTE: The boss will be in workbook(QA Master.xls) Once the boss is through entering names, the macro will then loop and create a workbook, copying the Workbook("QA Template.xls") and renaming it with the employee name that was entered. I don't know why, but I am getting the following error on the line that copies and names the new workbook: Runtime Error 9 Subscript Out of Range The macro is below: Option Base 1 'Add Employee to list and create workbook for employee Private Sub CommandButton2_Click() Dim Employee(1 To 200), Msg, Title As String 'Array may not reach 200 entries Dim Config, num, k As Integer Dim Ans1, Ans2, cnt As Integer Dim rng As Range num = 1 'Tracks number of employees entered. Becomes actual UBound of array myPath = ThisWorkbook.Path ADDEMP: Employee(num) = InputBox("Enter Employee's Name (First or Middle, Last Name)", "ADD EMPLOYEE") If Employee(num) = "" Or Left(Employee(num), 1) = " " Then MsgBox ("Invalid Entry. Please enter employee's name.") Employee(num) = "" GoTo ADDEMP End If Msg = "Is the employee's name correctly entered?" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & Employee(num) Config = vbYesNo + vbQuestion Title = "VERIFY ENTRY" Ans1 = MsgBox(Msg, Config, Title) If Ans1 = vbNo Then Employee(num) = "" GoTo ADDEMP End If 'Validation of Entry Complete 'Proceed to place data in cells Set rng = Range("I65536").End(xlUp).Offset(1, 0) rng.Select rng.Value = Employee(num) rng.Offset(0, -1).Value = Date 'Place DATE employee added into cell If rng.Offset(-1, -2).Value = "NUM" Then 'Determine if this is first line in list cnt = 1 'If YES, cnt = 1 Else cnt = rng.Offset(-1, -2).Value 'Pick up last number entered (total employees to date) cnt = cnt + 1 'Add one to last count in employee list End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. Msg = "Do you want to enter another employee?" Config = vbYesNo + vbQuestion Title = "CONTINUE" Ans2 = MsgBox(Msg, Config, Title) If Ans2 = vbYes Then num = num + 1 GoTo ADDEMP End If 'Create workbook for each employee entered For k = 1 To num Workbooks("QA Template.xls").SaveAs Filename:=myPath & "\" & Employee(k) & ".xls" Next k End Sub I appreciate any help with this. Les . . |
Subscript Out of Range Error
So now, in theory, you should be creating a single workbook for a single
name and then asking if there is a another name to process? Did you make sure that your InputBox variable is "Employee" ? (without the quotes of course.) If the variable Employee contains the right value and Workbooks("QA Template.xls") is a valid workbook,. there should be no reason why you would get an error. Why don't you post the complete code again as currently written so I can see if there are any fuzzy hands in it? "WLMPilot" wrote in message ... Oops! In my last post, I failed to mention one important item. I am still getting the same error. As I indicated, I removed the neccessity for an array by moving the SaveCopyAs line to the section in the code just prior to asking if another name needs to be entered. This way, everything that needs to be done with an employee's name is done prior to either exiting or looping back to get another name. Here is the section that holds the code as it currently is: Here is the section of the macro that contains the SaveCopyAs code now so you can see the new placement: End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" The error is indicated as occurring at the point of trying to copy and rename the new workbook. Les "JLGWhiz" wrote: Well, I worked up this little test case and it seemed to work OK. Option Base 1 Sub multWB() Dim i As Long, myPath As String, myNames myPath = ThisWorkbook.Path myNames = Array("Joe", "George", "Ralph") For i = 1 To UBound(myNames) Workbooks("TestBook.xls").SaveCopyAs _ Filename:=myPath & "\" & myNames(i) & ".xls" Next End Sub Chip pointed out something that I overlooked in my earlier post, and that is that using just SaveAs will change the name of the active workbook and effectively closes the original workbook that was copied. But using the SaveCopyAS method leaves the original workbook intact and saves the new workbooks as a closed file. The setup above captures the essence of what your code did to build the array of names, using option base 1, so that when the For ... Next loop runs, it is using the same parameters that your code was using and it worked. I don't know if it was a typo when you made the last post, but in this: Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" the (x) is missing from Employee, to indicate which item of the array. Your actual code probably has it included, but if it doesn't that would keep it from working. "WLMPilot" wrote in message ... Well, back to square one. I have made a couple of changes: 1) Changed SaveAs to SaveCopyAs 2) Removed the need of an array by creating the workbook for the employee at the point the name is placed in the spreadsheet. Workbook("QA Template.xls") does have the file ext .xls. It is in the same directory as the QA Master.xls workbook which is where mypath gets the path. FYI...The explanation previously given that sts the error may have been caused because of the SaveAs renaming the template workbook apparently was not true in this case. The QA Template workbook was never renamed on the first pass of the loop. Here is the section of the macro that contains the SaveCopyAs code now so you can see the new placement: End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" Thanks for your efforts. I await other options. Les "JLGWhiz" wrote: For k = 1 To num Workbooks("QA Template.xls").SaveAs Filename:=myPath & "\" & Employee(k) & ".xls" Next k Les, if this is where you are getting the error, then be sure that your workbook name, including the file extension is correct. You mentioned that you are working in xl07 and saving as xl97 - xl03. If so, your QA Template file could have .xlsx or .xlsm file extension which would throw the error. "WLMPilot" wrote in message ... The workbooks and macros used are being created in Excel 2007, but saved in the 97-2003 format. I have a macro that the boss will use to enter employees and place the names, date added, and count (sequential) into a worksheet. It loops in case there is more than one name to be added. NOTE: The boss will be in workbook(QA Master.xls) Once the boss is through entering names, the macro will then loop and create a workbook, copying the Workbook("QA Template.xls") and renaming it with the employee name that was entered. I don't know why, but I am getting the following error on the line that copies and names the new workbook: Runtime Error 9 Subscript Out of Range The macro is below: Option Base 1 'Add Employee to list and create workbook for employee Private Sub CommandButton2_Click() Dim Employee(1 To 200), Msg, Title As String 'Array may not reach 200 entries Dim Config, num, k As Integer Dim Ans1, Ans2, cnt As Integer Dim rng As Range num = 1 'Tracks number of employees entered. Becomes actual UBound of array myPath = ThisWorkbook.Path ADDEMP: Employee(num) = InputBox("Enter Employee's Name (First or Middle, Last Name)", "ADD EMPLOYEE") If Employee(num) = "" Or Left(Employee(num), 1) = " " Then MsgBox ("Invalid Entry. Please enter employee's name.") Employee(num) = "" GoTo ADDEMP End If Msg = "Is the employee's name correctly entered?" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & Employee(num) Config = vbYesNo + vbQuestion Title = "VERIFY ENTRY" Ans1 = MsgBox(Msg, Config, Title) If Ans1 = vbNo Then Employee(num) = "" GoTo ADDEMP End If 'Validation of Entry Complete 'Proceed to place data in cells Set rng = Range("I65536").End(xlUp).Offset(1, 0) rng.Select rng.Value = Employee(num) rng.Offset(0, -1).Value = Date 'Place DATE employee added into cell If rng.Offset(-1, -2).Value = "NUM" Then 'Determine if this is first line in list cnt = 1 'If YES, cnt = 1 Else cnt = rng.Offset(-1, -2).Value 'Pick up last number entered (total employees to date) cnt = cnt + 1 'Add one to last count in employee list End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. Msg = "Do you want to enter another employee?" Config = vbYesNo + vbQuestion Title = "CONTINUE" Ans2 = MsgBox(Msg, Config, Title) If Ans2 = vbYes Then num = num + 1 GoTo ADDEMP End If 'Create workbook for each employee entered For k = 1 To num Workbooks("QA Template.xls").SaveAs Filename:=myPath & "\" & Employee(k) & ".xls" Next k End Sub I appreciate any help with this. Les . . |
Subscript Out of Range Error
Here is the code as is (without the array). When the error occurs, I do a
mouseover of the variables in the code and they are correct, ie Employee holds the value of the name I entered and mypath is correct. Workbooks("QA Template.xls") is valid and in the current directory. The only workbook opened is the QA Master that the boss would have opened when he clicks the ADD EMPLOYEE commandbutton that executes this macro. Option Base 1 'Add Employee to list and create workbook for employee Private Sub CommandButton2_Click() Dim Employee, Msg, Title As String Dim Config, num, k As Integer Dim Ans1, Ans2, cnt As Integer Dim rng As Range myPath = ThisWorkbook.Path ADDEMP: Employee = InputBox("Enter Employee's Name (First or Middle, Last Name)", "ADD EMPLOYEE") If Employee = "" Or Left(Employee, 1) = " " Then MsgBox ("Invalid Entry. Please enter employee's name.") Employee = "" GoTo ADDEMP End If Msg = "Is the employee's name correctly entered?" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & Employee Config = vbYesNo + vbQuestion Title = "VERIFY ENTRY" Ans1 = MsgBox(Msg, Config, Title) If Ans1 = vbNo Then Employee = "" GoTo ADDEMP End If 'Validation of Entry Complete 'Proceed to place data in cells Set rng = Range("I65536").End(xlUp).Offset(1, 0) rng.Select rng.Value = Employee rng.Offset(0, -1).Value = Date 'Place DATE employee added into cell If rng.Offset(-1, -2).Value = "NUM" Then 'Determine if this is first line in list cnt = 1 'If YES, cnt = 1 Else cnt = rng.Offset(-1, -2).Value 'Pick up last number entered (total employees to date) cnt = cnt + 1 'Add one to last count in employee list End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" Config = vbYesNo + vbQuestion Title = "CONTINUE" Ans2 = MsgBox(Msg, Config, Title) If Ans2 = vbYes Then GoTo ADDEMP End If End Sub Thanks again for your time and effort on this. I did some googling and wonder if I do not need a FileFormat added to the SaveCopyAs line since I am in 2007 and saving in the 97-2003 format? Also, since I am copying and renaming a different workbook than the one that is opened, is SaveCopyAs the correct command? Les "JLGWhiz" wrote: So now, in theory, you should be creating a single workbook for a single name and then asking if there is a another name to process? Did you make sure that your InputBox variable is "Employee" ? (without the quotes of course.) If the variable Employee contains the right value and Workbooks("QA Template.xls") is a valid workbook,. there should be no reason why you would get an error. Why don't you post the complete code again as currently written so I can see if there are any fuzzy hands in it? "WLMPilot" wrote in message ... Oops! In my last post, I failed to mention one important item. I am still getting the same error. As I indicated, I removed the neccessity for an array by moving the SaveCopyAs line to the section in the code just prior to asking if another name needs to be entered. This way, everything that needs to be done with an employee's name is done prior to either exiting or looping back to get another name. Here is the section that holds the code as it currently is: Here is the section of the macro that contains the SaveCopyAs code now so you can see the new placement: End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" The error is indicated as occurring at the point of trying to copy and rename the new workbook. Les "JLGWhiz" wrote: Well, I worked up this little test case and it seemed to work OK. Option Base 1 Sub multWB() Dim i As Long, myPath As String, myNames myPath = ThisWorkbook.Path myNames = Array("Joe", "George", "Ralph") For i = 1 To UBound(myNames) Workbooks("TestBook.xls").SaveCopyAs _ Filename:=myPath & "\" & myNames(i) & ".xls" Next End Sub Chip pointed out something that I overlooked in my earlier post, and that is that using just SaveAs will change the name of the active workbook and effectively closes the original workbook that was copied. But using the SaveCopyAS method leaves the original workbook intact and saves the new workbooks as a closed file. The setup above captures the essence of what your code did to build the array of names, using option base 1, so that when the For ... Next loop runs, it is using the same parameters that your code was using and it worked. I don't know if it was a typo when you made the last post, but in this: Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" the (x) is missing from Employee, to indicate which item of the array. Your actual code probably has it included, but if it doesn't that would keep it from working. "WLMPilot" wrote in message ... Well, back to square one. I have made a couple of changes: 1) Changed SaveAs to SaveCopyAs 2) Removed the need of an array by creating the workbook for the employee at the point the name is placed in the spreadsheet. Workbook("QA Template.xls") does have the file ext .xls. It is in the same directory as the QA Master.xls workbook which is where mypath gets the path. FYI...The explanation previously given that sts the error may have been caused because of the SaveAs renaming the template workbook apparently was not true in this case. The QA Template workbook was never renamed on the first pass of the loop. Here is the section of the macro that contains the SaveCopyAs code now so you can see the new placement: End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" Thanks for your efforts. I await other options. Les "JLGWhiz" wrote: For k = 1 To num Workbooks("QA Template.xls").SaveAs Filename:=myPath & "\" & Employee(k) & ".xls" Next k Les, if this is where you are getting the error, then be sure that your workbook name, including the file extension is correct. You mentioned that you are working in xl07 and saving as xl97 - xl03. If so, your QA Template file could have .xlsx or .xlsm file extension which would throw the error. "WLMPilot" wrote in message ... The workbooks and macros used are being created in Excel 2007, but saved in the 97-2003 format. I have a macro that the boss will use to enter employees and place the names, date added, and count (sequential) into a worksheet. It loops in case there is more than one name to be added. NOTE: The boss will be in workbook(QA Master.xls) Once the boss is through entering names, the macro will then loop and create a workbook, copying the Workbook("QA Template.xls") and renaming it with the employee name that was entered. I don't know why, but I am getting the following error on the line that copies and names the new workbook: Runtime Error 9 Subscript Out of Range The macro is below: Option Base 1 'Add Employee to list and create workbook for employee Private Sub CommandButton2_Click() Dim Employee(1 To 200), Msg, Title As String 'Array may not reach 200 entries Dim Config, num, k As Integer Dim Ans1, Ans2, cnt As Integer Dim rng As Range num = 1 'Tracks number of employees entered. Becomes actual UBound of array myPath = ThisWorkbook.Path ADDEMP: Employee(num) = InputBox("Enter Employee's Name (First or Middle, Last Name)", "ADD EMPLOYEE") If Employee(num) = "" Or Left(Employee(num), 1) = " " Then MsgBox ("Invalid Entry. Please enter employee's name.") Employee(num) = "" GoTo ADDEMP End If Msg = "Is the employee's name correctly entered?" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & Employee(num) Config = vbYesNo + vbQuestion Title = "VERIFY ENTRY" Ans1 = MsgBox(Msg, Config, Title) If Ans1 = vbNo Then Employee(num) = "" GoTo ADDEMP End If 'Validation of Entry Complete 'Proceed to place data in cells Set rng = Range("I65536").End(xlUp).Offset(1, 0) rng.Select rng.Value = Employee(num) rng.Offset(0, -1).Value = Date 'Place DATE employee added into cell If rng.Offset(-1, -2).Value = "NUM" Then 'Determine if this is first line in list cnt = 1 'If YES, cnt = 1 Else cnt = rng.Offset(-1, -2).Value 'Pick up last number entered (total employees to date) cnt = cnt + 1 'Add one to last count in employee list End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. Msg = "Do you want to enter another employee?" Config = vbYesNo + vbQuestion Title = "CONTINUE" Ans2 = MsgBox(Msg, Config, Title) If Ans2 = vbYes Then num = num + 1 GoTo ADDEMP End If 'Create workbook for each employee entered For k = 1 To num Workbooks("QA Template.xls").SaveAs Filename:=myPath & "\" & Employee(k) & ".xls" Next k End Sub I appreciate any help with this. Les . . . |
Subscript Out of Range Error
Les, the macro works as it should down to the line to SaveCopyAs. All
variables are properly valued and cells are populated based on those values. The problem appears to be the workbook name. This can be the causes: 1. Spelling error. 2. Workbook is not open. 3. File extension is incorrect (.xls vs .xlsx or .xlsm) 4. File extension is not visible in system but is used in code, or vice versa. Click the FileOpen folder icon and look for this file: QA Template.xls If it does not appear exactly like this, then you need to change the code to reflect the correct name, exactly as it appears in the file folder. If the file is not open when the macro runs, you will need to open the file before attempthing the SaveCopyAs. "WLMPilot" wrote in message ... Here is the code as is (without the array). When the error occurs, I do a mouseover of the variables in the code and they are correct, ie Employee holds the value of the name I entered and mypath is correct. Workbooks("QA Template.xls") is valid and in the current directory. The only workbook opened is the QA Master that the boss would have opened when he clicks the ADD EMPLOYEE commandbutton that executes this macro. Option Base 1 'Add Employee to list and create workbook for employee Private Sub CommandButton2_Click() Dim Employee, Msg, Title As String Dim Config, num, k As Integer Dim Ans1, Ans2, cnt As Integer Dim rng As Range myPath = ThisWorkbook.Path ADDEMP: Employee = InputBox("Enter Employee's Name (First or Middle, Last Name)", "ADD EMPLOYEE") If Employee = "" Or Left(Employee, 1) = " " Then MsgBox ("Invalid Entry. Please enter employee's name.") Employee = "" GoTo ADDEMP End If Msg = "Is the employee's name correctly entered?" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & Employee Config = vbYesNo + vbQuestion Title = "VERIFY ENTRY" Ans1 = MsgBox(Msg, Config, Title) If Ans1 = vbNo Then Employee = "" GoTo ADDEMP End If 'Validation of Entry Complete 'Proceed to place data in cells Set rng = Range("I65536").End(xlUp).Offset(1, 0) rng.Select rng.Value = Employee rng.Offset(0, -1).Value = Date 'Place DATE employee added into cell If rng.Offset(-1, -2).Value = "NUM" Then 'Determine if this is first line in list cnt = 1 'If YES, cnt = 1 Else cnt = rng.Offset(-1, -2).Value 'Pick up last number entered (total employees to date) cnt = cnt + 1 'Add one to last count in employee list End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" Config = vbYesNo + vbQuestion Title = "CONTINUE" Ans2 = MsgBox(Msg, Config, Title) If Ans2 = vbYes Then GoTo ADDEMP End If End Sub Thanks again for your time and effort on this. I did some googling and wonder if I do not need a FileFormat added to the SaveCopyAs line since I am in 2007 and saving in the 97-2003 format? Also, since I am copying and renaming a different workbook than the one that is opened, is SaveCopyAs the correct command? Les "JLGWhiz" wrote: So now, in theory, you should be creating a single workbook for a single name and then asking if there is a another name to process? Did you make sure that your InputBox variable is "Employee" ? (without the quotes of course.) If the variable Employee contains the right value and Workbooks("QA Template.xls") is a valid workbook,. there should be no reason why you would get an error. Why don't you post the complete code again as currently written so I can see if there are any fuzzy hands in it? "WLMPilot" wrote in message ... Oops! In my last post, I failed to mention one important item. I am still getting the same error. As I indicated, I removed the neccessity for an array by moving the SaveCopyAs line to the section in the code just prior to asking if another name needs to be entered. This way, everything that needs to be done with an employee's name is done prior to either exiting or looping back to get another name. Here is the section that holds the code as it currently is: Here is the section of the macro that contains the SaveCopyAs code now so you can see the new placement: End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" The error is indicated as occurring at the point of trying to copy and rename the new workbook. Les "JLGWhiz" wrote: Well, I worked up this little test case and it seemed to work OK. Option Base 1 Sub multWB() Dim i As Long, myPath As String, myNames myPath = ThisWorkbook.Path myNames = Array("Joe", "George", "Ralph") For i = 1 To UBound(myNames) Workbooks("TestBook.xls").SaveCopyAs _ Filename:=myPath & "\" & myNames(i) & ".xls" Next End Sub Chip pointed out something that I overlooked in my earlier post, and that is that using just SaveAs will change the name of the active workbook and effectively closes the original workbook that was copied. But using the SaveCopyAS method leaves the original workbook intact and saves the new workbooks as a closed file. The setup above captures the essence of what your code did to build the array of names, using option base 1, so that when the For ... Next loop runs, it is using the same parameters that your code was using and it worked. I don't know if it was a typo when you made the last post, but in this: Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" the (x) is missing from Employee, to indicate which item of the array. Your actual code probably has it included, but if it doesn't that would keep it from working. "WLMPilot" wrote in message ... Well, back to square one. I have made a couple of changes: 1) Changed SaveAs to SaveCopyAs 2) Removed the need of an array by creating the workbook for the employee at the point the name is placed in the spreadsheet. Workbook("QA Template.xls") does have the file ext .xls. It is in the same directory as the QA Master.xls workbook which is where mypath gets the path. FYI...The explanation previously given that sts the error may have been caused because of the SaveAs renaming the template workbook apparently was not true in this case. The QA Template workbook was never renamed on the first pass of the loop. Here is the section of the macro that contains the SaveCopyAs code now so you can see the new placement: End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" Thanks for your efforts. I await other options. Les "JLGWhiz" wrote: For k = 1 To num Workbooks("QA Template.xls").SaveAs Filename:=myPath & "\" & Employee(k) & ".xls" Next k Les, if this is where you are getting the error, then be sure that your workbook name, including the file extension is correct. You mentioned that you are working in xl07 and saving as xl97 - xl03. If so, your QA Template file could have .xlsx or .xlsm file extension which would throw the error. "WLMPilot" wrote in message ... The workbooks and macros used are being created in Excel 2007, but saved in the 97-2003 format. I have a macro that the boss will use to enter employees and place the names, date added, and count (sequential) into a worksheet. It loops in case there is more than one name to be added. NOTE: The boss will be in workbook(QA Master.xls) Once the boss is through entering names, the macro will then loop and create a workbook, copying the Workbook("QA Template.xls") and renaming it with the employee name that was entered. I don't know why, but I am getting the following error on the line that copies and names the new workbook: Runtime Error 9 Subscript Out of Range The macro is below: Option Base 1 'Add Employee to list and create workbook for employee Private Sub CommandButton2_Click() Dim Employee(1 To 200), Msg, Title As String 'Array may not reach 200 entries Dim Config, num, k As Integer Dim Ans1, Ans2, cnt As Integer Dim rng As Range num = 1 'Tracks number of employees entered. Becomes actual UBound of array myPath = ThisWorkbook.Path ADDEMP: Employee(num) = InputBox("Enter Employee's Name (First or Middle, Last Name)", "ADD EMPLOYEE") If Employee(num) = "" Or Left(Employee(num), 1) = " " Then MsgBox ("Invalid Entry. Please enter employee's name.") Employee(num) = "" GoTo ADDEMP End If Msg = "Is the employee's name correctly entered?" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & Employee(num) Config = vbYesNo + vbQuestion Title = "VERIFY ENTRY" Ans1 = MsgBox(Msg, Config, Title) If Ans1 = vbNo Then Employee(num) = "" GoTo ADDEMP End If 'Validation of Entry Complete 'Proceed to place data in cells Set rng = Range("I65536").End(xlUp).Offset(1, 0) rng.Select rng.Value = Employee(num) rng.Offset(0, -1).Value = Date 'Place DATE employee added into cell If rng.Offset(-1, -2).Value = "NUM" Then 'Determine if this is first line in list cnt = 1 'If YES, cnt = 1 Else cnt = rng.Offset(-1, -2).Value 'Pick up last number entered (total employees to date) cnt = cnt + 1 'Add one to last count in employee list End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. Msg = "Do you want to enter another employee?" Config = vbYesNo + vbQuestion Title = "CONTINUE" Ans2 = MsgBox(Msg, Config, Title) If Ans2 = vbYes Then num = num + 1 GoTo ADDEMP End If 'Create workbook for each employee entered For k = 1 To num Workbooks("QA Template.xls").SaveAs Filename:=myPath & "\" & Employee(k) & ".xls" Next k End Sub I appreciate any help with this. Les . . . |
Subscript Out of Range Error
I have verified spelling of filename. When I right-click on the file (QA
Template) and click Properties it indicates File Type as Microsoft Office Excel 97-2003 Worksheet (.xls). When I left-click to highlight the file (without opening), the file information at the bottom also indicates Microsoft Office Excel 97-2003 Worksheet. As I indicated in the previous post, QA Template is NOT open during this time that the macro is executing, so that may be the problem. Not sure why a command to copy and rename a file from a macro cannot be executed without opening that file, but anyway. What code do I need to open that workbook? Les "JLGWhiz" wrote: Les, the macro works as it should down to the line to SaveCopyAs. All variables are properly valued and cells are populated based on those values. The problem appears to be the workbook name. This can be the causes: 1. Spelling error. 2. Workbook is not open. 3. File extension is incorrect (.xls vs .xlsx or .xlsm) 4. File extension is not visible in system but is used in code, or vice versa. Click the FileOpen folder icon and look for this file: QA Template.xls If it does not appear exactly like this, then you need to change the code to reflect the correct name, exactly as it appears in the file folder. If the file is not open when the macro runs, you will need to open the file before attempthing the SaveCopyAs. "WLMPilot" wrote in message ... Here is the code as is (without the array). When the error occurs, I do a mouseover of the variables in the code and they are correct, ie Employee holds the value of the name I entered and mypath is correct. Workbooks("QA Template.xls") is valid and in the current directory. The only workbook opened is the QA Master that the boss would have opened when he clicks the ADD EMPLOYEE commandbutton that executes this macro. Option Base 1 'Add Employee to list and create workbook for employee Private Sub CommandButton2_Click() Dim Employee, Msg, Title As String Dim Config, num, k As Integer Dim Ans1, Ans2, cnt As Integer Dim rng As Range myPath = ThisWorkbook.Path ADDEMP: Employee = InputBox("Enter Employee's Name (First or Middle, Last Name)", "ADD EMPLOYEE") If Employee = "" Or Left(Employee, 1) = " " Then MsgBox ("Invalid Entry. Please enter employee's name.") Employee = "" GoTo ADDEMP End If Msg = "Is the employee's name correctly entered?" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & Employee Config = vbYesNo + vbQuestion Title = "VERIFY ENTRY" Ans1 = MsgBox(Msg, Config, Title) If Ans1 = vbNo Then Employee = "" GoTo ADDEMP End If 'Validation of Entry Complete 'Proceed to place data in cells Set rng = Range("I65536").End(xlUp).Offset(1, 0) rng.Select rng.Value = Employee rng.Offset(0, -1).Value = Date 'Place DATE employee added into cell If rng.Offset(-1, -2).Value = "NUM" Then 'Determine if this is first line in list cnt = 1 'If YES, cnt = 1 Else cnt = rng.Offset(-1, -2).Value 'Pick up last number entered (total employees to date) cnt = cnt + 1 'Add one to last count in employee list End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" Config = vbYesNo + vbQuestion Title = "CONTINUE" Ans2 = MsgBox(Msg, Config, Title) If Ans2 = vbYes Then GoTo ADDEMP End If End Sub Thanks again for your time and effort on this. I did some googling and wonder if I do not need a FileFormat added to the SaveCopyAs line since I am in 2007 and saving in the 97-2003 format? Also, since I am copying and renaming a different workbook than the one that is opened, is SaveCopyAs the correct command? Les "JLGWhiz" wrote: So now, in theory, you should be creating a single workbook for a single name and then asking if there is a another name to process? Did you make sure that your InputBox variable is "Employee" ? (without the quotes of course.) If the variable Employee contains the right value and Workbooks("QA Template.xls") is a valid workbook,. there should be no reason why you would get an error. Why don't you post the complete code again as currently written so I can see if there are any fuzzy hands in it? "WLMPilot" wrote in message ... Oops! In my last post, I failed to mention one important item. I am still getting the same error. As I indicated, I removed the neccessity for an array by moving the SaveCopyAs line to the section in the code just prior to asking if another name needs to be entered. This way, everything that needs to be done with an employee's name is done prior to either exiting or looping back to get another name. Here is the section that holds the code as it currently is: Here is the section of the macro that contains the SaveCopyAs code now so you can see the new placement: End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" The error is indicated as occurring at the point of trying to copy and rename the new workbook. Les "JLGWhiz" wrote: Well, I worked up this little test case and it seemed to work OK. Option Base 1 Sub multWB() Dim i As Long, myPath As String, myNames myPath = ThisWorkbook.Path myNames = Array("Joe", "George", "Ralph") For i = 1 To UBound(myNames) Workbooks("TestBook.xls").SaveCopyAs _ Filename:=myPath & "\" & myNames(i) & ".xls" Next End Sub Chip pointed out something that I overlooked in my earlier post, and that is that using just SaveAs will change the name of the active workbook and effectively closes the original workbook that was copied. But using the SaveCopyAS method leaves the original workbook intact and saves the new workbooks as a closed file. The setup above captures the essence of what your code did to build the array of names, using option base 1, so that when the For ... Next loop runs, it is using the same parameters that your code was using and it worked. I don't know if it was a typo when you made the last post, but in this: Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" the (x) is missing from Employee, to indicate which item of the array. Your actual code probably has it included, but if it doesn't that would keep it from working. "WLMPilot" wrote in message ... Well, back to square one. I have made a couple of changes: 1) Changed SaveAs to SaveCopyAs 2) Removed the need of an array by creating the workbook for the employee at the point the name is placed in the spreadsheet. Workbook("QA Template.xls") does have the file ext .xls. It is in the same directory as the QA Master.xls workbook which is where mypath gets the path. FYI...The explanation previously given that sts the error may have been caused because of the SaveAs renaming the template workbook apparently was not true in this case. The QA Template workbook was never renamed on the first pass of the loop. Here is the section of the macro that contains the SaveCopyAs code now so you can see the new placement: End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" Thanks for your efforts. I await other options. Les "JLGWhiz" wrote: For k = 1 To num Workbooks("QA Template.xls").SaveAs Filename:=myPath & "\" & Employee(k) & ".xls" Next k Les, if this is where you are getting the error, then be sure that your workbook name, including the file extension is correct. You mentioned that you are working in xl07 and saving as xl97 - xl03. If so, your QA Template file could have .xlsx or .xlsm file extension which would throw the error. "WLMPilot" wrote in message ... The workbooks and macros used are being created in Excel 2007, but saved in the 97-2003 format. I have a macro that the boss will use to enter employees and place the names, date added, and count (sequential) into a worksheet. It loops in case there is more than one name to be added. NOTE: The boss will be in workbook(QA Master.xls) Once the boss is through entering names, the macro will then loop and create a workbook, copying the Workbook("QA Template.xls") and renaming it with the employee name that was entered. |
Subscript Out of Range Error
I got it to copy QA Template and rename to match employee name. I had to
open the QA Template workbook to do it and close it at the end. THANKS!!!!!!! Can I switch gears with you for the next question? Within the QA Master workbook, the boss will also have a list of questions (criteria) that will be mirrored in all employee workbooks and the QA Template. If the boss adds a criteria, I need to add it to the QA Template workbook and all employee workbooks so he does not have to go through 80+ workbooks individually. If you can provide the code to do this for one cell in QA Master and place it in QA Template, I believe I can figure out how to get it to the employee workbooks. Also, will I need to open each workbook in order to place the new criteria into it or can they all remain closed? Thanks again for your help and patiences! Les "JLGWhiz" wrote: Les, the macro works as it should down to the line to SaveCopyAs. All variables are properly valued and cells are populated based on those values. The problem appears to be the workbook name. This can be the causes: 1. Spelling error. 2. Workbook is not open. 3. File extension is incorrect (.xls vs .xlsx or .xlsm) 4. File extension is not visible in system but is used in code, or vice versa. Click the FileOpen folder icon and look for this file: QA Template.xls If it does not appear exactly like this, then you need to change the code to reflect the correct name, exactly as it appears in the file folder. If the file is not open when the macro runs, you will need to open the file before attempthing the SaveCopyAs. "WLMPilot" wrote in message ... Here is the code as is (without the array). When the error occurs, I do a mouseover of the variables in the code and they are correct, ie Employee holds the value of the name I entered and mypath is correct. Workbooks("QA Template.xls") is valid and in the current directory. The only workbook opened is the QA Master that the boss would have opened when he clicks the ADD EMPLOYEE commandbutton that executes this macro. Option Base 1 'Add Employee to list and create workbook for employee Private Sub CommandButton2_Click() Dim Employee, Msg, Title As String Dim Config, num, k As Integer Dim Ans1, Ans2, cnt As Integer Dim rng As Range myPath = ThisWorkbook.Path ADDEMP: Employee = InputBox("Enter Employee's Name (First or Middle, Last Name)", "ADD EMPLOYEE") If Employee = "" Or Left(Employee, 1) = " " Then MsgBox ("Invalid Entry. Please enter employee's name.") Employee = "" GoTo ADDEMP End If Msg = "Is the employee's name correctly entered?" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & Employee Config = vbYesNo + vbQuestion Title = "VERIFY ENTRY" Ans1 = MsgBox(Msg, Config, Title) If Ans1 = vbNo Then Employee = "" GoTo ADDEMP End If 'Validation of Entry Complete 'Proceed to place data in cells Set rng = Range("I65536").End(xlUp).Offset(1, 0) rng.Select rng.Value = Employee rng.Offset(0, -1).Value = Date 'Place DATE employee added into cell If rng.Offset(-1, -2).Value = "NUM" Then 'Determine if this is first line in list cnt = 1 'If YES, cnt = 1 Else cnt = rng.Offset(-1, -2).Value 'Pick up last number entered (total employees to date) cnt = cnt + 1 'Add one to last count in employee list End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" Config = vbYesNo + vbQuestion Title = "CONTINUE" Ans2 = MsgBox(Msg, Config, Title) If Ans2 = vbYes Then GoTo ADDEMP End If End Sub Thanks again for your time and effort on this. I did some googling and wonder if I do not need a FileFormat added to the SaveCopyAs line since I am in 2007 and saving in the 97-2003 format? Also, since I am copying and renaming a different workbook than the one that is opened, is SaveCopyAs the correct command? Les "JLGWhiz" wrote: So now, in theory, you should be creating a single workbook for a single name and then asking if there is a another name to process? Did you make sure that your InputBox variable is "Employee" ? (without the quotes of course.) If the variable Employee contains the right value and Workbooks("QA Template.xls") is a valid workbook,. there should be no reason why you would get an error. Why don't you post the complete code again as currently written so I can see if there are any fuzzy hands in it? "WLMPilot" wrote in message ... Oops! In my last post, I failed to mention one important item. I am still getting the same error. As I indicated, I removed the neccessity for an array by moving the SaveCopyAs line to the section in the code just prior to asking if another name needs to be entered. This way, everything that needs to be done with an employee's name is done prior to either exiting or looping back to get another name. Here is the section that holds the code as it currently is: Here is the section of the macro that contains the SaveCopyAs code now so you can see the new placement: End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" The error is indicated as occurring at the point of trying to copy and rename the new workbook. Les "JLGWhiz" wrote: Well, I worked up this little test case and it seemed to work OK. Option Base 1 Sub multWB() Dim i As Long, myPath As String, myNames myPath = ThisWorkbook.Path myNames = Array("Joe", "George", "Ralph") For i = 1 To UBound(myNames) Workbooks("TestBook.xls").SaveCopyAs _ Filename:=myPath & "\" & myNames(i) & ".xls" Next End Sub Chip pointed out something that I overlooked in my earlier post, and that is that using just SaveAs will change the name of the active workbook and effectively closes the original workbook that was copied. But using the SaveCopyAS method leaves the original workbook intact and saves the new workbooks as a closed file. The setup above captures the essence of what your code did to build the array of names, using option base 1, so that when the For ... Next loop runs, it is using the same parameters that your code was using and it worked. I don't know if it was a typo when you made the last post, but in this: Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" the (x) is missing from Employee, to indicate which item of the array. Your actual code probably has it included, but if it doesn't that would keep it from working. "WLMPilot" wrote in message ... Well, back to square one. I have made a couple of changes: 1) Changed SaveAs to SaveCopyAs 2) Removed the need of an array by creating the workbook for the employee at the point the name is placed in the spreadsheet. Workbook("QA Template.xls") does have the file ext .xls. It is in the same directory as the QA Master.xls workbook which is where mypath gets the path. FYI...The explanation previously given that sts the error may have been caused because of the SaveAs renaming the template workbook apparently was not true in this case. The QA Template workbook was never renamed on the first pass of the loop. Here is the section of the macro that contains the SaveCopyAs code now so you can see the new placement: End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" Thanks for your efforts. I await other options. Les "JLGWhiz" wrote: For k = 1 To num Workbooks("QA Template.xls").SaveAs Filename:=myPath & "\" & Employee(k) & ".xls" Next k Les, if this is where you are getting the error, then be sure that your workbook name, including the file extension is correct. You mentioned that you are working in xl07 and saving as xl97 - xl03. If so, your QA Template file could have .xlsx or .xlsm file extension which would throw the error. "WLMPilot" wrote in message ... The workbooks and macros used are being created in Excel 2007, but saved in the 97-2003 format. I have a macro that the boss will use to enter employees and place the names, date added, and count (sequential) into a worksheet. It loops in case there is more than one name to be added. NOTE: The boss will be in workbook(QA Master.xls) Once the boss is through entering names, the macro will then loop and create a workbook, copying the Workbook("QA Template.xls") and renaming it with the employee name that was entered. |
Subscript Out of Range Error
That is the problem. If it is not open then VBA cannot find it, hence Error
9. Workbooks.Open Filename:=myPath & "\QA Template.xls" Assuming that it is in the same folder as the active workbook. If not, you will need to enter the full path lIke: "C:\User\Documents and Settings\xlFiles\QA Template.xls" "WLMPilot" wrote in message ... I have verified spelling of filename. When I right-click on the file (QA Template) and click Properties it indicates File Type as Microsoft Office Excel 97-2003 Worksheet (.xls). When I left-click to highlight the file (without opening), the file information at the bottom also indicates Microsoft Office Excel 97-2003 Worksheet. As I indicated in the previous post, QA Template is NOT open during this time that the macro is executing, so that may be the problem. Not sure why a command to copy and rename a file from a macro cannot be executed without opening that file, but anyway. What code do I need to open that workbook? Les "JLGWhiz" wrote: Les, the macro works as it should down to the line to SaveCopyAs. All variables are properly valued and cells are populated based on those values. The problem appears to be the workbook name. This can be the causes: 1. Spelling error. 2. Workbook is not open. 3. File extension is incorrect (.xls vs .xlsx or .xlsm) 4. File extension is not visible in system but is used in code, or vice versa. Click the FileOpen folder icon and look for this file: QA Template.xls If it does not appear exactly like this, then you need to change the code to reflect the correct name, exactly as it appears in the file folder. If the file is not open when the macro runs, you will need to open the file before attempthing the SaveCopyAs. "WLMPilot" wrote in message ... Here is the code as is (without the array). When the error occurs, I do a mouseover of the variables in the code and they are correct, ie Employee holds the value of the name I entered and mypath is correct. Workbooks("QA Template.xls") is valid and in the current directory. The only workbook opened is the QA Master that the boss would have opened when he clicks the ADD EMPLOYEE commandbutton that executes this macro. Option Base 1 'Add Employee to list and create workbook for employee Private Sub CommandButton2_Click() Dim Employee, Msg, Title As String Dim Config, num, k As Integer Dim Ans1, Ans2, cnt As Integer Dim rng As Range myPath = ThisWorkbook.Path ADDEMP: Employee = InputBox("Enter Employee's Name (First or Middle, Last Name)", "ADD EMPLOYEE") If Employee = "" Or Left(Employee, 1) = " " Then MsgBox ("Invalid Entry. Please enter employee's name.") Employee = "" GoTo ADDEMP End If Msg = "Is the employee's name correctly entered?" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & Employee Config = vbYesNo + vbQuestion Title = "VERIFY ENTRY" Ans1 = MsgBox(Msg, Config, Title) If Ans1 = vbNo Then Employee = "" GoTo ADDEMP End If 'Validation of Entry Complete 'Proceed to place data in cells Set rng = Range("I65536").End(xlUp).Offset(1, 0) rng.Select rng.Value = Employee rng.Offset(0, -1).Value = Date 'Place DATE employee added into cell If rng.Offset(-1, -2).Value = "NUM" Then 'Determine if this is first line in list cnt = 1 'If YES, cnt = 1 Else cnt = rng.Offset(-1, -2).Value 'Pick up last number entered (total employees to date) cnt = cnt + 1 'Add one to last count in employee list End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" Config = vbYesNo + vbQuestion Title = "CONTINUE" Ans2 = MsgBox(Msg, Config, Title) If Ans2 = vbYes Then GoTo ADDEMP End If End Sub Thanks again for your time and effort on this. I did some googling and wonder if I do not need a FileFormat added to the SaveCopyAs line since I am in 2007 and saving in the 97-2003 format? Also, since I am copying and renaming a different workbook than the one that is opened, is SaveCopyAs the correct command? Les "JLGWhiz" wrote: So now, in theory, you should be creating a single workbook for a single name and then asking if there is a another name to process? Did you make sure that your InputBox variable is "Employee" ? (without the quotes of course.) If the variable Employee contains the right value and Workbooks("QA Template.xls") is a valid workbook,. there should be no reason why you would get an error. Why don't you post the complete code again as currently written so I can see if there are any fuzzy hands in it? "WLMPilot" wrote in message ... Oops! In my last post, I failed to mention one important item. I am still getting the same error. As I indicated, I removed the neccessity for an array by moving the SaveCopyAs line to the section in the code just prior to asking if another name needs to be entered. This way, everything that needs to be done with an employee's name is done prior to either exiting or looping back to get another name. Here is the section that holds the code as it currently is: Here is the section of the macro that contains the SaveCopyAs code now so you can see the new placement: End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" The error is indicated as occurring at the point of trying to copy and rename the new workbook. Les "JLGWhiz" wrote: Well, I worked up this little test case and it seemed to work OK. Option Base 1 Sub multWB() Dim i As Long, myPath As String, myNames myPath = ThisWorkbook.Path myNames = Array("Joe", "George", "Ralph") For i = 1 To UBound(myNames) Workbooks("TestBook.xls").SaveCopyAs _ Filename:=myPath & "\" & myNames(i) & ".xls" Next End Sub Chip pointed out something that I overlooked in my earlier post, and that is that using just SaveAs will change the name of the active workbook and effectively closes the original workbook that was copied. But using the SaveCopyAS method leaves the original workbook intact and saves the new workbooks as a closed file. The setup above captures the essence of what your code did to build the array of names, using option base 1, so that when the For ... Next loop runs, it is using the same parameters that your code was using and it worked. I don't know if it was a typo when you made the last post, but in this: Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" the (x) is missing from Employee, to indicate which item of the array. Your actual code probably has it included, but if it doesn't that would keep it from working. "WLMPilot" wrote in message ... Well, back to square one. I have made a couple of changes: 1) Changed SaveAs to SaveCopyAs 2) Removed the need of an array by creating the workbook for the employee at the point the name is placed in the spreadsheet. Workbook("QA Template.xls") does have the file ext .xls. It is in the same directory as the QA Master.xls workbook which is where mypath gets the path. FYI...The explanation previously given that sts the error may have been caused because of the SaveAs renaming the template workbook apparently was not true in this case. The QA Template workbook was never renamed on the first pass of the loop. Here is the section of the macro that contains the SaveCopyAs code now so you can see the new placement: End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" Thanks for your efforts. I await other options. Les "JLGWhiz" wrote: For k = 1 To num Workbooks("QA Template.xls").SaveAs Filename:=myPath & "\" & Employee(k) & ".xls" Next k Les, if this is where you are getting the error, then be sure that your workbook name, including the file extension is correct. You mentioned that you are working in xl07 and saving as xl97 - xl03. If so, your QA Template file could have .xlsx or .xlsm file extension which would throw the error. "WLMPilot" wrote in message ... The workbooks and macros used are being created in Excel 2007, but saved in the 97-2003 format. I have a macro that the boss will use to enter employees and place the names, date added, and count (sequential) into a worksheet. It loops in case there is more than one name to be added. NOTE: The boss will be in workbook(QA Master.xls) Once the boss is through entering names, the macro will then loop and create a workbook, copying the Workbook("QA Template.xls") and renaming it with the employee name that was entered. |
Subscript Out of Range Error
I was thinking a button from the Control Toolbox, but it might be just as
easy to use a Workbook_Open event so that whent the Master workbook is opened it asks if there are updates to be made: 'This would go into the ThisWorkbook code module. Private Sub Workbook_Open() upDt = MsgBox("Do you need to update criteria?", _ vbYesNo + vbQuestion, "CRITERIA UPDATE") If upDt = vbYes Then Sheets("Sheet1").Range("F2").ClearContents '<<<Change sheet? crit = InputBox("Enter the new criteria") Sheets("Sheet1").Range("F2") = crit '<<<Change to desired range End If End Sub By using the event code, you will give the boss a tickler that reminds him to update, whereas, the button would require that he be inspired by some other stimulus to click the button and run the macro. You can work out the sheet name and actual range to suit your purposes. Just substitute them into the code. "WLMPilot" wrote in message ... I got it to copy QA Template and rename to match employee name. I had to open the QA Template workbook to do it and close it at the end. THANKS!!!!!!! Can I switch gears with you for the next question? Within the QA Master workbook, the boss will also have a list of questions (criteria) that will be mirrored in all employee workbooks and the QA Template. If the boss adds a criteria, I need to add it to the QA Template workbook and all employee workbooks so he does not have to go through 80+ workbooks individually. If you can provide the code to do this for one cell in QA Master and place it in QA Template, I believe I can figure out how to get it to the employee workbooks. Also, will I need to open each workbook in order to place the new criteria into it or can they all remain closed? Thanks again for your help and patiences! Les "JLGWhiz" wrote: Les, the macro works as it should down to the line to SaveCopyAs. All variables are properly valued and cells are populated based on those values. The problem appears to be the workbook name. This can be the causes: 1. Spelling error. 2. Workbook is not open. 3. File extension is incorrect (.xls vs .xlsx or .xlsm) 4. File extension is not visible in system but is used in code, or vice versa. Click the FileOpen folder icon and look for this file: QA Template.xls If it does not appear exactly like this, then you need to change the code to reflect the correct name, exactly as it appears in the file folder. If the file is not open when the macro runs, you will need to open the file before attempthing the SaveCopyAs. "WLMPilot" wrote in message ... Here is the code as is (without the array). When the error occurs, I do a mouseover of the variables in the code and they are correct, ie Employee holds the value of the name I entered and mypath is correct. Workbooks("QA Template.xls") is valid and in the current directory. The only workbook opened is the QA Master that the boss would have opened when he clicks the ADD EMPLOYEE commandbutton that executes this macro. Option Base 1 'Add Employee to list and create workbook for employee Private Sub CommandButton2_Click() Dim Employee, Msg, Title As String Dim Config, num, k As Integer Dim Ans1, Ans2, cnt As Integer Dim rng As Range myPath = ThisWorkbook.Path ADDEMP: Employee = InputBox("Enter Employee's Name (First or Middle, Last Name)", "ADD EMPLOYEE") If Employee = "" Or Left(Employee, 1) = " " Then MsgBox ("Invalid Entry. Please enter employee's name.") Employee = "" GoTo ADDEMP End If Msg = "Is the employee's name correctly entered?" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & Employee Config = vbYesNo + vbQuestion Title = "VERIFY ENTRY" Ans1 = MsgBox(Msg, Config, Title) If Ans1 = vbNo Then Employee = "" GoTo ADDEMP End If 'Validation of Entry Complete 'Proceed to place data in cells Set rng = Range("I65536").End(xlUp).Offset(1, 0) rng.Select rng.Value = Employee rng.Offset(0, -1).Value = Date 'Place DATE employee added into cell If rng.Offset(-1, -2).Value = "NUM" Then 'Determine if this is first line in list cnt = 1 'If YES, cnt = 1 Else cnt = rng.Offset(-1, -2).Value 'Pick up last number entered (total employees to date) cnt = cnt + 1 'Add one to last count in employee list End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" Config = vbYesNo + vbQuestion Title = "CONTINUE" Ans2 = MsgBox(Msg, Config, Title) If Ans2 = vbYes Then GoTo ADDEMP End If End Sub Thanks again for your time and effort on this. I did some googling and wonder if I do not need a FileFormat added to the SaveCopyAs line since I am in 2007 and saving in the 97-2003 format? Also, since I am copying and renaming a different workbook than the one that is opened, is SaveCopyAs the correct command? Les "JLGWhiz" wrote: So now, in theory, you should be creating a single workbook for a single name and then asking if there is a another name to process? Did you make sure that your InputBox variable is "Employee" ? (without the quotes of course.) If the variable Employee contains the right value and Workbooks("QA Template.xls") is a valid workbook,. there should be no reason why you would get an error. Why don't you post the complete code again as currently written so I can see if there are any fuzzy hands in it? "WLMPilot" wrote in message ... Oops! In my last post, I failed to mention one important item. I am still getting the same error. As I indicated, I removed the neccessity for an array by moving the SaveCopyAs line to the section in the code just prior to asking if another name needs to be entered. This way, everything that needs to be done with an employee's name is done prior to either exiting or looping back to get another name. Here is the section that holds the code as it currently is: Here is the section of the macro that contains the SaveCopyAs code now so you can see the new placement: End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" The error is indicated as occurring at the point of trying to copy and rename the new workbook. Les "JLGWhiz" wrote: Well, I worked up this little test case and it seemed to work OK. Option Base 1 Sub multWB() Dim i As Long, myPath As String, myNames myPath = ThisWorkbook.Path myNames = Array("Joe", "George", "Ralph") For i = 1 To UBound(myNames) Workbooks("TestBook.xls").SaveCopyAs _ Filename:=myPath & "\" & myNames(i) & ".xls" Next End Sub Chip pointed out something that I overlooked in my earlier post, and that is that using just SaveAs will change the name of the active workbook and effectively closes the original workbook that was copied. But using the SaveCopyAS method leaves the original workbook intact and saves the new workbooks as a closed file. The setup above captures the essence of what your code did to build the array of names, using option base 1, so that when the For ... Next loop runs, it is using the same parameters that your code was using and it worked. I don't know if it was a typo when you made the last post, but in this: Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" the (x) is missing from Employee, to indicate which item of the array. Your actual code probably has it included, but if it doesn't that would keep it from working. "WLMPilot" wrote in message ... Well, back to square one. I have made a couple of changes: 1) Changed SaveAs to SaveCopyAs 2) Removed the need of an array by creating the workbook for the employee at the point the name is placed in the spreadsheet. Workbook("QA Template.xls") does have the file ext .xls. It is in the same directory as the QA Master.xls workbook which is where mypath gets the path. FYI...The explanation previously given that sts the error may have been caused because of the SaveAs renaming the template workbook apparently was not true in this case. The QA Template workbook was never renamed on the first pass of the loop. Here is the section of the macro that contains the SaveCopyAs code now so you can see the new placement: End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" Thanks for your efforts. I await other options. Les "JLGWhiz" wrote: For k = 1 To num Workbooks("QA Template.xls").SaveAs Filename:=myPath & "\" & Employee(k) & ".xls" Next k Les, if this is where you are getting the error, then be sure that your workbook name, including the file extension is correct. You mentioned that you are working in xl07 and saving as xl97 - xl03. If so, your QA Template file could have .xlsx or .xlsm file extension which would throw the error. "WLMPilot" wrote in message ... The workbooks and macros used are being created in Excel 2007, but saved in the 97-2003 format. I have a macro that the boss will use to enter employees and place the names, date added, and count (sequential) into a worksheet. It loops in case there is more than one name to be added. NOTE: The boss will be in workbook(QA Master.xls) Once the boss is through entering names, the macro will then loop and create a workbook, copying the Workbook("QA Template.xls") and renaming it with the employee name that was entered. |
Subscript Out of Range Error
I think one of us missed something. I do not see the code that points to
another workbook. The macro will be executed via a commandbutton. This is an area that he, at some point, decides he wants to add new criteria to the QA process. The code will be very similar to that we just worked with where the QA Template was copied and renamed to match the employee's name. Except in this case, when the boss wants to add new criteria to the list, it will also add it to the list in the QA Template and each employee workbook so that all are up-to-date. I actually will not get the value of the new criteria from a cell, but from the InputBox. I will then place each new criteria into an array until the boss has entered all that he needs to enter. At that point, a loop will execute that will start placing the new additions into each of the workbooks mentioned. Thanks, Les "JLGWhiz" wrote: I was thinking a button from the Control Toolbox, but it might be just as easy to use a Workbook_Open event so that whent the Master workbook is opened it asks if there are updates to be made: 'This would go into the ThisWorkbook code module. Private Sub Workbook_Open() upDt = MsgBox("Do you need to update criteria?", _ vbYesNo + vbQuestion, "CRITERIA UPDATE") If upDt = vbYes Then Sheets("Sheet1").Range("F2").ClearContents '<<<Change sheet? crit = InputBox("Enter the new criteria") Sheets("Sheet1").Range("F2") = crit '<<<Change to desired range End If End Sub By using the event code, you will give the boss a tickler that reminds him to update, whereas, the button would require that he be inspired by some other stimulus to click the button and run the macro. You can work out the sheet name and actual range to suit your purposes. Just substitute them into the code. "WLMPilot" wrote in message ... I got it to copy QA Template and rename to match employee name. I had to open the QA Template workbook to do it and close it at the end. THANKS!!!!!!! Can I switch gears with you for the next question? Within the QA Master workbook, the boss will also have a list of questions (criteria) that will be mirrored in all employee workbooks and the QA Template. If the boss adds a criteria, I need to add it to the QA Template workbook and all employee workbooks so he does not have to go through 80+ workbooks individually. If you can provide the code to do this for one cell in QA Master and place it in QA Template, I believe I can figure out how to get it to the employee workbooks. Also, will I need to open each workbook in order to place the new criteria into it or can they all remain closed? Thanks again for your help and patiences! Les "JLGWhiz" wrote: Les, the macro works as it should down to the line to SaveCopyAs. All variables are properly valued and cells are populated based on those values. The problem appears to be the workbook name. This can be the causes: 1. Spelling error. 2. Workbook is not open. 3. File extension is incorrect (.xls vs .xlsx or .xlsm) 4. File extension is not visible in system but is used in code, or vice versa. Click the FileOpen folder icon and look for this file: QA Template.xls If it does not appear exactly like this, then you need to change the code to reflect the correct name, exactly as it appears in the file folder. If the file is not open when the macro runs, you will need to open the file before attempthing the SaveCopyAs. "WLMPilot" wrote in message ... Here is the code as is (without the array). When the error occurs, I do a mouseover of the variables in the code and they are correct, ie Employee holds the value of the name I entered and mypath is correct. Workbooks("QA Template.xls") is valid and in the current directory. The only workbook opened is the QA Master that the boss would have opened when he clicks the ADD EMPLOYEE commandbutton that executes this macro. Option Base 1 'Add Employee to list and create workbook for employee Private Sub CommandButton2_Click() Dim Employee, Msg, Title As String Dim Config, num, k As Integer Dim Ans1, Ans2, cnt As Integer Dim rng As Range myPath = ThisWorkbook.Path ADDEMP: Employee = InputBox("Enter Employee's Name (First or Middle, Last Name)", "ADD EMPLOYEE") If Employee = "" Or Left(Employee, 1) = " " Then MsgBox ("Invalid Entry. Please enter employee's name.") Employee = "" GoTo ADDEMP End If Msg = "Is the employee's name correctly entered?" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & Employee Config = vbYesNo + vbQuestion Title = "VERIFY ENTRY" Ans1 = MsgBox(Msg, Config, Title) If Ans1 = vbNo Then Employee = "" GoTo ADDEMP End If 'Validation of Entry Complete 'Proceed to place data in cells Set rng = Range("I65536").End(xlUp).Offset(1, 0) rng.Select rng.Value = Employee rng.Offset(0, -1).Value = Date 'Place DATE employee added into cell If rng.Offset(-1, -2).Value = "NUM" Then 'Determine if this is first line in list cnt = 1 'If YES, cnt = 1 Else cnt = rng.Offset(-1, -2).Value 'Pick up last number entered (total employees to date) cnt = cnt + 1 'Add one to last count in employee list End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" Config = vbYesNo + vbQuestion Title = "CONTINUE" Ans2 = MsgBox(Msg, Config, Title) If Ans2 = vbYes Then GoTo ADDEMP End If End Sub Thanks again for your time and effort on this. I did some googling and wonder if I do not need a FileFormat added to the SaveCopyAs line since I am in 2007 and saving in the 97-2003 format? Also, since I am copying and renaming a different workbook than the one that is opened, is SaveCopyAs the correct command? Les "JLGWhiz" wrote: So now, in theory, you should be creating a single workbook for a single name and then asking if there is a another name to process? Did you make sure that your InputBox variable is "Employee" ? (without the quotes of course.) If the variable Employee contains the right value and Workbooks("QA Template.xls") is a valid workbook,. there should be no reason why you would get an error. Why don't you post the complete code again as currently written so I can see if there are any fuzzy hands in it? "WLMPilot" wrote in message ... Oops! In my last post, I failed to mention one important item. I am still getting the same error. As I indicated, I removed the neccessity for an array by moving the SaveCopyAs line to the section in the code just prior to asking if another name needs to be entered. This way, everything that needs to be done with an employee's name is done prior to either exiting or looping back to get another name. Here is the section that holds the code as it currently is: Here is the section of the macro that contains the SaveCopyAs code now so you can see the new placement: End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" The error is indicated as occurring at the point of trying to copy and rename the new workbook. Les "JLGWhiz" wrote: Well, I worked up this little test case and it seemed to work OK. Option Base 1 Sub multWB() Dim i As Long, myPath As String, myNames myPath = ThisWorkbook.Path myNames = Array("Joe", "George", "Ralph") For i = 1 To UBound(myNames) Workbooks("TestBook.xls").SaveCopyAs _ Filename:=myPath & "\" & myNames(i) & ".xls" Next End Sub Chip pointed out something that I overlooked in my earlier post, and that is that using just SaveAs will change the name of the active workbook and effectively closes the original workbook that was copied. But using the SaveCopyAS method leaves the original workbook intact and saves the new workbooks as a closed file. The setup above captures the essence of what your code did to build the array of names, using option base 1, so that when the For ... Next loop runs, it is using the same parameters that your code was using and it worked. I don't know if it was a typo when you made the last post, but in this: Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" the (x) is missing from Employee, to indicate which item of the array. Your actual code probably has it included, but if it doesn't that would keep it from working. "WLMPilot" wrote in message ... Well, back to square one. I have made a couple of changes: 1) Changed SaveAs to SaveCopyAs 2) Removed the need of an array by creating the workbook for the employee at the point the name is placed in the spreadsheet. Workbook("QA Template.xls") does have the file ext .xls. It is in the same directory as the QA Master.xls workbook which is where mypath gets the path. |
Subscript Out of Range Error
Why don't you start a new thread with a more detailed description of what
you want the macro to do. This one is getting a little far down the line, anyhow. Maybe somebody with a better idea than mine will pick up on it. "WLMPilot" wrote in message ... I think one of us missed something. I do not see the code that points to another workbook. The macro will be executed via a commandbutton. This is an area that he, at some point, decides he wants to add new criteria to the QA process. The code will be very similar to that we just worked with where the QA Template was copied and renamed to match the employee's name. Except in this case, when the boss wants to add new criteria to the list, it will also add it to the list in the QA Template and each employee workbook so that all are up-to-date. I actually will not get the value of the new criteria from a cell, but from the InputBox. I will then place each new criteria into an array until the boss has entered all that he needs to enter. At that point, a loop will execute that will start placing the new additions into each of the workbooks mentioned. Thanks, Les "JLGWhiz" wrote: I was thinking a button from the Control Toolbox, but it might be just as easy to use a Workbook_Open event so that whent the Master workbook is opened it asks if there are updates to be made: 'This would go into the ThisWorkbook code module. Private Sub Workbook_Open() upDt = MsgBox("Do you need to update criteria?", _ vbYesNo + vbQuestion, "CRITERIA UPDATE") If upDt = vbYes Then Sheets("Sheet1").Range("F2").ClearContents '<<<Change sheet? crit = InputBox("Enter the new criteria") Sheets("Sheet1").Range("F2") = crit '<<<Change to desired range End If End Sub By using the event code, you will give the boss a tickler that reminds him to update, whereas, the button would require that he be inspired by some other stimulus to click the button and run the macro. You can work out the sheet name and actual range to suit your purposes. Just substitute them into the code. "WLMPilot" wrote in message ... I got it to copy QA Template and rename to match employee name. I had to open the QA Template workbook to do it and close it at the end. THANKS!!!!!!! Can I switch gears with you for the next question? Within the QA Master workbook, the boss will also have a list of questions (criteria) that will be mirrored in all employee workbooks and the QA Template. If the boss adds a criteria, I need to add it to the QA Template workbook and all employee workbooks so he does not have to go through 80+ workbooks individually. If you can provide the code to do this for one cell in QA Master and place it in QA Template, I believe I can figure out how to get it to the employee workbooks. Also, will I need to open each workbook in order to place the new criteria into it or can they all remain closed? Thanks again for your help and patiences! Les "JLGWhiz" wrote: Les, the macro works as it should down to the line to SaveCopyAs. All variables are properly valued and cells are populated based on those values. The problem appears to be the workbook name. This can be the causes: 1. Spelling error. 2. Workbook is not open. 3. File extension is incorrect (.xls vs .xlsx or .xlsm) 4. File extension is not visible in system but is used in code, or vice versa. Click the FileOpen folder icon and look for this file: QA Template.xls If it does not appear exactly like this, then you need to change the code to reflect the correct name, exactly as it appears in the file folder. If the file is not open when the macro runs, you will need to open the file before attempthing the SaveCopyAs. "WLMPilot" wrote in message ... Here is the code as is (without the array). When the error occurs, I do a mouseover of the variables in the code and they are correct, ie Employee holds the value of the name I entered and mypath is correct. Workbooks("QA Template.xls") is valid and in the current directory. The only workbook opened is the QA Master that the boss would have opened when he clicks the ADD EMPLOYEE commandbutton that executes this macro. Option Base 1 'Add Employee to list and create workbook for employee Private Sub CommandButton2_Click() Dim Employee, Msg, Title As String Dim Config, num, k As Integer Dim Ans1, Ans2, cnt As Integer Dim rng As Range myPath = ThisWorkbook.Path ADDEMP: Employee = InputBox("Enter Employee's Name (First or Middle, Last Name)", "ADD EMPLOYEE") If Employee = "" Or Left(Employee, 1) = " " Then MsgBox ("Invalid Entry. Please enter employee's name.") Employee = "" GoTo ADDEMP End If Msg = "Is the employee's name correctly entered?" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & Employee Config = vbYesNo + vbQuestion Title = "VERIFY ENTRY" Ans1 = MsgBox(Msg, Config, Title) If Ans1 = vbNo Then Employee = "" GoTo ADDEMP End If 'Validation of Entry Complete 'Proceed to place data in cells Set rng = Range("I65536").End(xlUp).Offset(1, 0) rng.Select rng.Value = Employee rng.Offset(0, -1).Value = Date 'Place DATE employee added into cell If rng.Offset(-1, -2).Value = "NUM" Then 'Determine if this is first line in list cnt = 1 'If YES, cnt = 1 Else cnt = rng.Offset(-1, -2).Value 'Pick up last number entered (total employees to date) cnt = cnt + 1 'Add one to last count in employee list End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" Config = vbYesNo + vbQuestion Title = "CONTINUE" Ans2 = MsgBox(Msg, Config, Title) If Ans2 = vbYes Then GoTo ADDEMP End If End Sub Thanks again for your time and effort on this. I did some googling and wonder if I do not need a FileFormat added to the SaveCopyAs line since I am in 2007 and saving in the 97-2003 format? Also, since I am copying and renaming a different workbook than the one that is opened, is SaveCopyAs the correct command? Les "JLGWhiz" wrote: So now, in theory, you should be creating a single workbook for a single name and then asking if there is a another name to process? Did you make sure that your InputBox variable is "Employee" ? (without the quotes of course.) If the variable Employee contains the right value and Workbooks("QA Template.xls") is a valid workbook,. there should be no reason why you would get an error. Why don't you post the complete code again as currently written so I can see if there are any fuzzy hands in it? "WLMPilot" wrote in message ... Oops! In my last post, I failed to mention one important item. I am still getting the same error. As I indicated, I removed the neccessity for an array by moving the SaveCopyAs line to the section in the code just prior to asking if another name needs to be entered. This way, everything that needs to be done with an employee's name is done prior to either exiting or looping back to get another name. Here is the section that holds the code as it currently is: Here is the section of the macro that contains the SaveCopyAs code now so you can see the new placement: End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" The error is indicated as occurring at the point of trying to copy and rename the new workbook. Les "JLGWhiz" wrote: Well, I worked up this little test case and it seemed to work OK. Option Base 1 Sub multWB() Dim i As Long, myPath As String, myNames myPath = ThisWorkbook.Path myNames = Array("Joe", "George", "Ralph") For i = 1 To UBound(myNames) Workbooks("TestBook.xls").SaveCopyAs _ Filename:=myPath & "\" & myNames(i) & ".xls" Next End Sub Chip pointed out something that I overlooked in my earlier post, and that is that using just SaveAs will change the name of the active workbook and effectively closes the original workbook that was copied. But using the SaveCopyAS method leaves the original workbook intact and saves the new workbooks as a closed file. The setup above captures the essence of what your code did to build the array of names, using option base 1, so that when the For ... Next loop runs, it is using the same parameters that your code was using and it worked. I don't know if it was a typo when you made the last post, but in this: Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" the (x) is missing from Employee, to indicate which item of the array. Your actual code probably has it included, but if it doesn't that would keep it from working. "WLMPilot" wrote in message ... Well, back to square one. I have made a couple of changes: 1) Changed SaveAs to SaveCopyAs 2) Removed the need of an array by creating the workbook for the employee at the point the name is placed in the spreadsheet. Workbook("QA Template.xls") does have the file ext .xls. It is in the same directory as the QA Master.xls workbook which is where mypath gets the path. |
Subscript Out of Range Error
I will start another thread. I really appreciate your time and effort for
this last issue. I could not proceed until I got this problem solved. Thanks again, Les "JLGWhiz" wrote: Why don't you start a new thread with a more detailed description of what you want the macro to do. This one is getting a little far down the line, anyhow. Maybe somebody with a better idea than mine will pick up on it. "WLMPilot" wrote in message ... I think one of us missed something. I do not see the code that points to another workbook. The macro will be executed via a commandbutton. This is an area that he, at some point, decides he wants to add new criteria to the QA process. The code will be very similar to that we just worked with where the QA Template was copied and renamed to match the employee's name. Except in this case, when the boss wants to add new criteria to the list, it will also add it to the list in the QA Template and each employee workbook so that all are up-to-date. I actually will not get the value of the new criteria from a cell, but from the InputBox. I will then place each new criteria into an array until the boss has entered all that he needs to enter. At that point, a loop will execute that will start placing the new additions into each of the workbooks mentioned. Thanks, Les "JLGWhiz" wrote: I was thinking a button from the Control Toolbox, but it might be just as easy to use a Workbook_Open event so that whent the Master workbook is opened it asks if there are updates to be made: 'This would go into the ThisWorkbook code module. Private Sub Workbook_Open() upDt = MsgBox("Do you need to update criteria?", _ vbYesNo + vbQuestion, "CRITERIA UPDATE") If upDt = vbYes Then Sheets("Sheet1").Range("F2").ClearContents '<<<Change sheet? crit = InputBox("Enter the new criteria") Sheets("Sheet1").Range("F2") = crit '<<<Change to desired range End If End Sub By using the event code, you will give the boss a tickler that reminds him to update, whereas, the button would require that he be inspired by some other stimulus to click the button and run the macro. You can work out the sheet name and actual range to suit your purposes. Just substitute them into the code. "WLMPilot" wrote in message ... I got it to copy QA Template and rename to match employee name. I had to open the QA Template workbook to do it and close it at the end. THANKS!!!!!!! Can I switch gears with you for the next question? Within the QA Master workbook, the boss will also have a list of questions (criteria) that will be mirrored in all employee workbooks and the QA Template. If the boss adds a criteria, I need to add it to the QA Template workbook and all employee workbooks so he does not have to go through 80+ workbooks individually. If you can provide the code to do this for one cell in QA Master and place it in QA Template, I believe I can figure out how to get it to the employee workbooks. Also, will I need to open each workbook in order to place the new criteria into it or can they all remain closed? Thanks again for your help and patiences! Les "JLGWhiz" wrote: Les, the macro works as it should down to the line to SaveCopyAs. All variables are properly valued and cells are populated based on those values. The problem appears to be the workbook name. This can be the causes: 1. Spelling error. 2. Workbook is not open. 3. File extension is incorrect (.xls vs .xlsx or .xlsm) 4. File extension is not visible in system but is used in code, or vice versa. Click the FileOpen folder icon and look for this file: QA Template.xls If it does not appear exactly like this, then you need to change the code to reflect the correct name, exactly as it appears in the file folder. If the file is not open when the macro runs, you will need to open the file before attempthing the SaveCopyAs. "WLMPilot" wrote in message ... Here is the code as is (without the array). When the error occurs, I do a mouseover of the variables in the code and they are correct, ie Employee holds the value of the name I entered and mypath is correct. Workbooks("QA Template.xls") is valid and in the current directory. The only workbook opened is the QA Master that the boss would have opened when he clicks the ADD EMPLOYEE commandbutton that executes this macro. Option Base 1 'Add Employee to list and create workbook for employee Private Sub CommandButton2_Click() Dim Employee, Msg, Title As String Dim Config, num, k As Integer Dim Ans1, Ans2, cnt As Integer Dim rng As Range myPath = ThisWorkbook.Path ADDEMP: Employee = InputBox("Enter Employee's Name (First or Middle, Last Name)", "ADD EMPLOYEE") If Employee = "" Or Left(Employee, 1) = " " Then MsgBox ("Invalid Entry. Please enter employee's name.") Employee = "" GoTo ADDEMP End If Msg = "Is the employee's name correctly entered?" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & Employee Config = vbYesNo + vbQuestion Title = "VERIFY ENTRY" Ans1 = MsgBox(Msg, Config, Title) If Ans1 = vbNo Then Employee = "" GoTo ADDEMP End If 'Validation of Entry Complete 'Proceed to place data in cells Set rng = Range("I65536").End(xlUp).Offset(1, 0) rng.Select rng.Value = Employee rng.Offset(0, -1).Value = Date 'Place DATE employee added into cell If rng.Offset(-1, -2).Value = "NUM" Then 'Determine if this is first line in list cnt = 1 'If YES, cnt = 1 Else cnt = rng.Offset(-1, -2).Value 'Pick up last number entered (total employees to date) cnt = cnt + 1 'Add one to last count in employee list End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" Config = vbYesNo + vbQuestion Title = "CONTINUE" Ans2 = MsgBox(Msg, Config, Title) If Ans2 = vbYes Then GoTo ADDEMP End If End Sub Thanks again for your time and effort on this. I did some googling and wonder if I do not need a FileFormat added to the SaveCopyAs line since I am in 2007 and saving in the 97-2003 format? Also, since I am copying and renaming a different workbook than the one that is opened, is SaveCopyAs the correct command? Les "JLGWhiz" wrote: So now, in theory, you should be creating a single workbook for a single name and then asking if there is a another name to process? Did you make sure that your InputBox variable is "Employee" ? (without the quotes of course.) If the variable Employee contains the right value and Workbooks("QA Template.xls") is a valid workbook,. there should be no reason why you would get an error. Why don't you post the complete code again as currently written so I can see if there are any fuzzy hands in it? "WLMPilot" wrote in message ... Oops! In my last post, I failed to mention one important item. I am still getting the same error. As I indicated, I removed the neccessity for an array by moving the SaveCopyAs line to the section in the code just prior to asking if another name needs to be entered. This way, everything that needs to be done with an employee's name is done prior to either exiting or looping back to get another name. Here is the section that holds the code as it currently is: Here is the section of the macro that contains the SaveCopyAs code now so you can see the new placement: End If rng.Offset(0, -2).Value = cnt 'cnt serves as a count on number of employees in list. 'Create workbook for each employee entered Workbooks("QA Template.xls").SaveCopyAs Filename:=myPath & "\" & Employee & ".xls" Msg = "Do you want to enter another employee?" The error is indicated as occurring at the point of trying to copy and rename the new workbook. Les "JLGWhiz" wrote: Well, I worked up this little test case and it seemed to work OK. Option Base 1 Sub multWB() Dim i As Long, myPath As String, myNames myPath = ThisWorkbook.Path myNames = Array("Joe", "George", "Ralph") |
| All times are GMT +1. The time now is 09:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com