Copy Paste a range instead of row

[st1]

Hi Everyone
I have a macro i have been using for years no problem.
But i would like change it to copy a range of cells instead of whole row.
I have been tying to change it to no success
Please help!

Dim LSearchRow As Integer
    Dim LCopyToRow As Integer


    On Error GoTo Err_Execute


    Sheets("SHOP LOAD").Select


    'Start search in row 7
    LSearchRow = 7
    
    Sheets("SHOP LOAD").Select
    'Start copying data to row 2 in Sheet2 (row counter variable)
    LCopyToRow = 4


    While Len(Range("A" & CStr(LSearchRow)).Value) > 0


        'If value in column H = "COMPLETE", copy entire row to April18
        If Range("I" & CStr(LSearchRow)).Value = "COMPLETE" Then


            'Select row in Sheet to copy
            Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
            Selection.Copy


            'Paste row into April18 in next row
            Sheets("April18").Select
            LCopyToRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).PasteSpecial xlPasteValues


            'Move counter to next row
            LCopyToRow = LCopyToRow + 1


            'Go back to Sheet2 to continue searching
            Sheets("SHOP LOAD").Select


        End If

           LSearchRow = LSearchRow + 1

 

[Takae]

Hope this helps.

Dim LSearchRow As Integer, LC As Long
Dim LCopyToRow As Integer
Dim wsS As Worksheet, wsA As Worksheet
Set wsS = Sheets("SHOP LOAD")
Set wsA = Sheets("April18")
With wsS
    On Error GoTo Err_Execute
    'Start search in row 7
    LSearchRow = 7
    LCopyToRow = 4
    While Len(.cells(LSearchRow, 1).Value) > 0
        'If value in column H = "COMPLETE", copy entire row to April18
        If .cells(LSearchRow, 9).Value = "COMPLETE" Then
            LC = .cells(LSearchRow, Columns.count).End(xlToLeft).column
            LCopyToRow = wsA.cells(Rows.count, 1).End(xlUp).Row + 1
            'Select row in Sheet to copy
            .Rows(LSearchRow).copy
            wsA.Range(.cells(LCopyToRow, 1), .cells(LCopyToRow, LC)).Value = .Range(.cells(LSearchRow, 1), .cells(LSearchRow, LC)).Value
            'Move counter to next row
            LSearchRow = LSearchRow + 1
        End If
End With

 

[st1]

Hi
I tried the suggested but got some errors i tried to modify but still get error.
Please see my modified code.

' onemacro4 Macro
'
Dim LSearchRow As Integer, LC As Long
Dim LCopyToRow As Integer
Dim wsS As Worksheet, wsA As Worksheet
Set wsS = Sheets("SHOP LOAD")
Set wsA = Sheets("April18")


With wsS
    On Error GoTo Err_Execute
    'Start search in row 7
    LSearchRow = 7
    LCopyToRow = 4
    
    While Len(.Cells(LSearchRow, 1).Value) > 0
        'If value in column H = "COMPLETE", copy entire row to April18
        If .Cells(LSearchRow, 9).Value = "COMPLETE" Then
            LC = .Cells(LSearchRow, Columns.Count).End(xlToLeft).Column
            LCopyToRow = wsA.Cells(Rows.Count, 1).End(xlUp).Row + 1
            'Select row in Sheet to copy
            .Rows(LSearchRow).Copy
            wsA.Range(.Cells(LCopyToRow, 1), .Cells(LCopyToRow, LC)).Value = .Range(.Cells(LSearchRow, 1), .Cells(LSearchRow, LC)).Value
           LSearchRow = LSearchRow + 1
        End If
 
            
Wend
End With


Exit Sub


Err_Execute:
    MsgBox "An error occurred."




End Sub