Problems copying Excel Worksheet.......

I am using the following code to copy a worksheet from one workbook to another, but keep receiving a "Subscript out of range" error. I have double checked the names of the worksheets and they are correct. Any ideas?

Code:

---

Sub COPY_FROM_MainSched_TO_DoorSched()

Dim SourcePath, DestPath, SourceFile, DestFile As String

'set variables
SourcePath = "H:\schedule\"
DestPath = "H:\shared_tools\"
SourceFile = "Cabot, S.xls"
DestFile = "door_inventory.xls"
SourceWbk = SourcePath & SourceFile
DestWbk = DestPath & DestFile

    Range("A1").Select
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Workbooks.Open Filename:=SourceWbk
    Workbooks("SourceWbk").Sheets("Log (2)").Copy After:=Workbooks("DestWbk").Sheets("Menu")
    Windows(DestWbk).Activate
   
 

 
End Sub

---

Error occurs on this line: "Workbooks("SourceWbk").Sheets("Log (2)").Copy After:=Workbooks("DestWbk").Sheets("Menu")

Your placing double quotes around your variable amking it literal.

VB Code:

Workbooks([b]"[/b]SourceWbk[b]"[/b]).Sheets("Log (2)").Copy After:=Workbooks([b]"[/b]DestWbk[b]"[/b]).Sheets("Menu")
 
'Should be...
Workbooks(SourceWbk).Sheets("Log (2)").Copy After:=Workbooks(DestWbk).Sheets("Menu")

Ahh, thanks a lot. I'm an idiot! :) You can tell I haven't done any programming in a long while. Thanks again.

hmm... still getting the same error, even after removing the double quotes. I looked at the examples in the MSDN Knowledge Base, I don't see why it's not working. It didn't think I'd have this much trouble trying to copy a worksheet from one workbook to another. I've also tried:

Code:

---

sourcewbk.Worksheets("Log (2)").Copy After:= destwbk.Worksheets.("menu")

---

Could it be the comma in the file name? What OS and Excel version are you running?

Cabot, S.xls

Windows XP & Windows NT 4.0 With Office 2000 & 2003. I am able to open the file though, so why not copy from it? I set a breakpoint & step through, & the workbook opens just fine.

Maybe its a timing issue when your running in real time? It this running on the Workbook_Open event?

Code:

---

Sub COPY_FROM_MainSched_TO_DoorSched()

    Dim SourcePath as String, DestPath as String
    dim SourceFile as String, DestFile As String
    dim wrkSrc as workbook, shtSrc as worksheet
    dim wrkDest as workbook, shtDest as worksheet


'set variables
    SourcePath = "H:\schedule\"
    DestPath = "H:\shared_tools\"
    SourceFile = "Cabot, S.xls"
    DestFile = "door_inventory.xls"

    on error resume next

    SourceWbk = SourcePath & SourceFile
    DestWbk = DestPath & DestFile

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False


    set wrkSrc = Workbooks.Open(SourceWbk)
    set wrkDest = Workbooks.Open(DestWbk)

'---- this should in theory do the copy
'---- put a break point on the next line - use f8 to step through
    wrk.Sheets("Log (2)").Copy After:=wrkDest.Sheets("Menu")
   
'---- not sure this will work!
    wrkDest.Activate

    if err.number=0 then
        msgbox "Complete"
    else 
        msgbox "Error : " & err.number & vbcrlf & err.description,vbokonly,"Error"
    end if
 
'---- clean up
    set wrkSrc = nothing
    set wrkDest = nothing
 
End Sub

---

Try that?

Thanks, I'll give that a try.

Still no luck with several variations of the code. I think I see part of the problem.

Code:

---

Sub COPY_FROM_MainSched_TO_DoorSched()

Dim SourcePath, DestPath, SourceFile, DestFile As String
Dim SourceWbk, DestWbk as String



'set variables
SourcePath = "H:\schedule\"
DestPath = "H:\shared_tools\"
SourceFile = "Cabot, S.xls"
DestFile = "door_inventory.xls"
SourceWbk = SourcePath & SourceFile
DestWbk = DestPath & DestFile

'Disable alerts & screen updating
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
   
'Open the source workbook
    Workbooks.Open Filename:=SourceWbk
   
'Copy the sheet from the source workbook to the Destination workbook
    Workbooks(SourceWbk).Sheets("Log(2)").Copy After:=Workbooks(DestWbk).Sheets("Menu")
    Windows(DestWbk).Activate
   
 
End Sub

---

In order to build the path to the location of the file, I have to use strings. Once I get the path, I somehow need to convert SourceWbk & DestWbk to Workbook instead of string. Any ideas? It's the wrong type of variable in the copy statement that may be causing the problem, I think.

Is the destination workbook open?

What is the exact Full name of both the Source workbook and the Destination workbook?

Like:
H:\Schedule\DestFIle.xls 'Destination
H:\BlahTools\SourceFile 'Source

Are either of these workbooks the file containing the code your running?

Doubt it will help but when you dim your vars that way it actually dims them as a Variant when you dont specify.

VB Code:

Dim SourcePath, DestPath, SourceFile, DestFile As String
Dim SourceWbk, DestWbk as String
'Should be...
Dim SourcePath As String, DestPath As String, SourceFile As String, DestFile As String
Dim SourceWbk As String, DestWbk as String

Try this, it assumes that Door_Inventory is the workbook with the code:

VB Code:

Option Explicit
 
Sub COPY_FROM_MainSched_TO_DoorSched()
 
Dim SourcePath As String
Dim DestPath As String
Dim SourceFile As String
Dim DestFile As String
Dim SourceWbk As String
Dim DestWbk As String
 
 
'set variables
SourcePath = "H:\schedule\"
 
DestPath = "H:\shared_tools\"
 
SourceFile = "Cabot, s.xls"
 
DestFile = "door_inventory.xls"
 
SourceWbk = SourcePath & SourceFile
 
DestWbk = DestPath & DestFile
 
'Disable alerts & screen updating
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
'Open the source workbook
    Workbooks.Open SourceWbk
    
'Copy the sheet from the source workbook to the Destination workbook
    ActiveWorkbook.Sheets("Log(2)").Copy after:=ThisWorkbook.Sheets("Menu")
    Windows(DestFile).Activate
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub