Consulting

Results 1 to 5 of 5

Thread: Copy a sheet from all workbooks in a folder

  1. #1
    VBAX Regular
    Joined
    Nov 2004
    Posts
    27
    Location

    Copy a sheet from all workbooks in a folder

    Hello

    I require help creating a macro.
    From a workbook I want to execute a macro to select a folder, and copy into the active workbook a specifically named worksheet, (Index), from all of the workbooks in the folder.

    Thanks

    Ron

  2. #2
    VBAX Expert
    Joined
    Jul 2004
    Location
    Wilmington, DE
    Posts
    600
    Location
    Assumes all of those "Index" workbooks are closed at runtime...

    [VBA]
    Sub CopyIndex

    Dim fso As Object, fld As Object, fil As Object
    Dim wb As Workbook
    Dim ws As Worksheet

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder("c:\folder\subfolder")
    For Each fil In fld.Files
    If UCase(Right(fil.Name, 4)) = ".XLS" Then
    Set wb = Workbooks.Open(fil.Path)
    Set ws = wb.Worksheets("Index")
    With ActiveWorkbook
    ws.Copy After:=.Worksheets(.Worksheets.Count)
    End With
    wb.Close False
    End If
    Next

    Set fil = Nothing
    Set fld = Nothing
    Set fso = Nothing

    MsgBox "Done"

    End Sub
    [/VBA]
    Regards,

    Patrick

    I wept for myself because I had no PivotTable.

    Then I met a man who had no AutoFilter.

    Microsoft MVP for Excel, 2007 & 2008

  3. #3
    VBAX Regular
    Joined
    Nov 2004
    Posts
    27
    Location
    Thanks for the quick reply. When testing the code it copies all of the sheets into the active workbook not just the ?Index? named sheet and when each workbook in the folder is processed the previously copied sheets are deleted so when the macro completes there are only the original sheets in the active workbook.

    What am I doing wrong?

    Ron

  4. #4
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    Try changing ActiveWorkbook to ThisWorkbook.

  5. #5
    VBAX Regular
    Joined
    Nov 2004
    Posts
    27
    Location
    Thanks Norie that did the trick!

    Now I want some more functionality such as the user can select the folder location rather then hard coding the path and the originating workbook can be in the same folder.

    Similar to the code malik641 submitted at: http://www.vbaexpress.com/kb/getarticle.php?kb_id=829 ,but his copies all the worksheets from all the workbooks in one folder into the active workbook, how do I adjust the code to copy only a sheet named “Index”.

Posting Permissions

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