Hi,
ActiveWorkbook.Sheets.Count returns an integer.
But I think you need:
For i = 1 To ActiveWorkbook.Sheets.Count
Cells(i, 1) = W(i).Name
Next i
But this can cause problems if a sheet has been deleted. The macro will fault
if it calls a sheet that doesn't exist.
You could use:
i=1
For each s in Sheets
Sheets("YourSheet").Cells(i, 1) = s.Name
i = i + 1
Next s
Change "YourSheet" to the sheet name you on which want to record your sheet
names.
Regards - Dave.
> Date: Sat, 3 Jul 2010 02:05:42 -0700
> Subject: Re: $$Excel-Macros$$ First sips of Vba for Excel : Worksheet listing
> From: bpascal...@gmail.com
> To: excel-macros@googlegroups.com
>
> i = ActiveWorkbook.Sheets.Count
>
> Does ActiveWorkbook.Sheets.Count return an integer or a pointer to an
> object in which case i can't be of both type. Then how "for i ..."
> should be ?
>
> Thx
> Pascal
>
> On Jul 3, 4:20 am, Dave Bonallack <davebonall...@hotmail.com> wrote:
> > Hi,
> > I don't think you can say "For i = 1 to i" since i is undefined.
> > Regards - Dave
> >
> >
> >
> > > Date: Fri, 2 Jul 2010 11:52:06 -0700
> > > Subject: $$Excel-Macros$$ First sips of Vba for Excel : Worksheet listing
> > > From: bpascal...@gmail.com
> > > To: excel-macros@googlegroups.com
> >
> > > Hi,
> >
> > > Could you please tell me why this code is not working? From a
> > > programming background, i see no reasons why the code is not giving
> > > worksheets names through this for loop :
> >
> > > Option Explicit
> >
> > > Public Sub SheetList()
> >
> > > Dim W As Worksheet
> > > Dim i As Integer
> >
> > > Range("A1").Select
> >
> > > For i = 1 To i = ActiveWorkbook.Sheets.Count
> > > Cells(i, 1) = W(i).Name
> > > Next i
> >
> > > End Sub
> >
> > > Pascal
> >
> > > --
> > > ----------------------------------------------------------------------------------
> > > 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
> >
> > > <><><><><><><><><><><><><><><><><><><><><><>
> > > HELP US GROW !!
> >
> > > We reach over 7000 subscribers worldwide and receive many nice notes
> > > about the learning and support from the group.Let friends and co-workers
> > > know they can subscribe to group
> > > athttp://groups.google.com/group/excel-macros/subscribe
> >
> > _________________________________________________________________
> > Browse profiles for FREE! Meet local singles
> > online.http://clk.atdmt.com/NMN/go/150855801/direct/01/
>
> --
> ----------------------------------------------------------------------------------
> 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
>
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
>
> We reach over 7000 subscribers worldwide and receive many nice notes about
> the learning and support from the group.Let friends and co-workers know they
> can subscribe to group at
> http://groups.google.com/group/excel-macros/subscribe
_________________________________________________________________
If It Exists, You'll Find it on SEEK. Australia's #1 job site
http://clk.atdmt.com/NMN/go/157639755/direct/01/
--
----------------------------------------------------------------------------------
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
