Excel macro - personal folder

[Doogie]

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?

 

[Ron de Bruin]

Hi Doogie

Change every thisworkbook in the code to activeworkbook

 

[Dave Peterson]

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]

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.

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

 

[Doogie]

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

 

[KIM W]

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]

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

 

[KIM W]

That was all it took!

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