+ Reply to Thread
Results 1 to 19 of 19

Subscript Out of Range Error during Copy

  1. #1
    Registered User
    Join Date
    04-25-2009
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    84

    Question Subscript Out of Range Error during Copy

    I have the following VBA code that I use and tried to modify it to accomplish a new task. My goal is to copy the Data from workbook A(range A2:r2) to Workbook B. to a sheet name "Original" and paste the data beginning at( A7:R7). however I'm getting a subscript error 9 and can't seem to find the error. maybe my logic is missing something. I would appreciate your expertise on this issue. See below:

    to add more details: I go to a cloud location to export the data into an excel file on a daily basis, once I get the data on an excel file, then I'm trying to use a vba code to copy and paste to an existing template, into a Sheet called "Original" and copy the data starting at A7:R7.

    Please Login or Register  to view this content.
    please let me know if you have any questions.

    Thanks in advance for your assistance.
    Last edited by Jocote46; 04-24-2018 at 07:51 PM.

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    re: Subscript Out of Range Error during Copy

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Done
    Last edited by sandy666; 04-24-2018 at 07:59 PM.

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Subscript Out of Range Error during Copy

    Hello Jocote46,

    My guess is you have misspelled the worksheet name

    Please Login or Register  to view this content.
    Maybe you meant "AP-C09-Invoices Greater than" ?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  4. #4
    Registered User
    Join Date
    04-25-2009
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    84

    Re: Subscript Out of Range Error during Copy

    Leith,

    No, that's the name on the Sheets when it gets exported from the Icloud source., Also, I don't see the # [CODE] when i was submitting the post, i went back to Edit the post but i still don't see it. Sorry for not including the Tags but don't see the icon anywhere.

  5. #5
    Registered User
    Join Date
    04-25-2009
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    84

    Re: Subscript Out of Range Error during Copy

    Sandy666,

    I tried to put the tags around the code but I do not see the #icon that you're referring to.

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Subscript Out of Range Error during Copy

    Please Login or Register  to view this content.
    Last edited by sandy666; 04-24-2018 at 05:56 PM.

  7. #7
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Subscript Out of Range Error during Copy

    paste your code
    select whole code
    from menu click # button
    that's all

    btw. your start code tag should be [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]

    [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] - this is close tag
    you've twice close tag but not any open tag

    codetags.jpg
    Last edited by sandy666; 04-24-2018 at 06:14 PM.

  8. #8
    Registered User
    Join Date
    04-25-2009
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    84

    Re: Subscript Out of Range Error during Copy

    Thank you Sandy, unfortunately, i do not see that menu bar , but will go ahead and entered manually in the future. I appreciate your help.

  9. #9
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Subscript Out of Range Error during Copy

    maybe go to your profile - general settings and...

    editor.jpg

  10. #10
    Registered User
    Join Date
    04-25-2009
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    84

    Re: Subscript Out of Range Error during Copy

    Sandy666,

    I did go to profile, General settings. i had Standard Editor, switched to Basic Editor and still unable to see menu bar. I used both Explorer and Chrome and nothing seem to work. not sure why I can't see the menu bar. Anyhow, I would really appreciate if anyone can help me with the code.

    Thank you.

  11. #11
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Subscript Out of Range Error during Copy

    Hello Jocote46,

    Change this line...

    From
    Please Login or Register  to view this content.
    To This
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    04-25-2009
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    84

    Re: Subscript Out of Range Error during Copy

    Hi Leith,

    i made the change that you indicated above but i'm still getting the same error, i'm the thinking that perhaps the name of the active worsheets could be the problem. what about if we rename the worksheet to "sheet1" before copying the data?

  13. #13
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Subscript Out of Range Error during Copy

    Hello Jacote46,

    This should help troubleshoot the problem. A message box will be displayed with all the worksheet names in "FinalMaster to Use_new-Copy.xlsx". This will tell us if there is a worksheet named
    "Original" in the workbook.

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    04-25-2009
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    84

    Re: Subscript Out of Range Error during Copy

    Leith,

    I'm attaching sample files both source and destination to see if that helps. i tried running the revise code you provided but it didn't work. still got the same error.

    thank you again.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    04-25-2009
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    84

    Re: Subscript Out of Range Error during Copy

    Hi Leith,

    I tried your code and still get the same error.

  16. #16
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this !


    Working on my side :

    PHP Code: 
    Sub Demo1()
        [
    '[SOURCE DATA.xlsx]AP - C09 - Invoices Greater tha'!$A$1].CurrentRegion.Offset(1).Copy ['[FinalMaster to Use_new - Copy2.xlsx]Original'!$A$7]
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

  17. #17
    Registered User
    Join Date
    04-25-2009
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    84

    Re: Subscript Out of Range Error during Copy

    Marc,

    I replaced the name of the "Source Data.xlsx" with an ActiveWorkbook.Worksheets command and I modified the destination file name (removed the number 2 to match my filename) but i'm getting error 424. I only uploaded the files to give a better idea as to what i needed to accomplish but in reality i would be using a daily file exported from a Icloud location and exported to excel (csv) but i don't save the file, i just open the file to export data so that's why i would like to use the activeworkbook command.

    Please Login or Register  to view this content.
    thanks again for your help.

  18. #18
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Excel basics …


    As you must respect Excel basics !

    So if the source workbook is the active workbook (could be easier if the source worksheet is the active one) :

    PHP Code: 
    Sub Demo1a()
        [
    'AP - C09 - Invoices Greater tha'!$A$1].CurrentRegion.Offset(1).Copy ['[FinalMaster to Use_new - Copy2.xlsx]Original'!$A$7]
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

  19. #19
    Registered User
    Join Date
    04-25-2009
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    84

    Re: Subscript Out of Range Error during Copy

    i keep getting an error, i don't know what was causing it. in order to make it work i made the following changes:

    1. Saved file from the cloud to Source Data on local drive and rename the sheet where data is located to "sheet"
    2. changed the name of the destination file to Master and saved data to "Original"

    the working code looks like this:

    Please Login or Register  to view this content.
    thank you everyone for your time and assistance, i greatly appreciate it.

+ 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. [SOLVED] Run Time Error 9: Subscript Out of Range when Copy and Paste between Workbooks
    By excel989 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-27-2017, 02:10 PM
  2. [SOLVED] Run-time error '9': subscript out of range - error occurs on multiple computers except one
    By BrettE in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-06-2014, 11:19 PM
  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. Defining Array - Runtime error 9, Subscript out of range error
    By MaartenW in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-03-2012, 07:32 AM
  5. Subscript out of range error - save copy error
    By bg18461 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-13-2006, 11:53 AM
  6. [SOLVED] In multiple sheet copy error subscript out of range ?? HELP
    By Eddy Stan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-22-2006, 01:00 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