Extracting Worksheet from multiple files + consolidated them...

RCRod83 · 03-09-2013, 09:40 PM

Hello,

What is the best way to accomplish the following?

1. Extract Sheet7 from multiple excel files (the files are budget templates, so they are exactly the same). The sheet 7 has formulas, so I would need to extract the values.

2. Load Sheet 7 into one excel sheet (Sheet 7 tab name is different in each excel file, but it is Sheet7).

3. Using the same template format, I would like to combine all the values across all the sheets into 1 worksheet. I.e. Take the value in "A7" from all the worksheets and sum it together into A7 on Sheet1.

I hope this makes sense.

Thanks!

**JBeaucaire** · 03-09-2013, 10:58 PM

'WORKBOOKS TO 1 SHEET STACKED
Here's a macro for collecting data from all files in a specific folder.

Workbooks to 1 Sheet

The parts of the code that need to be edited are colored to draw your attention.

===============

The main customization will be in this section:

        'This is the section to customize, replace with your own action code as needed
            LR = Range("A" & Rows.Count).End(xlUp).Row  'Find last row
            Range("A1:A" & LR).EntireRow.Copy .Range("A" & NR)
            wbData.Close False                                'close file
            NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1  'Next row
            Name fPath & fName As fPathDone & fName           'move file to IMPORTED folder
        End If
        fName = Dir                                       'ready next filename

Something like this, add to this "map" as you need:

        'This is the section to customize, replace with your own action code as needed
            'create a map here of the cells you want to "add"
            .Range("A7").Value = .Range("A7").Value + sheet7.Range("A7").Value
            .Range("B9").Value = .Range("B9").Value + sheet7.Range("B9").Value
            .Range("C7").Value = .Range("C7").Value + sheet7.Range("C7").Value

            wbData.Close False                                'close file
            NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1  'Next row
            Name fPath & fName As fPathDone & fName           'move file to IMPORTED folder
        End If
        fName = Dir                                       'ready next filename

RCRod83 · 03-10-2013, 11:27 AM

Wow thanks! Let me play with this and see if I can get it to work. I'll report back. :D

RCRod83 · 03-10-2013, 11:53 AM

Quick question: It states that 'Sheet7' in your map: "Variable Not Defined"

\1

**sbehera** · 03-10-2013, 12:41 PM

Hi,

can you try once with


Sheets(7)

instead of sheet7

**JBeaucaire** · 03-10-2013, 08:12 PM

Mybad! Must attach the sheet7 reference to the opened workbook:

        'This is the section to customize, replace with your own action code as needed
            'create a map here of the cells you want to "add"
            .Range("A7").Value = .Range("A7").Value + wbData.sheet7.Range("A7").Value
            .Range("B9").Value = .Range("B9").Value + wbData.sheet7.Range("B9").Value
            .Range("C7").Value = .Range("C7").Value + wbData.sheet7.Range("C7").Value

            wbData.Close False                                'close file
            NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1  'Next row
            Name fPath & fName As fPathDone & fName           'move file to IMPORTED folder
        End If
        fName = Dir                                       'ready next filename