Move Sheet to Another Workbook
Posted by Jo on January 28, 2000 9:13 AM
I have several files regularly exported into Excel from a db app that I wish to consolidate into a single workbook as separate worksheets. Each of the exported files has one worksheet. Worked ok until I wanted to use variables for all of the file names to make changes easier. I open the files ok but get a run-time error "Workbooks method of application class failed" when trying to move a worksheet from one workbook to the "consolidation" workbook. Using the index number to refer to the sheet so that I do not need to know worksheets by name. What am I doing wrong?? (I am a beginner.) Thanks in advance...
Option Base 1
Static myPath As String
Static myConsolidationFile As String
Static myFile(4) As String
Static myFileDate(4) As Date
myPath = "\\usahouu6\Data\4a20\Shared\CMS Reports\TestArea\"
myConsolidationFile = "Open SR Report.xls"
myFile(1) = "Open OPR SRs.xls"
myFile(2) = "Open All SW Depts.xls"
myFile(3) = "Open All HW Depts.xls"
myFile(4) = "Open HEI.xls"
'create new file to consolidation misc data files into
Workbooks.Add.SaveAs FileName:="" & myPath & myConsolidationFile, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
'for each misc file defined, get its date, open it, and move its worksheet to the consolidation file
For n = LBound(myFile) To UBound(myFile)
myFileDate(n) = FileDateTime("" & myPath & myFile(n)) 'get file date
MsgBox "File and Date is " & myFile(n) & myFileDate(n)
Workbooks.Open FileName:="" & myPath & myFile(n) 'open file
Sheets(1).Move Before:=Workbooks("" & myPath & myFile(n)).Sheets(1)