Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Copy worksheet from personal.xlsb to the active sheet

This is a discussion on Copy worksheet from personal.xlsb to the active sheet within the Excel Questions forums, part of the Question Forums category; My goal is to copy a lookup table that I maintain in Personal.xlsb and paste it into whatever worksheet that ...

  1. #1
    Board Regular
    Join Date
    Oct 2012
    Posts
    88

    Default Copy worksheet from personal.xlsb to the active sheet

    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

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,352

    Default Re: Copy worksheet from personal.xlsb to the active sheet

    I would check (again) that that name exists in your PERSONAL.XLSB. By the way you don't need to activate/select to copy paste. What's the name of the worksheet to which you want to copy?
    Microsoft MVP - Excel

  3. #3
    Board Regular
    Join Date
    Oct 2012
    Posts
    88

    Default Re: Copy worksheet from personal.xlsb to the active sheet

    The name of the worksheet to which I want to copy is variable. It changes every month.

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,352

    Default Re: Copy worksheet from personal.xlsb to the active sheet

    So you've activated the worksheet before running your macro?
    Microsoft MVP - Excel

  5. #5
    Board Regular
    Join Date
    Oct 2012
    Posts
    88

    Default Re: Copy worksheet from personal.xlsb to the active sheet

    Quote Originally Posted by Andrew Poulsom View Post
    So you've activated the worksheet before running your macro?
    Yes that is correct. The destination workbook and sheet are active when I need to paste the table.

  6. #6
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,352

    Default Re: Copy worksheet from personal.xlsb to the active sheet

    Does this work for you?

    Code:
    Sub Test()
        Workbooks("PERSONAL.XLSB").Worksheets(1).Range("ENTIRELOOKUPTABLE").Copy ActiveSheet.Range("A1")
    End Sub
    Microsoft MVP - Excel

  7. #7
    Board Regular
    Join Date
    Oct 2012
    Posts
    88

    Default Re: Copy worksheet from personal.xlsb to the active sheet

    I cannot tell you how much I sincerely appreciate your assistance. So simple , so direct.

  8. #8
    Board Regular
    Join Date
    Oct 2012
    Posts
    88

    Default Re: Copy worksheet from personal.xlsb to the active sheet

    Mr. Poulsom,
    You assisted me with the code below. I hate to be a pest , but, I need to copy "defined range names" and formatting. It does not copy those to the new workbook. Is that possible ?

    Sub Test()
    Workbooks("PERSONAL.XLSB").Worksheets(1).Range("ENTIRELOOKUPTABLE").Copy ActiveSheet.Range("A1")
    End Sub

  9. #9
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,352

    Default Re: Copy worksheet from personal.xlsb to the active sheet

    What else is on the active sheet? Maybe you can copy the entire sheet instead of the range:

    Code:
    Workbooks("PERSONAL.XLSB").Worksheets(1).Copy Before:=ActiveWorkbook.Worksheets(1)
    Microsoft MVP - Excel

  10. #10
    Board Regular
    Join Date
    Oct 2012
    Posts
    88

    Default Re: Copy worksheet from personal.xlsb to the active sheet

    I actually named the entire worksheet as a defined range, used your recommendation to copy it and it pasted beautifully! But the range names and formats did not transfer over. So close...

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com