[mso] Re: Excel REPEAT key?

Dian,

A couple of points. 

1. Excel has a 'reapeat last action' just like Word. Try F4.

2. I'm working on an add-in to make bulk sheet creation much easier. The
code is below; although it's a work in progress, the code seems to work
fine. All you do is key the sheet tab names into cells, select the range
and then run this code. Not only will it create new sheets named as per
your list, it will also hyperlink to and from those sheets. You could
turn off the hyperlinking if you wanted to, of course.

Since it's a work in progress, comments are welcome.

Ray


Sub MakeNewSheet()
    
Dim strMain As String
Dim strAddress As String
Dim BadCharsCount As Integer
Dim DupeNameFlag As Boolean

    strMain = ActiveSheet.Name
    For Each c In Selection
        strAddress = c.Address
        If Len(c.Value) = 0 Then
            MsgBox "Empty cell at " & Replace(c.Address, "$", "")
            Exit Sub
        End If
        If Len(c.Value) > 31 Then
            MsgBox "Sheet name exceeds 31 characters at cell " &
Replace(c.Address, "$", "")
            Exit Sub
        End If
        BadCharsCount = InStr(1, c.Value, ":") + _
            InStr(1, c.Value, "\") + _
            InStr(1, c.Value, "/") + _
            InStr(1, c.Value, "?") + _
            InStr(1, c.Value, "*") + _
            InStr(1, c.Value, "[") + _
            InStr(1, c.Value, "]")
        If BadCharsCount > 0 Then
            myAnswer = MsgBox("Illegal characters in proposed filename
at cell " & _
                Replace(c.Address, "$", "") & "." & vbCrLf & "Do you
want me to fix it?", vbYesNo)
            If myAnswer = vbNo Then
                MsgBox "Halting execution at cell " & Replace(c.Address,
"$", "")
                Exit Sub
            End If
            c.Value = Replace(c.Value, ":", "-")
            c.Value = Replace(c.Value, "\", "-")
            c.Value = Replace(c.Value, "/", "-")
            c.Value = Replace(c.Value, "?", "-")
            c.Value = Replace(c.Value, "*", "-")
            c.Value = Replace(c.Value, "[", "-")
            c.Value = Replace(c.Value, "]", "-")
        End If
        For Each d In Sheets
            If c.Value = d.Name Then DupeNameFlag = True
        Next d
        If DupeNameFlag Then
            MsgBox "Duplicate sheet name at cell " & Replace(c.Address,
"$", "")
            Exit Sub
        End If
        Sheets.Add after:=Sheets(Sheets.Count)
        ActiveSheet.Name = c.Value
        strSubAddr = "'" & c.Value & "'!A1"
        Sheets(strMain).Hyperlinks.Add c, "", strSubAddr, "Go to sheet",
c.Value
        strSubAddr = "'" & strMain & "'!" & strAddress
        ActiveSheet.Hyperlinks.Add ActiveSheet.Range("a1"), "",
strSubAddr, "Back to index", "Main Sheet"
        Sheets(strMain).Select
    Next c
    
End Sub

------------------------------------
GR Business Process Solutions
Ray Blake
Head of Software Design
ray@xxxxxxxxx
Braedon
Newell Road
Hemel Hempstead
Herts HP3 9PD
tel: 01442 396518
fax: 01442 389353
www.grbps.com
------------------------------------


-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On
Behalf Of Dian Chapman
Sent: 27 February 2004 03:50
To: mso@xxxxxxxxxxxxx
Subject: [mso] Excel REPEAT key?


Does Excel have a repeat key like Word? 

There are a few times when I do something that I'd like to do again
without having to manually do it over and over. 

1) In a pivot table, sometimes I select an item and move it up/down in
the table. The only way I know to move it up several positions is to
continually right click and click move up. Is there an easier way?

2) When adding new sheets. What if I want to add say, 4 new
worksheets...rather than clicking Insert > Worksheet...is there an
easier way?

TIA...

Dian D. Chapman
Technical Consultant, Instructor
Microsoft MVP & MOS Expert Certified

Free MS Tutorials: www.mousetrax.com/techtrax
Free Word Tips & Tricks eBook: www.mousetrax.com/books.html Learn VBA
the easy way, thru video! www.mousetrax.com/techcourses.html


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.592 / Virus Database: 375 - Release Date: 2/18/2004
 

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx
or MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the
quotes) in the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will
also allow you to change your email settings to digest or vacation (no
mail). http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group,
send a request to mso-moderators@xxxxxxxxxxxxx and you will be sent an
invitation with instructions.  Once you are a member of the files group,
you can go here to upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in 
the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also allow 
you to change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a 
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with 
instructions.  Once you are a member of the files group, you can go here to 
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************

Other related posts: