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!

Client	SILO	January	February
Client a	group 1	$ 1,000.00	$ 1,500.00
Client b	group 2	$ 2,000.00	$ 5,000.00
Client c	group 3	$ 3,000.00	$ 2,500.00
Client d	group 1	$ 4,000.00	$ 6,800.00
Client e	group 2	$ 5,100.00	$ 3,500.00

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[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