Moving macro to Personal.XLSB - Workbook Reference

[skiskool]

I have imported a macro from a workbook into my personal.xlsb file so that I can run across different workbooks and activate via the Quick Access Toolbar (QAT).

The macro uses data from a source worksheet (variable name) and copies/reformats it on a new target worksheet (in the same workbook), created within the macro.

This works fine when I have the macro stored in the workbook, but following the move to personal.xlsb, the target worksheet is created in the personal.xlsb workbook and cannot find the source worksheet (because its not in the personal.xlsb workbook).

How do I re-focus the macro to the workbook that was active when it's run from the QAT?

The relevant code is below (Excel 2007, Win 7):

Sub CreateTargetSheet()

' Sets the variables used in the macro
Dim wsNew As Worksheet
Dim wsSource As String
Dim NewSetName As String
' Captures source of the set being created
wsSource = ActiveSheet.Name
' Define name for new worksheet
NewSetName = Application.InputBox("Enter a name for the new worksheet")
' Checks for the existance of a sheet with that name then creates a new worksheet
If Not SheetExists(NewSetName) Then
Set wsNew = ThisWorkbook.Worksheets.Add
wsNew.Name = NewSetName
Else
Dim Msg, Style, Title, Response, MyString
Msg = "New Worksheet name already exists, do you wish to overwrite?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "New Worksheet" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then

' User chose Yes so delete existing worksheet and set up a new one
Sheets(NewSetName).Delete
Set wsNew = ThisWorkbook.Worksheets.Add
wsNew.Name = NewSetName
Else ' User chose No. so end macro
GoTo MyExit
End If
End If
' rest of macro code goes here
MyExit:
End Sub

 

[VoG]

Try

Set wsNew = ActiveWorkbook.Worksheets.Add

 

[GlennUK]

How about having:

Dim CurrWbk As Workbook
Set CurrWbk = ActiveWorkBook

and then changing all occurences of ThisWorkbook to CurrWbk
?

 

[skiskool]

Many thanks for the quick and helpful responses - both solutions work.

How about having:

Dim CurrWbk As Workbook
Set CurrWbk = ActiveWorkBook

and then changing all occurences of ThisWorkbook to CurrWbk
?

Try

Set wsNew = ActiveWorkbook.Worksheets.Add