Outrageous! We like simple. :) Many thanks Paul.
Pete
On Dec 15, 1:50 pm, Paul Schreiner <schreiner_p...@att.net> wrote:
> If that's the case, use:
>
> ShtName = sheet11.Name
>
> Range("N15") = "=SUM('" & ShtName & "'!AC3403:AC3409)*1000"
>
> Paul
>
>
>
> ----- Original Message ----
> > From: Susan <cow...@aol.com>
> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > Sent: Wed, December 15, 2010 11:00:07 AM
> > Subject: Re: $$Excel-Macros$$ Reference sheet number in macro
>
> > Thank you Dave. Perhaps you can clarify:
> > I'm not sure that this will work as I'm asking the macro to repopulate
> > a cell with a formula. Inside the instruction, is the sheet name, and
> > I'm not sure if it will accept a sheet number in it's place.
>
> > In the following instruction, I'm looking for the correct replacement
> > of Base Scenario and replace it with the sheet number reference which
> > is 12:
> > Sheet11.Select
> > Range("N15") = "=SUM('Base Scenario'!AC3403:AC3409)*1000"
>
> > Have tried multiple variations. . .Many thanks for a look at it.
> > Pete
>
> > On Dec 14, 10:37 pm, Dave Bonallack <davebonall...@hotmail.com> wrote:
> > > Hi Susan,
> > > Not sure if this is the problem, but I thought I'd mention it;
> > > When using a sheet name, the syntax is:
> > > Worksheets("Base Scenario") or just Sheets("Base Scenario")
> > > But when using the sheet number, neither quotes, nor the word 'Sheet' are
> > > not
> >used inside the brackets.
> > > So it just becomes:
> > > Worksheets(12) or just Sheets(12)
> > > Regards - Dave.
>
> > > > Date: Tue, 14 Dec 2010 08:44:10 -0800
> > > > Subject: $$Excel-Macros$$ Reference sheet number in macro
> > > > From: cow...@aol.com
> > > > To: excel-macros@googlegroups.com
>
> > > > Have a macro to refresh a formula, however, if the sheet named Base
> > > > Scenario gets renamed, It won't change the macro. If I replace
> > > > the"Base Scenario" reference in the macro's parenthesis with
> > > > "Sheet12", (Sheet12 is its reference before the name change) it
> > > > creates a #REF error. Any thoughts? Thanks in advance for any ideas.
>
> > > > Pete
>
> > > > Sub RefreshFormulaInSummary_15()
>
> > > > Sheet11.Select
> > > > Range("N15") = "=SUM('Base Scenario'!AC3403:AC3409)*1000"
>
> > > > end sub
>
> > > > --
>
> >----------------------------------------------------------------------------------
>
> > > > Some important links for excel users:
> > > > 1. Follow us on TWITTER for tips tricks and links
> >:http://twitter.com/exceldailytip
> > > > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > > > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > > > To post to this group, send email to excel-macros@googlegroups.com
>
> > > > <><><><><><><><><><><><><><><><><><><><><><>
> > > > Like our page on facebook , Just follow below link
> > > >http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&;...
> >Hide quoted text -
>
> > > - Show quoted text -
>
> > --
> >----------------------------------------------------------------------------------
> >-
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to excel-macros@googlegroups.com
>
> > <><><><><><><><><><><><><><><><><><><><><><>
> > Like our page on facebook , Just follow below link
> >http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&...-
> >Hide quoted text -
>
> - Show quoted text -
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
