+ Reply to Thread
Results 1 to 3 of 3

run-time error “9”: message saying that the “subscript is out of range”

  1. #1
    Registered User
    Join Date
    05-17-2013
    Location
    Cardiff
    MS-Off Ver
    Excel 2010
    Posts
    5

    run-time error “9”: message saying that the “subscript is out of range”

    Morning everyone,

    I need some urgent help with a macro I am trying to use. Basically, I want information to go from A3:I3 on “payment information” to a new workbook entitled invoices paid. I have got the following macro which seems to work between worksheets fine, but when I try to do it between workbooks I get a run-time error “9”: message saying that the “subscript is out of range”.

    This is the macro:


    Sub CopyHistData()

    Dim SrcSheet As String
    Dim DestSheet As String

    SrcSheet = "[Purchase Ledger Template.xlsx]Payment Information" 'Change to suit
    DestSheet = "[Invoices Paid.xlsx]Payments Made" 'Change to suit

    Sheets(SrcSheet).Range("A3:I3").Copy
    Sheets(DestSheet).Range("A2" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

    Application.CutCopyMode = False

    End Sub


    If anyone is able to offer any advice on what I’m doing wrong it would be very gratefully received.

    Thank you!

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: run-time error “9”: message saying that the “subscript is out of range”

    You can't have Range("A2" & rows.count) you probablywant it to be Range("A" & rows.count). However, that won't give you the subscript out of range error, I assume that is because of the sheet reference, I'm not sure how to do it in that format, I would do it like this:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    05-17-2013
    Location
    Cardiff
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: run-time error “9”: message saying that the “subscript is out of range”

    Genius! Works perfectly, thank you for your help!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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