How to simplify the VBA code from function to send rows into send range

Hi,
I have a code where the function is to send a certain amount of data from a single rows value from one sheet to another sheet by name in a specific cell.
my question is whether function can be simplified if we want to send 2 or more rows because this is the appropriate code below just to send the value from the 2 rows on the 8 column value
And will become a long code if we want to send 50 rows

Dim nextRow As Range
Dim lngCounter As Long

If Range("B3").Value = "" Then Exit Sub 'variable-1
On Error Resume Next
With Sheets(Range("B3").Value) 'variable-2
    Set nextRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With
If Not nextRow Is Nothing Then
  For lngCounter = 1 To 8
    Select Case lngCounter
      Case 1
        nextRow = "'" & Cells(3, 2 + lngCounter).Value 'variabel-3
      Case Else
        nextRow.Offset(0, lngCounter - 1) = Cells(3, 2 + lngCounter).Value 'variabel-4
    End Select
  Next lngCounter
End If

If Range("B4").Value = "" Then Exit Sub
On Error Resume Next
With Sheets(Range("B4").Value)
    Set nextRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With
If Not nextRow Is Nothing Then
  For lngCounter = 1 To 8
    Select Case lngCounter
      Case 1
        nextRow = "'" & Cells(4, 2 + lngCounter).Value
      Case Else
        nextRow.Offset(0, lngCounter - 1) = Cells(4, 2 + lngCounter).Value
    End Select
  Next lngCounter
End If

If the code "send ROWS value from one sheet to other in the next blank row" how if simplified to " send RANGE value from one sheet to other in the next blank row"
Thanks for the help

Hi, herukuncahyono,

maybe try it like this (untested):

Dim nextRow As Range
Dim lngCounter As Long
Dim lngRow As Long

lngRow = 3
Do While Range("B" & lngRow).Value <> ""
  On Error Resume Next
  With Sheets(Range("B" & lngRow).Value) 'variable-2
    Set nextRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
  End With
  If Not nextRow Is Nothing Then
    For lngCounter = 1 To 8
      Select Case lngCounter
        Case 1
          nextRow = "'" & Cells(lngRow, 2 + lngCounter).Value 'variabel-3
        Case Else
          nextRow.Offset(0, lngCounter - 1) = Cells(lngRow, 2 + lngCounter).Value 'variabel-4
      End Select
    Next lngCounter
  End If
  lngRow = lngRow + 1
Loop

I doubt itīs a good idea to have On Error Resume Next in the code.

Ciao,
Holger