Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: How to capture header rows from all Excel worksheets in a directory to a separate master file

  1. #1
    New Member
    Join Date
    Oct 2017
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to capture header rows from all Excel worksheets in a directory to a separate master file

    I already have VBA code that will recursively trawl through a directory. I am currently using this to identify if any columns in any of the worksheets in any of the Excel workbooks within that directory contain email addresses and, if so, those columns are then deleted.

    Here's the find and delete loop:

    Code:
    Dim wsCurrent1 As Worksheet
    Dim B As Range
    For Each wsCurrent1 In ActiveWorkbook.Worksheets
        Do
        Set B = wsCurrent1.Columns.Find(What:="*@*", LookIn:=xlValues, lookat:=xlPart)
        If B Is Nothing Then Exit Do
        B.EntireColumn.Delete
    Loop
    Next wsCurrent1

    What I'd really like help with is to see if it's possible to modify this loop, so that instead of finding columns to delete, it copies the header row (of every worksheet in every workbook within the directory) and pastes them into a separate master file. I have seen code that more-or-less achieves this objective elsewhere - but not within the context of a RecursiveDir approach.

    Any suggestions gratefully received (even if it's just to say it can't be done this way!).

    Thank you,
    Martin.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    20,613
    Post Thanks / Like
    Mentioned
    331 Post(s)
    Tagged
    35 Thread(s)

    Default Re: How to capture header rows from all Excel worksheets in a directory to a separate master file

    Hi
    You have posted this to the test board.
    If it's a question then you will need to start a new thread in the relevant board.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  3. #3
    New Member
    Join Date
    Oct 2017
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to capture header rows from all Excel worksheets in a directory to a separate master file

    Thank you for the nudge. I think I've managed to actually post this now!

    Martin.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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