+ Reply to Thread
Results 1 to 7 of 7

Copy/pasting from one workbook to another - Out of range error

  1. #1
    Registered User
    Join Date
    07-24-2013
    Location
    Bratislava, SK
    MS-Off Ver
    Office Pro Plus 2016
    Posts
    42

    Copy/pasting from one workbook to another - Out of range error

    Hello all,

    I've been having a little trouble with something that should be simple, and I keep getting an error which I can't figure out.

    I have a file with contacts in the "Supplier Contacts" tab. Under certain conditions, I'd like the range A1:F1000 in the current file to be copied to a master file which the user selects using an "Open File" dialogue. The master file is identical in structure to the file I'm working on (same sheets). Up to here, all is ok. But when I get to the pasting in the master file part, I get an Out of range error, and it's driving me nuts.

    Here is my code (messy, I apologise beforehand):

    Please Login or Register  to view this content.
    When the macro gets to:
    Please Login or Register  to view this content.
    I get the "Subscript out of range" error, and I can't understand why.

    I don't know if there are any errors after that line, BTW, as I can't get past the error above

    Can anyone here see the error that is eluding me?

    Thank you in advance for any advice.

    -Isa

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,038

    Re: Copy/pasting from one workbook to another - Out of range error

    "Subscript out of range" also occurs when the name of the workbook (possibly Sheetname too) does not exist.
    Check the value of the variable MasterFile, make sure it is correct.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,348

    Re: Copy/pasting from one workbook to another - Out of range error

    Please Login or Register  to view this content.
    Use quotation marks around MasterFile is at least one correction. Not certain if there are more.

  4. #4
    Forum Expert (Lazarus)
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    .
    Posts
    2,594

    Re: Copy/pasting from one workbook to another - Out of range error

    How about
    Please Login or Register  to view this content.
    Your other range references use ':'

  5. #5
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,038

    Re: Copy/pasting from one workbook to another - Out of range error

    Quote Originally Posted by Logit View Post
    Please Login or Register  to view this content.
    Use quotation marks around MasterFile is at least one correction. Not certain if there are more.
    MasteFile is a variable not a string.

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,596

    Re: Copy/pasting from one workbook to another - Out of range error

    Isa

    MasterFile will contain the path and filename of the file that the user selects using GetOpenFileName.

    If you want to refer to the workbook once it's open then you only need the filename, if you include the path then you'll get a 'subscript out of range' error.

    One way to avoid this problem is to use a variable to refer to the workbook being opened.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  7. #7
    Registered User
    Join Date
    07-24-2013
    Location
    Bratislava, SK
    MS-Off Ver
    Office Pro Plus 2016
    Posts
    42

    Re: Copy/pasting from one workbook to another - Out of range error

    Thanks to all of you guys, you're a great group of people.

    The variable to refer to the workbook being opened resolved the problem. I also corrected the semicolon instead of the colon in my range, thanks for pointing that out.

    It all makes perfect sense now, but I was pulling my hair out yesterday. Thanks for restoring my sanity

    -Isa

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Ease of Copy Pasting from one workbook to other.
    By shashu999 in forum Excel General
    Replies: 4
    Last Post: 04-04-2014, 05:16 AM
  2. [SOLVED] Copying Active Cell-Row-Range to Other Workbook is pasting an image of copied range
    By jrtraylor in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-18-2013, 01:08 AM
  3. [SOLVED] Copy row contaning matching value to another workbook - Subscript out of range error
    By VBA FTW in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-04-2013, 12:55 PM
  4. [SOLVED] Help pasting Range from one workbook to another
    By Matt3989 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-17-2013, 09:08 PM
  5. [SOLVED] Error when pasting a worksheet from one workbook into another
    By kawhite03 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-01-2012, 02:56 PM
  6. Looping and pasting worksheets into new workbook, error part way through
    By yerromnitsuj in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-06-2011, 02:26 AM
  7. Replies: 0
    Last Post: 08-11-2011, 01:23 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1