Append specific sheets from multiple workbooks into a single workbook sheet

  • We have 200 Excel survey workbooks collected in 1 folder
    Our Director wants to collect all the responses that are recorded in the Summary Sheet of each of the files (there are many other sheets in each of these workbooks)
    Specifically B9:K9 from Summary in each of the 200 files into 1 workbook = 1 sheet = Survey Summary


    I have been playing with some of the VBA file combine codes posted but the ones I am finding combine to one workbook and all the sheets are collected not appended as 1 sheet
    Some of the code is based on workbooks being already open


    I am looking for the code to
    designate where the folder of 200 files resides
    The specific sheet name
    The specific cells in the designated sheet
    TO
    a new workbook with 1 sheet of all the 200 rows from each file


    Hopefully I am making sense
    Posted sample of summary sheet


    Thank you

  • Here is one approach. Adjust path etc to suit. The code goes in the master file.

  • Thank you for your assistance
    I tried your code with changes to the path as well as sheet name


    However I get a runtime error 91


    Object variable or with block variable not set


    Is there other lines that needed my custom entry?


    Thank you

  • it did not flag any line just that error message this is the code with my changes


  • When it stops does it not give you an option to end or debug? If it does, press debug and see which line is highlighted. Do you know how to step through code using F8?


    Are all the files you're opening Excel files and do they all have the Summary sheet?


    Does the code do anything at all?

  • No option to end or debug
    only when runs message @ links to update or don't update


    I did step through with F8
    Screen shot attached


    the master file is in the same folder as the test files = on desktop in testing folder
    the test files are all excel - but not open -- (she will have 200 so hopefully they do not need to touched at all)
    they all have a sheet called summary with the row range b9.k9 (I sent you a sample)


    But no the code does not seem to do anything


    Many thanks for your time
    Leslie

  • that did the trick!


    Last question - the script prompts to update each file
    in the real folder there will be 200 files so
    is there a way to bypass the prompts and have it automatically update ?


    Thank you
    Leslie

  • Don't kill me but I see another issue I did not anticipate
    because the row we are copying is a sum row it is replicating the sum on every cell
    can we just have the row copied without the formula - just as text ?
    Attached sample - each copied cell sums the column not a copy of the value [ATTACH=JSON]{"data-align":"left","data-size":"medium","data-attachmentid":1210303}[/ATTACH] from the original sheet


    Thank you for your time and effort
    Much appreciated
    Leslie

  • Re the first query, can you try adding this line at the top

    Code
    application.displayalerts=false


    and turn it back on at the end?


    Re the second, replace this line

    Code
    wb.Sheets("Summary").Range("B9:K9").Copy _
     ThisWorkbook.Sheets("Summary").Range("A" & Rows.Count).End(xlUp)(2)

    with these two lines

    Code
    wb.Sheets("Summary").Range("B9:K9").Copy
    ThisWorkbook.Sheets("Summary").Range("A" & Rows.Count).End(xlUp)(2).pastespecial xlvalues
  • almost there!
    so the rows seem to be copying without summing -- perfect - replacing those lines seem do the check but I will double check
    However
    I am still getting update prompts


    I tried coping line after the screen update line
    also before it (not sure it makes a difference)
    as well as without the screen update line
    but still getting prompts or syntax errors
    what I have so far is attached


    Thank you[ATTACH=JSON]{"data-align":"none","data-size":"large","data-attachmentid":1210321}[/ATTACH]

  • StephenR


    Wanted to follow-up since you have been so helpful
    I was able to find some code to prevent the prompts to continue (and the user having to click through them) when opening the files to copy the row
    the set wb ..................... I have in red seems to be the addition needed to prevent the prompts


    here is the final - and it works like a charm - again my thanks


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!