Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Feb 2014
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with Copying Sheet from One Workbook to Another Workbook using Excel VBA

    I am new to VBA. I have folllowing code I am trying to use to copy the Sheet from One to Another but I get error "Run-time error '9': Subscript out of range.

    Sub Copy_sh()
    Dim SDrv As String
    Dim DDrv As String
    Dim Sfname As String
    Dim Dfname As String

    SDrv = "Y:\Main\"
    Sfname = "Mailing Lists.xlsx"

    DDrv = "C:\Test\"
    Dfname = "Test 05FEB2014.xlsx"

    Workbooks.Open (SDrv & Sfname)
    ' Windows(SDrv & Sfname).Activate
    Workbooks(DDrv & Dfname).Sheets("Mailing Lists").Delete
    Sheets("List").Copy Before:=Workbooks(DDrv & Dfname).Sheets("List")

    Workbooks(SDrv & Sfname).Close
    Workbooks(DDrv & Dfname).Save
    Workbooks(DDrv & Dfname).Close

    End Sub

  2. #2
    New Lounger
    Join Date
    Feb 2014
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am sorry below is the code:
    Code:
    Sub Copy_sh()
    Dim SDrv As String
    Dim DDrv As String
    Dim Sfname As String
    Dim Dfname As String
    
    SDrv = "Y:\Main\"
    Sfname = "Mailing Lists.xlsx"
    
    DDrv = "C:\Test\"
    Dfname = "Test 05FEB2014.xlsx"
    
    Workbooks.Open (SDrv & Sfname)
    ' Windows(SDrv & Sfname).Activate
    Workbooks(DDrv & Dfname).Sheets("List").Delete
    Sheets("List").Copy Before:=Workbooks(DDrv & Dfname).Sheets("List")
    
    Workbooks(SDrv & Sfname).Close
    Workbooks(DDrv & Dfname).Save
    Workbooks(DDrv & Dfname).Close
    
    End Sub
    Last edited by RetiredGeek; 2014-02-05 at 09:49. Reason: Added code tags

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    pp2014,

    Welcome to the Lounge as a new poster.

    Which workbook contains the VBA code you have shown above?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Which line causes the error? The error suggests the file, sheet, range, you are referring to is misnamed as it does not exist...

    Steve
    PS The line:
    Sheets("List")...

    Has no explicit listing for the workbook the worksheet "List" is from and so excel assumes it is in the activeworkbook. you may want an explicit reference to a workbook here even if this line is not the problem...
    Last edited by sdckapr; 2014-02-05 at 09:56.

  5. #5
    New Lounger
    Join Date
    Feb 2014
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    VBA Code is in another workbook named test.xlsm.
    I am getting Run-time error '9' Subscript out of range at the following line ( I commented the line above which was also giving me the same error)

    Workbooks(DDrv & Dfname).Sheets("List").Delete

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Try:
    Workbooks(Dfname).Sheets("List").Delete

    The drive is not part of the workbooks name (change in the other lines as well). It would be used for opening them, but not for working with them while open.

    Steve

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    pp2014,

    You have opened the Source workbook but have not opened the Destination workbook. The Destination WB must be open to operate on it (e.g. .delete & .copy) . HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    New Lounger
    Join Date
    Feb 2014
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Steve,

    I changed to the following: It works but it prompts whether to delete or cancel.

    Workbooks(Dfname).Sheets("List").Delete

    I get the message "Data may exist in the sheet selected for deletion. To permanently delete the data, press delete". If I do not want this message then what is the code I need to Add.

    Also Once I press Enter to delete the sheet.

    I get the I get "Run-time error '9': Subscript out of range on the line below:
    Sheets("List").Copy Before:=Workbooks(Dfname).Sheets("List")

  9. #9
    New Lounger
    Join Date
    Feb 2014
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I changed my code to following: everything works fine but I get error on I get the I get "Run-time error '9': Subscript out of range on the line below:
    Sheets("List").Copy Before:=Workbooks(Dfname).Sheets("List")

    Sub Copy_sh()
    Dim SDrv As String
    Dim DDrv As String
    Dim Sfname As String
    Dim Dfname As String

    SDrv = "Y:\Main\"
    Sfname = "Mailing Lists.xlsx"

    DDrv = "C:\Test\"
    Dfname = "Test 05FEB2014.xlsx"


    Application.DisplayAlerts = False

    Workbooks.Open (SDrv & Sfname)
    Windows(Sfname).Activate
    Workbooks.Open (DDrv & Dfname)
    Workbooks(Dfname).Sheets("List").Delete
    Sheets("List").Copy Before:=Workbooks(Dfname).Sheets("List")

    Workbooks(SDrv & Sfname).Close
    Workbooks(DDrv & Dfname).Save
    Workbooks(DDrv & Dfname).Close
    Application.DisplayAlerts = True
    End Sub

  10. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    pp2014,

    Give this code a try it worked for me. Please comment out the lines with my drives/file names and uncomment the ones with yours.
    Code:
    Option Explicit
    
    Sub Copy_sh()
    
       Dim SDrv   As String
       Dim DDrv   As String
       Dim Sfname As String
       Dim Dfname As String
       Dim wkbSrc As Workbook
       Dim wkbDst As Workbook
    
    '   SDrv = "Y:\Main\"
    '   Sfname = "Mailing Lists.xlsx"
    '
    '   DDrv = "C:\Test\"
    '   Dfname = "Test 05FEB2014.xlsx"
       
       SDrv = "G:\BEKDocs\Excel\Test\"
       Sfname = "TestSource.xlsx"
    
       DDrv = "G:\BEKDocs\Excel\Test\"
       Dfname = "TestDestination.xlsx"
    
        Set wkbSrc = Workbooks.Open(SDrv & Sfname)
        Set wkbDst = Workbooks.Open(DDrv & Dfname)
            
        Application.DisplayAlerts = False
    
        wkbDst.Sheets("List").Delete   'Delete List sheet from Dest
        wkbSrc.Sheets("List").Copy After:=wkbDst.Sheets(1)
        
        wkbSrc.Close
        With wkbDst
            .Save
            .Close
        End With
    
    End Sub
    I didn't know if the Destination workbook contained any other sheets so I had it copy after Sheet1. You of course can change this to another sheet name but it can not be "List" since you have just deleted this sheet! HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #11
    New Lounger
    Join Date
    Feb 2014
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It worked !! Thanks a lot for the help....

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    RG,
    You could reduce the last 4 lines [with / end with construction] to the single line:
    wkbDst.Close (true)

    Also I would add the line
    Application.DisplayAlerts = True

    Aftere the delete line, just to make sure it gets turned back on.

    Steve

  13. #13
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Here's a routine to copy a complete tab from one workbook to a new one
    Code:
    Function copytab(tabtocopy, savename)
    '
    ' Copies a complete tab to a new filename and closes it off
    '
        tabtocopy.Select
        tabtocopy.Copy
        Application.DisplayAlerts = False
        ActiveWorkbook.Close savechanges:=True, Filename:=savename
        Application.DisplayAlerts = True
        copytab = 1
        
    End Function
    And here's how I use it

    Code:
            Set finputbook = Workbooks.Open(Filename:=Finputname, UpdateLinks:=0, ReadOnly:=True, Format:=5)
            Set wfdata = Worksheets("Forecast") ' where the input data lives
    '
    '*** and copy it to a new file
    '
            Foutputname = "c:\a new file.xlsx"
    '
    '*** first - delete it if it's there
    '
            If fs.fileexists(Foutputname) Then fs.deletefile (Foutputname) ' delete the output file
            OK = copytab(wfdata, Foutputname) ' copy the first tab to a new file and close it
            finputbook.Close savechanges:=False ' close the original
    I hope this helps

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •