Results 1 to 7 of 7

Subscript out of Range ? Run time error 9

Threaded View

  1. #1
    Registered User
    Join Date
    11-23-2023
    Location
    Yorkshire, England
    MS-Off Ver
    Office 365
    Posts
    22

    Subscript out of Range ? Run time error 9

    Can anyone suggest why I get a subscript out of range error 9 ?

    My code is :
    Sub CopyDataToSheet2AndMaster()
        Dim wsSource As Worksheet
        Dim wsTarget1 As Worksheet
        Dim wsTarget2 As Worksheet
        Dim lastRow As Long
        
        
        ' Set references to the source and target worksheets
        Set wsSource = ThisWorkbook.Sheets("IncidentsForm")         :mad: ERROR HAPPENS HERE (Subscript out of Range, Run time error 9)
        Set wsTarget1 = ThisWorkbook.Sheets("Incidents")
        Set wsTarget2 = ThisWorkbook.Sheets("IncidentsMaster")
        
        Sheets("IncidentsForm").Select
        
        ' Find the last row with data in column A of Sheet1
        lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
        
        ' Copy data from columns A to X in IncidentsForm to the next available row in Incidents sheet
        wsSource.Range("A3:X" & lastRow).Copy wsTarget1.Cells(wsTarget1.Rows.Count, "A").End(xlUp).Offset(1)
        
        ' Copy data from columns A to X in IncidentsForm to the next available row in IncidentsMaster sheet
        wsSource.Range("A3:X" & lastRow).Copy wsTarget2.Cells(wsTarget2.Rows.Count, "A").End(xlUp).Offset(1)
        
        ' Clear the original data in IncidentsForm
        wsSource.Range("A3:X" & lastRow).ClearContents
        
        ' Optionally, you can clear the clipboard
        ' Application.CutCopyMode = False
        
        ' Print a success message
        MsgBox "Data copied from IncidentsForm to Incidents and Master sheet, and original data cleared."
    End Sub
    The macro is supposed to copy data in a Table on the IncidentsForm Tab, (Table currently has data in it from A3 to X17, table header is in row 2, and the table is called Input)
    I want it to copy into a new line at the bottom of the data in the Incidents tab, and also a copy of the data into the IncidentsMaster tab and then clear the data.

    I have triple checked the sheet names, and they are correct, so why is it not able to Set the Worksheet ?

    Any idea's ?

    Many thanks

    Rich
    Last edited by 6StringJazzer; 03-20-2024 at 10:32 AM. Reason: please use code tags

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
    By josros in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-17-2021, 05:37 PM
  2. Error code "run time error 9: subscript out of range
    By iBennett93 in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 06-28-2021, 05:03 AM
  3. [SOLVED] Run Time Error 9 - Subscript out of range
    By kersplash in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-25-2018, 02:05 AM
  4. Run Time error 9 Subscript out of range
    By kaseyleigh in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-28-2016, 08:57 AM
  5. [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
  6. run-time error '9': Subscript out of range - WHY??
    By pastotnikr in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-31-2013, 03:31 PM
  7. 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

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