asked on

How do I populate a sheet with specified information

I have a workbook with 18 sheets. On each sheet there are different types of material in one column, material prices in another column and the amount of material in another column.
i would like to make another sheet that automatically lists the number and type of material in all 18 sheets.
mcpijld.xlsx

ragnarok89

Create a sheet called aggregate, and put in your column headers A1="Amount" and B1="Description"

Then run this macro

Dim sh As Worksheet

For Each sh In Worksheets
    Sheets(sh.Name).Select
    If sh.Name = "aggregate" Then GoTo agg:
    Range("B3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Selection.Copy
    Sheets("aggregate").Select
    Range("b65536").End(xlUp).Select
    ActiveCell.Offset(1, -1).Select
    ActiveSheet.Paste
agg:
    Next sh
End Sub

Open in new window

mcpijld

ASKER

I typed in this macro to the letter and every time I put a number in th a column on any of the sheets I get an outside perrimeter error.

ASKER CERTIFIED SOLUTION

Magadu

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

mcpijld

ASKER

This is getting closer. The only items i need on "AllSHeets" would be the ones with a number in column A.

ragnarok89

This works assuming your All Sheets is Blank. This macro will populate All sheets ONLY with items that have amounts greater than zero.

Sub x()
Dim sh As Worksheet
ag = "All Sheets"
aggr = 2
For Each sh In Worksheets
    Sheets(sh.Name).Select
    r = 3
    If sh.Name = "All Sheets" Then GoTo agg:
    While Range("B" & r).Value <> ""
        If Range("A" & r).Value > 0 Then
            Sheets(ag).Range("A" & aggr).Value = Range("A" & r).Value
            Sheets(ag).Range("B" & aggr).Value = Range("B" & r).Value
            aggr = aggr + 1
        End If
        r = r + 1
    Wend
agg:
    Next sh
End Sub

Open in new window

mcpijld

ASKER

ragnarok89,

I am not getting another sheet with the list?

mcpijld

ASKER

maybe I should restate what I want to do.

In all of these worksheets I would like every cell in column A that has a value sent to a separate worksheet with the corresponding cell in column B.
takeoff.xlsx

mcpijld

ASKER

I combined some of maqadu's and raqnarok89's macros and got what I was looking for.

thank you very much guys.

here is what works for me
__________________________________________________________________________________

Sub Takeoff()

Dim shCurrent As Excel.Worksheet
Dim shAllSHeets As Excel.Worksheet
Dim lLastSCell As Long
Dim lLastDRow As Long
lLastDRow = 2
Set shAllSHeets = Sheets.Add(After:=Sheets(Sheets.Count))
shAllSHeets.Name = "All Sheets"
ag = "All Sheets"
aggr = 2
For Each sh In Worksheets
Sheets(sh.Name).Select
r = 3
If sh.Name = "All Sheets" Then GoTo agg:
While Range("B" & r).Value <> ""
If Range("A" & r).Value > 0 Then
Sheets(ag).Range("A" & aggr).Value = Range("A" & r).Value
Sheets(ag).Range("B" & aggr).Value = Range("B" & r).Value
aggr = aggr + 1
End If
r = r + 1
Wend
agg:
Next sh