VBA to Create a report

[NYEXCEL1]

Per the below grid, I am looking to have VBA take the data for each Silo pull the entire row (client, Silo, Jan, Feb, etc) onto a new worksheet also including the header row and total the months. So for example, the Group 1 worksheet would have 2 lines for client "a" and "d" under the header (ideally alphabetically by client) and sum each month Column.

Thanks in advance to the Excel community!


[TABLE="width: 374"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Client[/TD]
[TD]SILO[/TD]
[TD]January[/TD]
[TD]February[/TD]
[/TR]
[TR]
[TD]Client a[/TD]
[TD]group 1[/TD]
[TD] $ 1,000.00[/TD]
[TD] $ 1,500.00[/TD]
[/TR]
[TR]
[TD]Client b[/TD]
[TD]group 2[/TD]
[TD] $ 2,000.00[/TD]
[TD] $ 5,000.00[/TD]
[/TR]
[TR]
[TD]Client c[/TD]
[TD]group 3[/TD]
[TD] $ 3,000.00[/TD]
[TD] $ 2,500.00[/TD]
[/TR]
[TR]
[TD]Client d[/TD]
[TD]group 1[/TD]
[TD] $ 4,000.00[/TD]
[TD] $ 6,800.00[/TD]
[/TR]
[TR]
[TD]Client e[/TD]
[TD]group 2[/TD]
[TD] $ 5,100.00[/TD]
[TD] $ 3,500.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[NYEXCEL1]

Thanks Zack, I will apply the above and see how it goes. Regarding VBA, I learned Excel the same way with formulas, etc. I felt lke VBa was always a different animal as the options and delarations at the beginning of the code made things confusing and the program wont work without it.

If ok with you, i hope to pick you brain with questions in the future to for ideas and guidance.

thanks

 

[Zack Barresse]

Pick away, anytime.

 

[NYEXCEL1]

I just tried. the Format worked fine, the total just added another column to the right of the total column already there.

I am looking for a total of the column not the row. so a total for the January column under the last item in that column.

Does that make sense.?

 

[Zack Barresse]

Oh, I see. So a new row at the bottom of the data, with a total for each column? So if there is 10 rows of data, row 1 being a header row, then row 12 would contain formulas like

SUM(C2:C11)

?

 

[NYEXCEL1]

Correct

 

[Zack Barresse]

Gotcha. Change the last two lines of code I gave you for these two...

        Sheet.Range("A2").Offset(UBound(Silo, 2), 2).Resize(1, UBound(Silo, 1) - 2).Formula = "=SUM(C2:C" & UBound(Silo, 2) + 1 & ")"
        Sheet.Range("A1").Offset(UBound(Silo, 2) + 1, 0).Value = "Total"

 

[NYEXCEL1]

Thanks that worked. I tried writing in the following to select all worksheets and autofit all columns but it is not working and i am getting no error messages. below is the code, can you advise and where should i put it in the program.

ActiveWorkbook.Sheets.Select
Columns("A:O").EntireColumn.AutoFit

 

[Zack Barresse]

I would think that should work, but I wouldn't do it that way. I don't like 'Select'ing anything, it's not necessary and generally provides a lot of overhead as far as [CPU] processing power, just because of the way VBA is structured as an Object Oriented Programming (OOP) language. Instead I would do it inside of the last loop, where we've been making edits, and before the 'Next' line I would put

Sheet.Cells.EntireColumn.AutoFit

 

[NYEXCEL1]

Ok will try.....thank you