Results 1 to 4 of 4

Grabbing worksheets from various workbooks

Threaded View

  1. #1
    Registered User
    Join Date
    07-22-2010
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    85

    Grabbing worksheets from various workbooks

    Hi,

    I have a code that aims to grab various worksheets from various workbooks based on worksheet names. Basically it takes the workbook names from col b and worksheet name from col c to import them in. however I am having an error on this line below:

    ws.Copy after:=wbkDest.Sheets(Sheets.Count)


    I have attached the relevant files with Test and Test2 being the files to be copied whilst MAIN is the consolidation file. Currently I am using excel 2003 for this macro. Thanks for anyone's help in advance

    Sub GrabWBs()
    
        Dim wbkDest As Workbook:    Set wbkDest = ActiveWorkbook
        Dim wbkGet As Workbook, ws As Worksheet
        Dim x As Integer:   x = 2
        
        Do While wbkDest.Sheets("Control").Cells(x, 2) <> ""
            Set wbkGet = Workbooks.Open(Filename:=wbkDest.Sheets("Control").Cells(x, 2).Value)
            For Each ws In wbkGet.Worksheets
                If ws.Name = wbkDest.Sheets("Control").Cells(x, 3).Value Then
                    ws.Copy after:=wbkDest.Sheets(Sheets.Count)
                    Exit For
                End If
            Next ws
            wbkGet.Close False
            x = x + 1
        Loop
    
    End Sub
    Attached Files Attached Files
    Last edited by kchm_2000; 10-13-2011 at 04:36 AM.

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