Simple copy worksheet not working...

[numberchomper]

I am receiving an error "Subscript out of range" when trying to copy a worksheet from one workbook to another. The workbooks are selected by the user. I then want to copy the worksheet from workbook #2 and place it at the end of the worksheet in workbook #1. Below is my code with the offending line in red. (Please note that there is more code following this line, but I did not think it would be relevant.) Can someone please help me find the problem?


Sub CopySheetTest()
ChDrive "L:\"
ChDir "L:\SharedData\Houston\FinRpt\Corp Acct\"
FileToOpen = Application.GetOpenFilename _
(Title:="Please choose the current Trial Balance exported from SAP R3", _
FileFilter:="DAT Files *.dat (*.dat),")
If FileToOpen = False Then
MsgBox "No file specified.", vbExclamation, "Error"
Else
Workbooks.OpenText Filename:=FileToOpen _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _
TrailingMinusNumbers:=True
End If
Range("A1").Select
Sheets(1).Select
File1Sheet1 = Sheets(1).Name
ChDrive "L:\"
ChDir "L:\SharedData\Houston\FinRpt\Corp Acct\"
FileToOpen2 = Application.GetOpenFilename _
(Title:="Please choose the current Qtrly Income Stmt exported from SAP R3", _
FileFilter:="DAT Files *.dat (*.dat),")
If FileToOpen2 = False Then
MsgBox "No file specified.", vbExclamation, "Error"
Else
Workbooks.OpenText Filename:=FileToOpen2 _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
End If
Sheets(1).Select
File2Sheet1 = Sheets(1).Name
Application.CutCopyMode = False
ActiveSheet(File2Sheet1).Copy After:=Workbooks(FileToOpen).Sheets(File1Sheet1)
End Sub

 

[Norie]

ActiveSheet doesn't take any arguments so I don't know why you have File2Sheet1 in there.

Also using GetOpenFilename is going to return the filename and path, Workbooks only requires the name of the workbook you want to refer to, not the path.

 

[numberchomper]

Thank you for your response!

I have replaced "ActiveSheet (File2Sheet1)" with "ActiveSheet" only. However, I still get the same error message.

What would I use to replace "GetOpenFilename" to get only the workbook name?

 

[Norie]

You wouldn't replace GetOpenFilename to do that, and if you did how would the user select the file to open in the first place?

Try creating references to the workbooks when you open them.

If you were opening an actual workbook rather than a text file you could just do that like this.

Set wbOpen = Workbooks.Open(FileName:=FileToOpen)

But since you are using OpenText you might need something like this.

Workbooks.OpenText Filename:=FileToOpen _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _
TrailingMinusNumbers:=True
 
Set wbOpen = ActiveWorkbook

It might also help if you could explain in words what you are actually trying to do.

If you are trying to import text files into a spreadsheet there might be a better approach.

You can use Data>Get external data... Import Text File...

And with that you can specify the destination for the data.

 

[numberchomper]

Thanks again for the response!

I am trying to merge the information contained in three .DAT files into one workbook that will be saved as an Excel workbook. The .DAT files are exports out of another system, SAP. This is the only method available to us for gathering the data. The three files .DAT files containg a trial balance, balance sheet, and income statement in comma delimited format. I want to open the trial balance (FileToOpen1), then open the balance sheet (FileToOpen2). I want to then copy the only worksheet in FileToOpen2 and place behind the only worksheet in FileToOpen1. I then want to open the income statement (FileToOpen3) and copy the only worksheet in FileToOpen3 and place behind the worksheet copied from FileToOpen2 in FileToOpen1. I will then save FileToOpen1 as a new Excel workbook file.

All of the .DAT files will have various names, therefore, they must be selected by the user. Once the .DAT files are opened, the name of the file is automatically assigned to the only worksheet in the workbook. Therefore, the worksheet names will vary as well. In essence, I want a user to pick three .DAT files, copy all of the worksheets into one workbook, and save as an Excel workbook.

I appreciate your help on this! If you need more information, please ask!

 

[numberchomper]

Bump!