Simple copy worksheet not working...

numberchomper

New Member
Joined
Aug 19, 2009
Messages
13
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,410
Office Version
365
Platform
Windows
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

New Member
Joined
Aug 19, 2009
Messages
13
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,410
Office Version
365
Platform
Windows
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.
Code:
Set wbOpen = Workbooks.Open(FileName:=FileToOpen)
But since you are using OpenText you might need something like this.
Code:
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

New Member
Joined
Aug 19, 2009
Messages
13
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!
 

Forum statistics

Threads
1,086,235
Messages
5,388,624
Members
402,127
Latest member
Jemx

Some videos you may like

This Week's Hot Topics

Top