Combine Many Worksheets into one Worksheet

[corona]

Hi.!

I got an excel file ( excel 97) which contains many worksheets.
All worksheets have same format.
I want to create new worksheet and combine data from every
worksheets into this new worksheet.
Could you please help me how to do this.?
Thank

Corona

[PaddyD]

Corona,

welcome to the board!

I think we'll need a bit more info before anyone can try & help out. What do you mean by 'combine' - sum, count, something else? What sort of data have you got - text, numbers, formulas.

The more data you give, the more people get a view on exactly what you're trying to do.

Paddy

[corona]

Hi!

Thanks.

"Combine"
I meant copy all data from every worksheet and put them together
in one worksheet.
( no sorting)
== example===
< Data from worksheet1>
< Data from worksheet2>
.
.
.
< Data from worksheet n>


K.Corona

[PaddyD]

Corona,

You can't copy excel files to the board - can you give us a representative example?

Paddy

[Nimrod]

Hi Corna & Paddy : This is a reprint of a solution I did for someone the othr day ..... IS this What your looking for ?

This Macro should do the trick for you . It will go through all sheets of your workbook and copy information to summary sheet.

In this example the summary sheet is called "MailList"..though you can see that at top of macro you can change that.

To add more lines to macro just copy the line Sheets(ML).Cells(TargRow, 1) = SrcWs.Range("A1") and modify the number "1" and the "A1"

The "A1" should be changed to indicate which cell the information is coming from .
The Number "1" indicates which column you want it to be placed in the "Mailing List" sheet.

For example Let's say that the PO's has the person's name in Cell "C12" and you want these names placed in column 3 of the Mailing list then:
-- "A1" needs to be changed to "C12"
-- 1 needs to be changed to 3

If you have 10 pieces of information from each PO collected, just copy the line 10 times and make the appropriate "A1" and (1) changes.

HERE'S THE MACRO

Public Sub MakeMailingList()
ML = "MailList" ' This MUST match the SheetName of your Target MailList
TargRow = 0 ' Change Start Value to change Starting TargetRow, 0=row1
    For Each SrcWs In Worksheets
        If Not (SrcWs.Name = ML) Then
        TargRow = TargRow + 1
            
            '  Target columns here          SourceCells Here
            Sheets(ML).Cells(TargRow, 1) = SrcWs.Range("A1")
            Sheets(ML).Cells(TargRow, 2) = SrcWs.Range("C5")
        
        End If
    Next SrcWs
End Sub

This macro does not require any knowledge of sheet names other then the Target Mailing List sheet.
Don't have any other workbooks open when running macro.

PS Please let me know if this Macro met your needs.





[ This Message was edited by: Nimrod on 2002-05-18 04

[ This Message was edited by: Nimrod on 2002-05-20 21:39 ]

[corona]

Hi.

Here is example.

I have an excel file ( corona.xls).
It has 15 worksheet with same format. Worksheet name are
Worksheet1, worksheet2, ... ,worksheet15.
I want to create new worksheet16
in corona.xls. I copy all data from
Worksheet1 to worksheet16 then
copy all data from worksheet2 to
worksheet16 (append) and do this job until all data from 15 worksheets are in worksheet16.
But it spend too much time to do
this copy & paste if I have many file to process.
Any other method for reduce copy & paste time?
I hope you can understand my situation.

K.Corona

[PaddyD]

In which case ,Nimrod's code looks like just the thing!!

Post back if it works once you've made the appropriate modifications.


Paddy

[Nimrod]

Hmmm entire sheets heh ?
I'm going to spend a little time and see if I can give you something a little on point ...

[Nimrod]

Maybe this one is a little more "on point" . This macro will look for all worksheets in a workbook and copy all data to "sheet16" within the same workbook. Is this what you need ?

Sub CopyRanges()
TargSheet = "Sheet16"
For Each SrcWs In Worksheets
    With SrcWs
    If Not (.Name = TargSheet) Then
        SrcTopRow = .Range("A:A").End(xlUp).Row
        SrcBotRow = .Cells(65536, 1).End(xlUp).Row
        TargBotRow = Sheets(TargSheet).Cells(65536, 1).End(xlUp).Row + 1
        TargNewBot = TargBotRow + SrcBotRow - SrcTopRow
        .Rows(SrcTopRow & ":" & SrcBotRow).Copy
        ActiveSheet.Paste Destination:=Worksheets(TargSheet).Range(TargBotRow & ":" & TargNewBot)
    End If
    End With
Next SrcWs
End Sub

[corona]

Hi NimRod

Thanks.
I will wait....

Thanks.
K.Corona