Simple Consolidation of Worksheets

[nonapp2]

I scanned a huge database into Excel.
It has around 300 seperate sheets.
All I need to do is combine all the sheets into one sheet.
Copy & paste would take DAYS!
Any help?

 

[tompl]

I had to make a lot of assumptions about your situation:
1 – The data is in the same format on all worksheets.
2 – You want all of the content of each worksheet combined on one new
worksheet.
3 – One worksheet is capable of including all data.

So I gave it a shot and came up with this macro to do the work for you:

Start copy after this line---

Sub ConsolidateIt()

Dim wsEach As Worksheet
Dim wsComb As Worksheet

Set wsComb = ThisWorkbook.Worksheets("Combined")

For Each wsEach In ThisWorkbook.Worksheets
If Not wsEach.Name = "Combined" Then
wsEach.UsedRange.Copy wsComb.Range("A" & wsComb. _
UsedRange.Row + wsComb.UsedRange.Rows.Count)
End If
Next wsEach

End Sub

End copy before this line ----

To make it work you need to add a new worksheet to your workbook, double
click on the new worksheet tab and name it “Combined†without the quotes.
Next, right click on the new worksheet tab and select “Edit Code†then past
the language above in the VB editor window. Close the VB editor window and
run the macro “ConsolidateItâ€. That should do it, but there are a few
unknowns here so be sure you work with a backup of your file.

Let me know if it works.
Tom

 

[Ashish Mathur]

Hi,

You may Google for RDB Merge addin. This is a good addin for consolidating
data from different worksheets/workbooks