using formula =sum('*'!A1) in a macro does not work.

  • Thread starter Thread starter TG
  • Start date Start date
T

TG

I am trying to use the formula above in a macro that is ran once a button is
clicked.

the problem is that the formula goes from =sum('*'!A1) to =sum('*''!A1'),
this results in an error message (#NAME), does anyone know why this is
happening when it is used in a macro? If i use this in a cell and type it
myself it works great!


Thanks in advance,

TG
 
I found this on a website called The Code Cage.com You can read the
whole thread here.

http://www.thecodecage.com/forumz/e...um-cell-value-across-multiple-worksheets.html

My solution to that is to add in place holder sheets. Add a newe sheet
called
'Start' at the front fo the book an done called 'End' at the end of
the book.
Now make your formula
=sum(Start:End!A1)
Now hide the Start and End sheets. Any sheets that are added will be
added
'Original Source: The Code Cage Forums
http://www.thecodecage.com/forumz/e...ue-across-multiple-worksheets.html#post634940
between start and end so the total will include all sheets.
--
HTH...

Jim Thomlinson

'Original Source:
http://www.thecodecage.com/forumz/e...um-cell-value-across-multiple-worksheets.html
 
hi, TG !
I am trying to use the formula above in a macro that is ran once a button is clicked.
the problem is that the formula goes from =sum('*'!A1) to =sum('*''!A1'), this results in an error message (#NAME)
does anyone know why this is happening when it is used in a macro?
If i use this in a cell and type it myself it works great!
Click to expand...

I'm not sure "how" do you use the sintax in your vba-code (?)
I used like this in immediate code-pane and it is working "as expected"...

activecell.formula = "=sum('*'!a1)"

resulting formula includes every [A1] cell in all worksheets in workbook (BUT activesheet)

hth,
hector.
 
Back
Top