Insert new row and paste data

[izet99]

Hi, have a little marco code below that work great but I would like to modify destination...

For example, how do I set destination to specific cell, let cell start from C25, insert new row and paste data in it. Basicly, I have data in C24 and C30, I would code below to insert copied data between C24:C30 range. Data that will be inserted varies in size/number of row so I would need push down existing used row in C30. Anybody have any idea to to modify existing code?

Destination code, where I have issue with:

.Range("B" & i).EntireRow.Copy _
 Destination:=ws.Range("C65536").End(xlUp).Offset(1, 0)

Full code below... it distribution data from one sheet to multiple sheet if value in column match.

Sub DistributeData()


Dim i               As Long
Dim LastRow         As Long
Dim ws              As Worksheet
Dim ErrorLog        As String


    With Sheets("all")
        LastRow = .Range("C65536").End(xlUp).Row
        For i = 40 To LastRow
            On Error Resume Next
            Set ws = Sheets(.Range("B" & i).Text)
            On Error GoTo 0
            If ws Is Nothing Then
                ErrorLog = ErrorLog & vbNewLine & _
                "Row:" & i & " Sheet Name: " & .Range("C" & i).Text
            Else
                .Range("B" & i).EntireRow.Copy _
                Destination:=ws.Range("C65536").End(xlUp).Offset(1, 0)
            End If
            Set ws = Nothing
        Next i
    End With
       
    Set ws = Nothing
    
End Sub