+ Reply to Thread
Results 1 to 2 of 2

Run time error 9 subscript out of range

  1. #1
    Registered User
    Join Date
    06-30-2015
    Location
    chennai
    MS-Off Ver
    2010
    Posts
    1

    Post Run time error 9 subscript out of range

    Hi,

    Am trying to copy paste of data from one sheet to another sheet but got out of range error. Could any one help me... here i pasted the code

    Sub copycolumns()
    Dim lastrow As Long, erow As Long

    lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastrow
    Sheet1.Cells(i, 1).Copy
    erow = Sheet13.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

    Sheet1.Paste Destination = Worksheets("Sheet13").Cells(erow, 1)

    Sheet1.Cells(i, 2).Copy
    Sheet1.Paste Destination = Worksheets("Sheet13").Cells(erow, 2)

    Sheet1.Cells(i, 3).Copy
    Sheet1.Paste Destination = Worksheets("Sheet13").Cells(erow, 3)

    Sheet1.Cells(i, 4).Copy
    Sheet1.Paste Destination = Worksheets("Sheet13").Cells(erow, 4)

    Sheet1.Cells(i, 5).Copy
    Sheet1.Paste Destination = Worksheets("Sheet13").Cells(erow, 5)

    Sheet1.Cells(i, 6).Copy
    Sheet1.Paste Destination = Worksheets("Sheet13").Cells(erow, 6)

    Sheet1.Cells(i, 7).Copy
    Sheet1.Paste Destination = Worksheets("Sheet13").Cells(erow, 7)

    Sheet1.Cells(i, 8).Copy
    Sheet1.Paste Destination = Worksheets("Sheet13").Cells(erow, 8)

    Sheet1.Cells(i, 9).Copy
    Sheet1.Paste Destination = Worksheets("Sheet13").Cells(erow, 9)

    Sheet1.Cells(i, 10).Copy
    Sheet1.Paste Destination = Worksheets("Sheet13").Cells(erow, 10)

    Next i

    Application.CutCopyMode = False
    Sheet13.Columns().AutoFit
    Range("A1").Select

    End Sub


    Thanks
    JK

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Run time error 9 subscript out of range

    Try using Sheets("Sheet13") instead of just Sheet13

    This line maybe causing errors.

    And, why cant you copy all at once and past at once, instead of having different code lines?
    Cheers!
    Deep Dave

+ 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. Subscript out of range- Run time Error
    By astha.malik1 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-19-2014, 10:22 AM
  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] Run-time Error '9': Subscript out of Range
    By Veena Anoohya in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-19-2014, 05:06 AM
  4. Run-time Error '9': Subscript out of Range
    By bcn1988 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-11-2012, 03:01 PM
  5. Run-time error 9 subscript out of range
    By thebigman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-01-2010, 05:00 PM

Tags for this Thread

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