leal72
11-20-2009, 02:03 PM
Having a problem with this part of my code. When I test it on my PC it works fine but the PC of the end user, he gets an error "Script out of range"
' User Input box to name workbook
WkbName = InputBox("Name the workbook to store test data", "WORKBOOK NAME")
' Create new workbook with single worksheet
Set NewWkb = Workbooks.Add(xlWBATWorksheet)
' Exit if dialog box canceled
If WkbName <> "" Then
NewWkb.SaveAs FileName:=WkbName ' Save workbook with InputBox info
Else
NewWkb.Close ' Close created workbook if InputBox is cancelled
Exit Sub
End If
' Rename Sheet1
Workbooks(WkbName).Sheets(1).Name = "SummaryData" <<<script out of range error here
' Select Raw Data (*.csv) files
Files = Application.GetOpenFilename _
("Text Files (*.txt), *.txt", _
Title:="Select which data files to import", MultiSelect:=True)
' Exit macro if no files were selected
If Not IsArray(Files) Then
MsgBox "No file was selected."
Exit Sub
End If
' Loop through selected files and add to Results workbook
For z = LBound(Files) To UBound(Files)
Workbooks.OpenText FileName:=Files(z), _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
' store the workbook name in variable "temp"
WsName = ActiveSheet.Name
' Moves active sheet to named workbook
Sheets(WsName).Move after:=Workbooks(WkbName).Sheets("SummaryData")
' User Input box to name workbook
WkbName = InputBox("Name the workbook to store test data", "WORKBOOK NAME")
' Create new workbook with single worksheet
Set NewWkb = Workbooks.Add(xlWBATWorksheet)
' Exit if dialog box canceled
If WkbName <> "" Then
NewWkb.SaveAs FileName:=WkbName ' Save workbook with InputBox info
Else
NewWkb.Close ' Close created workbook if InputBox is cancelled
Exit Sub
End If
' Rename Sheet1
Workbooks(WkbName).Sheets(1).Name = "SummaryData" <<<script out of range error here
' Select Raw Data (*.csv) files
Files = Application.GetOpenFilename _
("Text Files (*.txt), *.txt", _
Title:="Select which data files to import", MultiSelect:=True)
' Exit macro if no files were selected
If Not IsArray(Files) Then
MsgBox "No file was selected."
Exit Sub
End If
' Loop through selected files and add to Results workbook
For z = LBound(Files) To UBound(Files)
Workbooks.OpenText FileName:=Files(z), _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
' store the workbook name in variable "temp"
WsName = ActiveSheet.Name
' Moves active sheet to named workbook
Sheets(WsName).Move after:=Workbooks(WkbName).Sheets("SummaryData")