+ Reply to Thread
Results 1 to 2 of 2

Looping and pasting worksheets into new workbook, error part way through

Hybrid View

  1. #1
    Registered User
    Join Date
    09-10-2011
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Looping and pasting worksheets into new workbook, error part way through

    My code is supposed to open four workbooks, each of which have a worksheet entitled "Template", loop through each, and for each workbook copy and paste all worksheets after "Template" into a new workbook. The macro is working great until it gets to the final workbook and it stops part of the way through and I get an error saying I can't name a worksheet the same name as an existing worksheet. When I try to move ahead with the macro, it seems that instead of pasting into the new workbook it is trying to paste into the original workbook. I can't figure out why. Here is the macro:



     
    
    Private Template_file As String
    
    
    
    
    
    Sub CopySheets()
        Dim wb As Variant
        Dim idx As Long
        Dim i As Long
        Dim YM As String
        
        
        Template_file = Worksheets(SheetA).Range("B5")
        
    
        Workbooks.Open (Worksheets(SheetA).Range("L47")), UpdateLinks:=0
    
        Windows(Template_file).Activate
        
            Sheets(SheetA).Select
            
        Workbooks.Open (Worksheets(SheetA).Range("L48")), UpdateLinks:=0
    
        Windows(Template_file).Activate
        
            Sheets(SheetA).Select
            
        Workbooks.Open (Worksheets(SheetA).Range("L49")), UpdateLinks:=0
    
        Windows(Template_file).Activate
        
            Sheets(SheetA).Select
            
        Workbooks.Open (Worksheets(SheetA).Range("L50")), UpdateLinks:=0
    
        Windows(Template_file).Activate
        
            Sheets(SheetA).Select
         
        ThisWorkbook.Activate
        
        For Each wb In Array(Worksheets(SheetA).Range("N47").Value, Worksheets(SheetA).Range("N48").Value, Worksheets(SheetA).Range("N49").Value, Worksheets(SheetA).Range("N50").Value) '<---- target workbooks
             
            With Workbooks(wb)
                 
                idx = .Worksheets("Template").Index
                For i = idx + 1 To .Worksheets.Count
                     
                    .Worksheets(i).Copy after:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
                Next i
            End With
        Next wb
        
        Windows(Worksheets(SheetA).Range("N47").Value).Activate
        ActiveWorkbook.Close savechanges:=False
        
        Windows(Worksheets(SheetA).Range("N48").Value).Activate
        ActiveWorkbook.Close savechanges:=False
        
        Windows(Worksheets(SheetA).Range("N49").Value).Activate
        ActiveWorkbook.Close savechanges:=False
        
        Windows(Worksheets(SheetA).Range("N50").Value).Activate
        ActiveWorkbook.Close savechanges:=False
    End Sub


    And here is where the error occurs after it's looped through a few worksheets on the final workbook in the array:



     
    
    .Worksheets(i).Copy after:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)


    Why might Excel out of nowhere try to paste into the workbook from where it's being copied when it's done what I want it to do up until this seemingly random point? Thanks for the help!

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Looping and pasting worksheets into new workbook, error part way through

    You don't need to activate or select the workbooks and worksheets.

    Can you attach a dummy file it will be easier to se what the cells are being used for in the workbook names
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

+ Reply to Thread

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