Copy worksheet from personal.xlsb to the active sheet

[DENWARD]

My goal is to copy a lookup table that I maintain in Personal.xlsb and paste it into whatever worksheet that is currently open on the desktop. The destination filename constantly changes (currently TESTERPASTE.XLSX) so I want it to paste it on whatever worksheet is currently open on my desktop. Is that possible ??

My humble attempt gives me an error message on line 2. Text entered is not a valid reference or defined name

Sub testerpastermacro()
'
' testerpastermacro Macro
'
'
Windows("PERSONAL.XLSB").Visible = True
Application.Goto Reference:="ENTIRELOOKUPTABLE"
Selection.Copy
Windows("TESTERPASTE.xlsx").Activate
Range("A1").Select
ActiveSheet.Paste

End Sub

 

[Andrew Poulsom]

For me copying a sheet also copies names. I don't know why that doesn't happen for you. Are you sure that you copied the sheet not the cells?

 

[DENWARD]

let me give it a try, thanks

 

[DENWARD]

nope, it still doesnt copy the range names. Is there a type of copy command that gets all of the sheet details ???

 

[Andrew Poulsom]

Did you use the code I suggested in Post #9?

 

[DENWARD]

Mr Poulsom,
Hypermagic! It worked like a charm . Once again Thank you !!
den