Copy range from sheet to sheet using VBA

[Jo4x4]

Hi Everybody,

I am currently using this code to copy data from one sheet to the other.

Private Sub Worksheet_Change(ByVal Target As Range)

    Const sOrig As String = "Results" '<- Base sheet
    Const sNew As String = "Club scores"  '<- Destination sheet
    Const CopyCols As String = "b:K"  '<- Columns to copy
    
    With ActiveSheet
        If .Name = sOrig Then
            Application.ScreenUpdating = False
            With Sheets(sNew)
                Intersect(.UsedRange, .Columns(CopyCols)).Clear
            End With
            Intersect(.UsedRange, .Columns(CopyCols)).Copy _
                Destination:=Sheets(sNew).Range("B1")
            Sheets(sOrig).Select
            Application.ScreenUpdating = True
        End If
    End With
End Sub

However, it has now become necessary to copy only range B3:K 62, as there are different calculations done in the lower rows of columns A to K.

Any advise please?

Thanks
Jo
Win XP, Excel 2007

 

[Peter_SSs]

Untested, but try

Private Sub Worksheet_Change(ByVal Target As Range)

    Const sOrig As String = "Results" '<- Base sheet
    Const sNew As String = "Club scores"  '<- Destination sheet
    Const CopyCols As String = "b:K"  '<- Columns to copy
    Const sCopyRows As String = "3:62" '<- Rows to copy
    
    With ActiveSheet
        If .Name = sOrig Then
            Application.ScreenUpdating = False
            With Sheets(sNew)
                Intersect(.UsedRange, .Columns(CopyCols)).Clear
            End With
            Intersect(.UsedRange, .Columns(CopyCols), .Rows(sCopyRows)).Copy _
                Destination:=Sheets(sNew).Range("B1")
            Sheets(sOrig).Select
            Application.ScreenUpdating = True
        End If
    End With
End Sub

 

[Peter_SSs]

Or perhaps this

Private Sub Worksheet_Change(ByVal Target As Range)

    Const sOrig As String = "Results" '<- Base sheet
    Const sNew As String = "Club scores"  '<- Destination sheet
    Const CopyRng As String = "B3:K62"  '<- Range to copy
    
    With ActiveSheet
        If .Name = sOrig Then
            Application.ScreenUpdating = False
            With Sheets(sNew)
                Intersect(.UsedRange, .Columns(CopyCols)).Clear
            End With
            Intersect(.UsedRange, .Range(CopyRng)).Copy _
                Destination:=Sheets(sNew).Range("B1")
            Sheets(sOrig).Select
            Application.ScreenUpdating = True
        End If
    End With
End Sub

 

[Jo4x4]

Thanks Peter!

Jo