topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Thursday March 28, 2024, 3:37 pm
  • Proudly celebrating 15+ years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: Utilities for an excel book  (Read 9240 times)

Contro

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 3,940
    • View Profile
    • Donate to Member
Utilities for an excel book
« on: March 03, 2014, 06:41 AM »
I have an excel book with many sheets.
I would like to list, in the more easy way, the sheets to a txt file or similar

How can I do this ?

Best Regards
 :-*

katykaty

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 224
    • View Profile
    • Donate to Member
Re: Utilities for an excel book
« Reply #1 on: March 03, 2014, 02:45 PM »
ASAP Utilities has a function to create an index sheet with named links to each worksheet. You could easily take that list, select and copy all, and paste as values.

http://www.asap-utilities.com/

Contro

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 3,940
    • View Profile
    • Donate to Member
Re: Utilities for an excel book
« Reply #2 on: March 03, 2014, 09:27 PM »
ASAP Utilities has a function to create an index sheet with named links to each worksheet. You could easily take that list, select and copy all, and paste as values.

http://www.asap-utilities.com/

Really ?
Running to try
 :-*

Contro

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 3,940
    • View Profile
    • Donate to Member
Re: Utilities for an excel book
« Reply #3 on: March 04, 2014, 06:44 PM »
simply wonderful

 :-*

TaoPhoenix

  • Supporting Member
  • Joined in 2011
  • **
  • Posts: 4,642
    • View Profile
    • Donate to Member
Re: Utilities for an excel book
« Reply #4 on: March 04, 2014, 07:01 PM »
ASAP Utilities has a function to create an index sheet with named links to each worksheet. You could easily take that list, select and copy all, and paste as values.

http://www.asap-utilities.com/

Heh yep! But I don't have Excel here at home so I didn't jump in, but I recall using it at work to do a few other things!


Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: Utilities for an excel book
« Reply #5 on: March 04, 2014, 07:14 PM »
if you don't want the overhead of a(nother) addin here's a bit of vba code that will do the same thing

Sub addlinks()
Dim rowno As Integer, i As Integer
rowno = 2

Worksheets.Add before:=Sheets(1)

For i = 2 To Worksheets.Count
    Sheets(1).Hyperlinks.Add Anchor:=ActiveSheet.Cells(rowno, 2), Address:="", SubAddress:="'" & Sheets(i).Name & "'!A1", TextToDisplay:=Sheets(i).Name
    rowno = rowno + 1
Next i

End Sub

TaoPhoenix

  • Supporting Member
  • Joined in 2011
  • **
  • Posts: 4,642
    • View Profile
    • Donate to Member
Re: Utilities for an excel book
« Reply #6 on: March 04, 2014, 07:43 PM »
if you don't want the overhead of a(nother) addin here's a bit of vba code that will do the same thing

Maybe, but as I begin to understand Contro, he's the kind of fella who wants a lot of cool new tricks. For him I'd recommend the Utilities because if he pokes around he might find answers to random things he hasn't asked us yet.


Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: Utilities for an excel book
« Reply #7 on: March 04, 2014, 07:59 PM »
so you're heading him off at the pass? ;D ;D ;D

TaoPhoenix

  • Supporting Member
  • Joined in 2011
  • **
  • Posts: 4,642
    • View Profile
    • Donate to Member
Re: Utilities for an excel book
« Reply #8 on: March 04, 2014, 08:31 PM »
so you're heading him off at the pass? ;D ;D ;D

Just a little! : )


Contro

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 3,940
    • View Profile
    • Donate to Member
Re: Utilities for an excel book
« Reply #9 on: March 04, 2014, 11:29 PM »
if you don't want the overhead of a(nother) addin here's a bit of vba code that will do the same thing

Sub addlinks()
Dim rowno As Integer, i As Integer
rowno = 2

Worksheets.Add before:=Sheets(1)

For i = 2 To Worksheets.Count
    Sheets(1).Hyperlinks.Add Anchor:=ActiveSheet.Cells(rowno, 2), Address:="", SubAddress:="'" & Sheets(i).Name & "'!A1", TextToDisplay:=Sheets(i).Name
    rowno = rowno + 1
Next i

End Sub

work very well.
 ;D

katykaty

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 224
    • View Profile
    • Donate to Member
Re: Utilities for an excel book
« Reply #10 on: March 05, 2014, 01:26 PM »
Glad it worked. :)

Now, for your next Excel project:

https://www.youtube..../watch?v=canLENvTD8E

Contro

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 3,940
    • View Profile
    • Donate to Member
Re: Utilities for an excel book
« Reply #11 on: March 08, 2014, 06:59 PM »
Glad it worked. :)

Now, for your next Excel project:

https://www.youtube..../watch?v=canLENvTD8E

Really wonderful and powerful. Excel getting a graphic environment like BLENDER (more or less.....)
 ;D

Curt

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 7,566
    • View Profile
    • Donate to Member
Re: Utilities for an excel book
« Reply #12 on: March 09, 2014, 08:10 AM »
if he pokes around he might find answers to random things he hasn't asked us yet.

more things, he hasn't asked us yet ?!  :o

Contro

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 3,940
    • View Profile
    • Donate to Member
Re: Utilities for an excel book
« Reply #13 on: March 09, 2014, 11:22 AM »
if he pokes around he might find answers to random things he hasn't asked us yet.

more things, he hasn't asked us yet ?!  :o


Curt is right . I have assumed my ignorance. Beware of Contro  !!!!!!!!!!!!!!!!!!!
 :-*

Curt

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 7,566
    • View Profile
    • Donate to Member
Re: Utilities for an excel book
« Reply #14 on: March 09, 2014, 02:04 PM »
at least beware of the blender!!

AndyM

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 616
    • View Profile
    • Donate to Member
Re: Utilities for an excel book
« Reply #15 on: March 10, 2014, 06:13 PM »
if you don't want the overhead of a(nother) addin ....
... but as far as Excel goes, ASAP Utilities is not just another addin, it's the addin. ;D