Macro to enter AutoSum in the last row of data from Column F:Q

[Johnny Thunder]

Hello All,


I have a macro that paste data from a master report into tabs (About 12) and
then Hides Column A.

In the same macro I need to add code to the end that will look in all tabs and in Columns F through Q I need to find the last cell with data and make it a SUM formula that will go to the top of the report specifically to cell 3.

So in column F, in the last cell with data it should look something like this =SUM(F3:F"Whatever cell is directly above the formula")

Hope someone knows of a way. Thanks in advance!!!!

 

[ravishankar]

Hi
try these codes. you can run it as an independent macro.

sub johnny()
dim a as long, b as long, x as long
for a = 1 to sheets.count
for b = 6 to 16
x= worksheets(a).cells(rows.count,b).end(xlup).row
worksheets(a).cells(3,b) = "=sum(" & chr(b+64) & "3:" & chr(b+64) & x & ")"
Next b
Next a
msgbox "Complete"
end sub

Ravi

 

[Johnny Thunder]

Ravi,


Where do I apply the rows I want to enter the code into? F:Q are the only columns I need to apply the AutoSum too.

 

[Johnny Thunder]

Ravi,



The macro worked but maybe I was unclear about how I needed it to work, Sorry.


How do I get it to locate the last cell with data in column F and then autosum upwards? right now it is starting in row 3 and autosum downwards?

 

[ravishankar]

Hi
Just replace cells(3,b) with cells(x+1,b)
Ravi

 

[Johnny Thunder]

Ravi,


Thank you for the reply. I put the new changes into the spreradsheet and the macro is entering the Auto-Sum under the last line of data? I wanted the macro to replace the last line with the auto-sum?

Is there something else that needs to be changed in the script?

 

[Johnny Thunder]

So the macro works but I just need it to put the auto sum in the last row with numbers, right now it is putting the auto sum directly under the last row?

Anyone have any ideas on this?

 

[ravishankar]

HI
Try these codes and see if this is what you want.

sub johnny()
dim a as long, b as long, x as long
for a = 1 to sheets.count
for b = 6 to 16
x= worksheets(a).cells(rows.count,b).end(xlup).row
worksheets(a).cells(x,b) = worksheets(a).cells(x,b) & chr(10) & "Total = " & worksheetfunction.sum(Range((chr(b+64) & "3:" & chr(b+64) & x ))
Next b
Next a
msgbox "Complete"
end sub

ravi

 

[Johnny Thunder]

Hey Ravi,

The line
worksheets(a).cells(x,b) = worksheets(a).cells(x,b) & chr(10) & "Total = " & worksheetfunction.sum(Range((chr(b+64) & "3:" & chr(b+64) & x ))

Is all Red when I pasted the macro into VBA? I couldnt figure out what the error is?