combine data from multiple worksheet

[prague]

I was working on a workbook with >50 worksheet and in each sheet I have
got about 700 lines of data (or 700 rows). These data are
cross-referenced with formulas.

What I need to do is to combine all data from all worksheets into one
single worksheet, or a sheer with ~35,000 lines. I can do this with
building copy/paste macros in Excel but I believe there would be much
easier ways to achieve this.

Can anyone help me this out?

Thanks.

 

[Ron de Bruin]

Hi

Try this
http://www.rondebruin.nl/copy2.htm

 

[prague]

Thanks a lot...

I did sth similar based on your code:

******************************************
Sub Test()

Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets

If sh.Name <> "master" Then

If sh.Name <> "temp" Then

sh.Range("a1:h10").Copy
Sheets("temp").Select
Selection.PasteSpecial xlPasteValues, , False, False
Range("a1:h10").Copy
Sheets("master").Select
Range("A1").Select
Selection.Insert Shift:=xlDown

End If

End If

Next

End Sub
**************************************************

actually I insert 2 sheets named "master" and "temp" before I start th
macro.

One adjacent question:

Can we insert copied cells, but "value" only? What I did is in 2 steps
paste special + copy and insert copied cells.

Thanks,

P

 

[Ron de Bruin]

Hi

On my site there are two values only examples in each macro.