[mso] Re: Excel REPEAT key?

Way cool...thanks! I'll check out the code this weekend!!

As for F4...I THOUGHT I did that and it didn't work??? I'll have to try
again and figure out what's happening to see what isn't happening! ;-)

Appreciate the help! 


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


-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On Behalf
Of Ray Blake
Sent: Friday, February 27, 2004 12:12 AM
To: mso@xxxxxxxxxxxxx
Subject: [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
*************************************************************

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

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

Other related posts: