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?

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?

When I have this kind of thing, I'll write the code to use the activesheet.

Any chance your code is specific in what sheet it uses?

You may want to post your code if this doesn't help.

Doogie wrote:

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?

--

Dave Peterson

On Jul 30, 1:58*pm, Dave Peterson wrote:
When I have this kind of thing, I'll write the code to use the activesheet.

On Jul 30, 2:07*pm, Doogie wrote:
On Jul 30, 1:58*pm, Dave Peterson wrote:





When I have this kind of thing, I'll write the code to use the activesheet.

Any chance your code is specific in what sheet it uses?

You may want to post your code if this doesn't help.

Doogie wrote:

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?

--

Dave Peterson

Here's my code. *I don't have any place that I can see a thisWorkbook
from the other person's post so couldn't make those changes. (there
are more than 2 cases in my case statement, but I only put two here
for demonstrative purposes).

Sub changeTabNames()
* * Dim worksheetValue As String
* * Dim tabName As String
* * Dim worksheet

* * For Each worksheet In Worksheets
* * * * worksheetValue = Left(worksheet.Range("A6"), 2)

* * * * Select Case worksheetValue
* * * * * * Case "4D"
* * * * * * * * tabName = "4D-BALID"
* * * * * * Case "4I"
* * * * * * * * tabName = "4I-HNCUT"
* * * * End Select

* * * * worksheet.Name = tabName
* * Next

End Sub- Hide quoted text -

- Show quoted text -

I figured it out. I had to go one step up and check the
workbook...the only thing, I'm hardcoding a look for the
"personal.xlsm" file, which if there was a better way to do that I'd
like to.

Sub changeTabNames()
Dim worksheetValue As String
Dim tabName As String
Dim worksheet
Dim workbook

For Each workbook In Workbooks
If (workbook.Name < "personal.xlsm") Then
For Each worksheet In workbook.Worksheets
worksheetValue = Left(worksheet.Range("A6"), 2)

Select Case worksheetValue
Case "4D"
tabName = "4D-BALID"
Case "4I"
tabName = "4I-HNCUT"
End Select
Next
End If
Next

End Sub

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?

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?

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?