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 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
>
--
----------------------------------------------------------------------------------
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
