MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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...

Sample code:

Option Base 1
Sub CreateNewOpenTest()
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:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
'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)


End Sub

Posted by Ivan Moala on January 28, 2000 2:57 PM


Hi Jo
try replacing your last line with this;

Sheets(1).Copy Before:=Workbooks(myConsolidationFile).Sheets(1)

Note: the Copy & not move, and the close routine
as you may want the books to be closed after.
Your routine only copies Sheet(1) over is this what you wanted ??