Copy a worksheet to the end of a pre-named Workbook - without hardcoding the workbook name

[fireguy7]

Hello and thank you in advance.

I have to do staff evaluations and each month I put together a scorecard with each reps stats for the month. I keep an excel workbook and copy a new tab each month onto their workbooks.

What I can't figure out is how to enter a cell reference into the macro so I can type the workbook name into the cell and used a named range to reference this in the code.

the code to copy the active sheet to the named sheet is pretty simple.

 ActiveSheet.Copy After:=Workbooks("Jack.xlsx").Sheets(Sheets.Count)

It's when I try to reference the "Jack.xlsx" to a cell that I have issues.

I've tried the following without success and can't find a online reference to what I need.

Sub Macro1()

Dim r As Range
r = Worksheets("rep").Range("a2")

    ActiveSheet.Copy After:=Workbooks(r).Sheets(Sheets.Count)
   
 End Sub

The reference cell A2 will have the workbook name that I want to copy to.

I'm usually missing something small so any help is appreciated.

I've attached sample sheets but I doubt you guys will need it
WB test.xlsx, WS Feb = this is what I'm trying to copy
Reference cell is on Rep tab on WB Test.xlsx.
WB Jack.xlsx = this is where I'm trying to copy the Feb tab to the end of

thanks