Running a macro in workbook now running some of the macro in the personal.xlsb file!

[Sallyprit]

I have been happily running macros in varous workbooks. These macros are stored in personal.xlsb.
Today I added a new sub & now my macros are performing the macros in the personal.xlsb file.eg with the following VBA, the selection of the blue cells happens in the correct file but the new worksheet is added in the personal.xlsb file.

Sub CopyBlueColumnsToNewWorksheet()
    Dim ws As Worksheet
    Dim newWs As Worksheet
    Dim lastColumn As Long
    Dim i As Long
    Dim j As Long
    Dim blueColumns() As Boolean
    Dim blueCount As Long
    Dim wb As Workbook
   
    ' Set a reference to the active worksheet
    Set ws = ActiveSheet
   
    ' Find the last column in row 1
    lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
   
    ' Initialize an array to track blue columns
    ReDim blueColumns(1 To lastColumn)
   
    ' Check each cell in row 1 for blue color
    For i = 1 To lastColumn
        If ws.Cells(1, i).Interior.Color = RGB(0, 0, 255) Then
            blueColumns(i) = True ' Mark as blue
            blueCount = blueCount + 1 ' Increment blue column count
        End If
    Next i
   
    ' If no blue columns found, exit sub
    If blueCount = 0 Then Exit Sub
   
    ' Set a reference to the current workbook
    Set wb = ThisWorkbook
   
    ' Create a new worksheet in the current workbook
    Set newWs = wb.Sheets.Add
   
    ' Name the new worksheet
    newWs.Name = "BlueColumns"
   
    ' Copy blue columns to the new worksheet
    j = 1 ' Initialize new worksheet column index
    For i = 1 To lastColumn
        If blueColumns(i) Then
            ' Copy entire column and paste to new worksheet
            ws.Columns(i).Copy Destination:=newWs.Columns(j)
            j = j + 1 ' Move to the next column in the new worksheet
        End If
    Next i
   
    ' Clear the clipboard
    Application.CutCopyMode = False
End Sub

This is also happening with all of my other macros stored in xlsb. Help please I have an urgent deadline!

 

[jkpieterse]

ThisWorkbook is the workbook that runs the code, so in your case personal.xlsb

 

[Tupe77]

@jkpieterse already told the reason, but...

Basically, you have to define the workbook to be processed.
In general, the issue can also be solved by using the ActiveWorkbook configuration instead of ThisWorkbook.
By omitting ThisWorkbook from the code, Excel will automatically use ActiveWorkbook as I remember.
(ActiveWorkbook can be problematic if the user has several workbooks open)

My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."

 

[jkpieterse]

If you know what ThisWorkbook means, why do you have this in your Personal.xlsb code and then complain it works on Personal.xlsb:

   Set wb = ThisWorkbook

 

[RoryA]

@jkpieterse
That wasn't the OP.

 

[jkpieterse]

@RoryA hahaha I wasn't paying attention apparently

 

[Sallyprit]

If you know what ThisWorkbook means, why do you have this in your Personal.xlsb code and then complain it works on Personal.xlsb:

   Set wb = ThisWorkbook

sorry I’m new to vba

 

[Sallyprit]

@jkpieterse already told the reason, but...

Basically, you have to define the workbook to be processed.
In general, the issue can also be solved by using the ActiveWorkbook configuration instead of ThisWorkbook.
By omitting ThisWorkbook from the code, Excel will automatically use ActiveWorkbook as I remember.
(ActiveWorkbook can be problematic if the user has several workbooks open)

My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."

Thank you, I will take a look