Moving a sheet from a closed spreadsheet & making it part of an open one.

  • Thread starter Thread starter mjwillyone
  • Start date Start date
M

mjwillyone

Friends,

I am attempting to take "sheet1" from the following closed Excel
spreadsheet "C:\Automated\achchecks.xls" and make it "sheet1" of my
open spreadsheet. Incidently, I do not have a "sheet1" in the open
spreadsheet.

Can anyone help? I have looked throughout this forum and have found
some similar situations to my own but not entirely the same. In fact
I have tried some of them and get subscript errors.

Thanks,
Mike
 
Mike,

This actually takes a copy and does not move it.

Sub testit()
Dim wkb As Workbook
Set wkb = Workbooks.Open("C:\Automated\achchecks.xls")
wkb.Worksheets("Sheet1").Copy Before:=ThisWorkbook.Worksheets(1)
wkb.Close SaveChanges:=False
End Sub

Rob
 
This will work if both open

Sub CopyWB()
Workbooks("TV1.xls").Sheets("Sheet1").Move _
Before:=ActiveWorkbook.Sheets("Sheet2")
End Sub
 
One way would be to open "achchecks.xls" long enough to copy the sheet over
and then close it again. Here's how:
Replace "CurrFile.xls" with the name of your currently open file.

Sub SheetFromClosedWbook()
Application.ScreenUpdating = False
Workbooks.Open Filename:="C:\Automated\achchecks.xls"
Sheets("Sheet1").Copy Before:=Workbooks("CurrFile.xls").Sheets(1)
Windows("C:\Automated\achchecks.xls").Activate
ActiveWindow.Close
Application.ScreenUpdating = True
End Sub

HTH
 
Back
Top