out of range

[mprice]

I'm trying to imprort a sheet from another (closed, static name) workbook into my active workbook (variable) and make it the first sheet. The name of the first sheet in the active workbook will be variable.

I'm here but recieving a subscript out of range alarm.

Workbooks("G:\New G Drive\Data\MIR_History\History_Sheet_Template.xls").Sheets("MIR History").Copy _
Before:=ActiveWorkbook.Sheets(1)

any help is appreciated.

 

[Joe4]

I have been watching this thread, and see that no one has replied yet. I have kind of a "brute force" method that works. Since no one has replied yet, I will post it here. Maybe someone will come along and post a more elegant solution. If not, at least you should have a viable work-around.

Sub MyCopySheet()
    
    Dim MyDestWB As Workbook
    Dim MySourceWB As Workbook
    
'   Capture current workbook
    Set MyDestWB = ActiveWorkbook
    
'   Insert blank sheet at before first page
    Sheets(1).Select
    Sheets.Add
    
'   Open source workbook and capture it
    Workbooks.Open Filename:="G:\New G Drive\Data\MIR_History\History_Sheet_Template.xls"
    Set MySourceWB = ActiveWorkbook
    
'   Copy sheet to destination workbook
    Sheets("MIR History").Activate
    Cells.Copy
    MyDestWB.Activate
    Range("A1").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
'   Close source workbook
    MySourceWB.Close
 
End Sub

 

[mprice]

I made a hybrid of what you showed me cause I'm eventualy going to add a macro into the sheet i'm going to be copying. I came pretty close until i put in the "MyDestWB.Sheets _
(1)" I'm doing something wrong there.


Dim MyDestWB As Workbook
Dim MySourceWB As Workbook

' Capture current workbook
Set MyDestWB = ActiveWorkbook
ChDir "G:\New G Drive\Data\MIR_History"
Workbooks.Open Filename:="G:\New G Drive\Data\MIR_History\History_Sheet.xls"
Set MySourceWB = ActiveWorkbook
Sheets("MIR History").Select
Sheets("MIR History").Copy Before:=MyDestWB.Sheets _
(1)
Windows("History_Sheet.xls").Activate
ActiveWindow.Close

 

[shg]

What happens when you run the code?

Try this:

Sub x()
    Dim wkb         As Workbook
 
    Set wkb = ActiveWorkbook
    With Workbooks.Open(Filename:="G:\New G Drive\Data\MIR_History\History_Sheet.xls")
        .Worksheets("MIR History").Copy Before:=wkb.Worksheets(1)
        .Close SaveChanges:=False
    End With
End Sub

Are you sure "MIR History" is the name of the worksheet?

 

[mprice]

It gives me a
Run-time error'-2147221080(800401a8)':
Automation error

it was running right up untill i changed out the copy destination from a static address to MyDestWB.

 

[mprice]

Thanks shg That ran!

 

[shg]

All's well that ends well, you're welcome.