Help on Data Manupilation needed- macros needed!

[sanlejushi]

Hi

I have this problem where I need to manupilate data, in sort of 2 stages.
The first stage involves automatically summing the data from a column (col A) to form a new column (Col B). e.g.
col A col B
2.......-
3.......5
4.......7
5.......9
6.......11

Now, I have, many, many sheets of data, and they have unequal rows of data, some have less than 50, others more than 500 - but the summing should be done automatically.

For stage 2, its even more challenging- I need to bring all the data in all the "column Bs" in all the worksheets into a new worksheet, such that this new worksheet has all the column B's - whats more, to identify the data, each of te "column B's" inside the new worksheet needs to have a header row, which is the name of the worksheet that the particular " column B" originate from.

e.g. from the previous example, lets say the name of the previous worksheet is "apple"

the new worksheet should look something like this:

apple
-
5
7
9
11

I've more than a hundred worksheets, this will take me a year to do manually (gawd!), I guess a macro is needed here, but my skill in macros is severely limited- especially since this involves moving data across worksheets -does anyone know how the code should look like?

Any help in this is greatly appreciated. thanks!

 

[Andrew Poulsom]

Try this:

Sub Test()
    Dim ShNew As Worksheet
    Dim Sh As Worksheet
    Dim c As Integer
    Dim Rng As Range
    Application.ScreenUpdating = False
    Set ShNew = Worksheets.Add
    c = 1
    For Each Sh In Worksheets
        If Sh.Name <> ShNew.Name Then
            ShNew.Cells(1, c).Value = Sh.Name
            ShNew.Cells(2, c).Value = 0
            Set Rng = Sh.Range("A2:A" & Sh.Range("A65536").End(xlUp).Row)
            With Rng.Offset(0, 1)
                .FormulaR1C1 = "=SUM(R[-1]C[-1]:RC[-1])"
                .Copy
                ShNew.Cells(3, c).PasteSpecial xlPasteValues
            End With
            c = c + 1
        End If
    Next Sh
    ShNew.Activate
    Application.ScreenUpdating = True
End Sub

 

[sanlejushi]

Thanks a trillion! It works like a charm!
Andrew, if we're living on the same continent i wld've hopped over and gave u a big hug....hahahaa...yippee!