Results 1 to 6 of 6

How to move several worksheets in one workbook to several different workbooks

Threaded View

  1. #1
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    458

    How to move several worksheets in one workbook to several different workbooks

    Hello everyone. I have worksheet, named "105", that contains several worksheets. I would like to move those worksheets to several other workbooks, with various names. Please see the code below:

    Sub Disclosure_Generation()
        Const sFileInp1 As String = "105.xlsm"
        Const sFileInp2 As String = "Borrowing_Portfolio_Bonds.xlsm"
        Const sFileInp3 As String = "Borrowing_Portfolio_Swaps.xlsm"
        Const sFileInp4 As String = "Client_Operations.xlsm"
        Const sFileInp5 As String = "Other_Equity.xlsm"
        Dim sPath1      As String
        Dim sPath2      As String
        Dim wb1         As Workbook
        Dim wb2         As Workbook
        Dim wb3         As Workbook
        Dim wb4         As Workbook
        Dim wb5         As Workbook
          
        sPath1 = fPath & fDate & "_157\105_Reports\"
        sPath2 = fPath & fDate & "_157\157_Reports\IBRD_Disclosure\"
        
        Set wb2 = Workbooks.Open(sPath2 & sFileInp2)
        Set wb3 = Workbooks.Open(sPath2 & sFileInp3)
        Set wb4 = Workbooks.Open(sPath2 & sFileInp4)
        Set wb5 = Workbooks.Open(sPath2 & sFileInp5)
        Set wb1 = Workbooks.Open(sPath1 & sFileInp1)
        
        Sheets("Bonds").Move Before:=Workbooks("Borrowing_Portfolio_Bonds.xlsm"). _
            Sheets(1)
        Sheets("Bond_Details").Move Before:=Workbooks( _
            "Borrowing_Portfolio_Bonds.xlsm").Sheets(1) <---------- Error
            
        Sheets("Swaps").Move Before:=Workbooks("Borrowing_Portfolio_Swaps.xlsm"). _
            Sheets(1)
        Sheets("Swap_Details").Move Before:=Workbooks( _
            "Borrowing_Portfolio_Swaps.xlsm").Sheets(1)
        
        Sheets("Client_Operations").Move Before:=Workbooks("Client_Operations.xlsm"). _
            Sheets(1)
        Sheets("Client_Operations_Details").Move Before:=Workbooks( _
            "Client_Operations.xlsm").Sheets(1)
            
        Sheets("Other_Equity").Move Before:=Workbooks("Other_Equity.xlsm"). _
            Sheets(1)
        Sheets("Client_Operations_Details").Move Before:=Workbooks( _
            "Other_Equity.xlsm").Sheets(1)
            
        wb1.Close SaveChanges:=True
        wb2.Close SaveChanges:=True
        wb3.Close SaveChanges:=True
        wb4.Close SaveChanges:=True
        wb5.Close SaveChanges:=True
    
    End Sub
    I am getting an error, "subscript out of range", and I not sure of a correct fix. Any suggestions. Thank you.
    Last edited by AnthonyWB; 05-07-2010 at 03:50 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1