VBA to copy data from 1 worksheet to another

[nancyching1711]

Hi

May I request for help (vba codes) to copy data from 1 worksheet to another:
Source: sheet1
Range: A1:IJ1000
Detination: sheet9

I tried the following (add "Module 1") but the code cannot work:

Sub CopySource()
    Dim rngSource As Range
    Dim rngTarget As Range
    Dim iRow As Integer

    Set rngSource = Worksheets("Sheet1").Range("A1:IJ100")
    iRow = Worksheets("Sheet9").Cells(Rows.Count, 1).End(xlUp).Row + 1
    Set rngTarget = Worksheets("Sheet9").Range("A" & iRow)
    rngSource.Copy Destination:=rngTarget
End Sub

Thank you for your attention and assistance.

Regards
Nancy