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
    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

  • 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

  • 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

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


    and turn it back on at the end?

    Re the second, replace this line

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

    with these two lines

    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
    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!