Amlagamate Worksheets 1

[phatmankerr2005]

I need to be able to amalgamate seperate worksheet data into one single sheet (all the sheets contain the same headings) but I need all the data in one block (minus the headings if possible).

Anybody any ideas how to do this simply. Only reason I ask is that I have 140 worksheets all with varying amounts of data that need to be combined.

Any ideas would be much appreciated.

 

[SkipVought]

Hi,

Take a look at MS Query via menu item Data/Get External Data/New Database Query -- Excel files -- YOUR WORKBOOK -- ONE OF YOUR WORKSHEETS.

Once you figgure out how to do it, macro record adding the querytable, which is what you are doing. Once you record ONE you can programatically loop thru the sheets and query them all AND you can programatically calculate the NEXT row to insert to get the data all in one table. You can control whether or not to include the headings.

Keep in mind that the sheet that you are combining them on, will end up with multiple query tables. If this will be a recurring thing, be sure to DELETE all data/query tables on the combine sheet.

Post back if you need any specific help.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[phatmankerr2005]

Skip,

Cheers mate but I was really looking for a VBA solution - hit the books and came up with the following (if anybody's interested). Worked fine.

Sub amalgamate()

For i = 2 To Worksheets.count
Worksheets(i).Activate
ActiveSheet.Range("A1").Select
ActiveCell.CurrentRegion.Select
Selection.Copy

Worksheets(1).Activate
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

Next i

End Sub

Thanks

Phat

 

[SkipVought]

How about a GOOD SOLUTION?!

Limiting yourself, ARTIFICIALLY, to code, is not an effective approch.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[FaneDuru]

Your code may be simplified and made faster like this:

For i = 2 To Worksheets.Count
 Worksheets(i).Range("A1").CurrentRegion.Copy _
            Worksheets(1).Range("A1").End(xlDown).Offset(1, 0)
Next i

Fane Duru

 

[phatmankerr2005]

FaneDuru,

Cheers mate have applied your new code - runs noticably faster.

Thanks

Phat