Results 1 to 6 of 6

Thread: Adding a column with workbook name in multiple workbooks

  1. #1
    New Member
    Join Date
    Dec 2018
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Adding a column with workbook name in multiple workbooks

    Hi

    I have over 500 workbooks, I want to add the workbook name in column AD for every row with text in it for all 500 spreadsheets.
    I plan to merge them into one master spreadsheet but I need to know which spreadsheet the data came from.
    Can someone please help me with the first part to add the workbook's name in column AD for every spreadsheet as I'm not too sure how I can do that.


    I need the workbook name not the sheet name.

    Thank you

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,177
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Adding a column with workbook name in multiple workbooks

    Are all the workbooks in the same folder?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Dec 2018
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding a column with workbook name in multiple workbooks

    Quote Originally Posted by Fluff View Post
    Are all the workbooks in the same folder?
    Yes they are in the same folder

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,177
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Adding a column with workbook name in multiple workbooks

    How about
    Code:
    Sub fawnlemur()
       Dim Pth As String
       Dim Fname As String
       Dim Wbk As Workbook
       Dim Ws As Worksheet
       
       Application.ScreenUpdating = True
       Pth = "C:\MrExcel\Fluff\"
       Fname = Dir(Pth & "*.xls*")
       Do While Fname <> ""
          Set Wbk = Workbooks.Open(Pth & Fname)
          For Each Ws In Wbk.Worksheets
             Ws.Range("AD1:AD" & Ws.Range("A" & Rows.Count).End(xlUp).Row).Value = Fname
          Next Ws
          Wbk.Close True
          Fname = Dir()
       Loop
    End Sub
    This uses column A to decide the last row, but than can easily be changed if needed.
    Change path in red to suit
    Last edited by Fluff; Sep 17th, 2019 at 07:38 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Dec 2018
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding a column with workbook name in multiple workbooks

    Thanks this is perfect

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,177
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Adding a column with workbook name in multiple workbooks

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •