About Us

KIM W

That was all it took!
:)

"Ron de Bruin" wrote:

Try it like this

Sub NumberWorksheets_2()
'Purpose is to pre-pend each worksheet name with number for easy reference.
'Replaces with numbers, starting with 1 at leftmost worksheet.

Dim iCtr As Long
Dim iPos As Long

For iCtr = 1 To ActiveWorkbook.Worksheets.Count
On Error Resume Next
With ActiveWorkbook.Worksheets(iCtr)
iPos = InStr(1, .Name, ".")
If iPos 0 Then
.Name = iCtr & "." & Right(.Name, Len(.Name) - iPos)
Else
.Name = iCtr & "." & .Name
End If
If Err.Number < 0 Then
MsgBox "Trouble with " & ActiveWorkbook.Worksheets(iCtr).Name
Err.Clear
End If
End With
Next iCtr

End Sub

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"KIM W" wrote in message ...
This post seems to be related to my issue, but I cannot seem to fix it based
on info in this post.
My I get help with my Macro in Personal.xlsb?
This code works fine on the Workbook I originally created it in, but now I
find it so useful that I want it always available. I re-number worksheets in
the active workbook using the following, but it only re-numbers the
worksheets in Personal.xlsb when run from any other workbook:

Sub NumberWorksheets()
'Purpose is to pre-pend each worksheet name with number for easy reference.
'Replaces with numbers, starting with 1 at leftmost worksheet.

Dim iCtr As Long
Dim iPos As Long

For iCtr = 1 To Worksheets.Count
On Error Resume Next
With Worksheets(iCtr)
iPos = InStr(1, .Name, ".")
If iPos 0 Then
.Name = iCtr & "." & Right(.Name, Len(.Name) - iPos)
Else
.Name = iCtr & "." & .Name
End If
If Err.Number < 0 Then
MsgBox "Trouble with " & Worksheets(iCtr).Name
Err.Clear
End If
End With
Next iCtr

End Sub

"Ron de Bruin" wrote:

Hi Doogie

Change every thisworkbook in the code to activeworkbook

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"Doogie" wrote in message ...
I have created an excel macro and stored it in my personal.xlsm
workbook. I can access it from other workbooks just fine, but when I
execute it, it always executes the functionality against
personal.xlsm. How do I get it to ignore personal.xlsm and execute
against the other workbook I have open?

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
From Outlook to Excel Personal Folder	Roy Hobbs	Excel Worksheet Functions	0	October 10th 10 04:33 AM
Personal Macro Workbook in startup folder missing	Susan Rochon	Excel Discussion (Misc queries)	3	September 17th 09 02:44 AM
how do I create a personal folder	Billy[_6_]	Excel Programming	4	October 18th 07 05:29 PM
Personal.xls macro folder stopped working	[email protected][_2_]	Excel Programming	1	October 3rd 06 11:27 PM

Menu

About Us