# [SOLVED] VBA: Sum Columns in Multiple Sheets



## rsavage1 (Dec 26, 2009)

Hi All. Me again. I have a spreadsheet with a macro that will sum columns and rows in sheet 1. I spent all night (literally) trying to make it sum the same columns and row in multiple sheets. The macro is in sheet1. I tried it in "ThisWorkbook" but it still didn't work. I know the problem has something to do with how I set up the loop, but I can't figure it out. I plan to use this method to summarize monthly project expenditures for multiple projects. I appreciate any help you guys can give. Thanks in advance. :4-dontkno

rsavage1


----------



## RSpecianjr (Jan 20, 2010)

*Re: VBA: Sum Columns in Multiple Sheets*

Hey rsavage1,

haha, that is some clean, sleek code.

With the way the loops currently work, you are only looping through the rows and columns. You need to add a loop for the worksheets too. You could use worksheet count to find an end. Will you want to do this process on all the worksheets?

Regards,

Robert D. Specian Jr.


----------



## RSpecianjr (Jan 20, 2010)

*Re: VBA: Sum Columns in Multiple Sheets*

Try this:



> Option Explicit
> 
> Sub SumRowsColumns1Sheet()
> 
> ...


Regards,

Robert D. Specian Jr.


----------



## rsavage1 (Dec 26, 2009)

*Re: VBA: Sum Columns in Multiple Sheets*

Hi Robert. First, thanks for the quick reply. I wish I could lay claim to that code but the truth is I'm very new to VBA and I got most of that from the Daily Dose of Excel blog (www.dailydoseofexcel) and other online sources. 

Yes. I would like this to work on all worksheets. I just ran the code you supplied and it looks like it caused the calculation to run 3 times in sheet 1. I wonder, do I need to put this procedure in "Thisworkbook"?


----------



## RSpecianjr (Jan 20, 2010)

*Re: VBA: Sum Columns in Multiple Sheets*

Run it in its own module and see if that helps at all.


----------



## rsavage1 (Dec 26, 2009)

*Re: VBA: Sum Columns in Multiple Sheets*

Hi Robert. Ran it in it's own module with the same result. I think I can see the problem but I don't know how to fix it. What I think the problem is is that the nSheets variable is not initialized anywhere. Thus, the For nSheet = 1 to 3 - Next nSheets loop only instructs VBA to execute the code between the loops parameters 3 times in the same sheet. Any tips on how to initialize the nSheets variable so that the other sheets are calculated? Thanks in advance.


----------



## RSpecianjr (Jan 20, 2010)

*Re: VBA: Sum Columns in Multiple Sheets*

hey,

it is initialized in the line of code:

with worksheets(nsheets)

not sure why it isn't working, i'll take a look at it again in the morning though.

robert


----------



## rsavage1 (Dec 26, 2009)

*Re: VBA: Sum Columns in Multiple Sheets*

Hi Robert. I found a solution. In my version of the code I did not have a "." at the beginning of the lines that start with ".Cells(iRow..." or ".Cells(LastRow +1...". One I added the ".", the "With Worksheets(nSheets)" code you introduced worked perfectly. Thank you so much for your help. Have a great day.

rsavage1


----------



## RSpecianjr (Jan 20, 2010)

Glad I could help = )


----------

