Excel 2007 : Combining data from multiple Excel Files

**pike** · 10-05-2010, 07:36 AM

good work . i was a little unsure from your sample.xls which cell{s} to save
this bit well change to find the cells to copy

 'This is the section to customize, replace with your own action code as needed
        'Find last row and copy data
            LR = Range("A" & Rows.Count).End(xlUp).Row
            If NR = 1 Then      'copy the titles and data
                Range("A1:A" & LR).EntireRow.Copy _
                    wbknew.Sheets("Master").Range("A" & NR)
            Else                'copy the data only
                Range("A2:A" & LR).EntireRow.Copy _
                    wbknew.Sheets("Master").Range("A" & NR)
            End If
        'close file

what we need to do is look in certain cell if they have a values then copy the ranges

i think its a5 with row 7 to 10 and a15 with rows 17 to 20? or can you highlight the stuff you need.

**robbyvegas** · 10-05-2010, 01:16 PM

OK. Working on it. Out of curiosity. What is the best VBA book out there?

**robbyvegas** · 10-07-2010, 01:08 AM

OK, I'm working on this task again and I have a few thoughts that might simplify the tasks if you can help me figure out how to implement them.

First, I believe that I will always need B3, G24, and J24 of each spreadsheet. Can you help me to amend the code

  'This is the section to customize, replace with your own action code as needed
        'Find last row and copy data
            LR = Range("A" & Rows.Count).End(xlUp).Row
            If NR = 1 Then      'copy the titles and data
                Range("A1:A" & LR).EntireRow.Copy _
                    wbknew.Sheets("Master").Range("A" & NR)
            Else                'copy the data only
                Range("A2:A" & LR).EntireRow.Copy _
                    wbknew.Sheets("Master").Range("A" & NR)
            End If

to pick up these two cells from each spreadsheet?

Second, I think that there is a command to have excel create separate worksheets where you have white space between sets of data. Is that correct? If so, can you remind me of what function will do this (if I'm thinking of a macro that you are familiar with and not a function, please let me know)?

Thank you,

**robbyvegas** · 10-07-2010, 01:28 AM

Third, using this great code to pull all of these separate files together, is there then a way to automatically transpose all of the rows to columns, and putting all of the data into colums A & B?

I think that these three things would go a long way towards simplifying my life tonight.

Thank you again for all of the help on this.