Help please - Code keeps pasting 34 rows instead of 1

[K_Stevs1]

Hi my I want my code to copy and paste the column of data, to the next empty row in the stated sheet.

It is currently copying the data to the row in the correct sheet, but doing 34 times. Can someone please help advise where I am going wrong?

Sub CopyData()
   
    Application.ScreenUpdating = False
   
    Dim srcWS As Worksheet, team As Range, lRow As Long, lRow2 As Long
    Set srcWS = Sheets("Form")
    lRow = Range("B" & Rows.Count).End(xlUp).Row
   
        With Sheets(Range("B5").Value)
            lRow2 = .Range("A" & .Rows.Count).End(xlUp).Row + 1
            .Range("A" & lRow2).Resize(lRow).Value = Range("B8")
            .Range("B" & lRow2).Resize(lRow).Value = Range("B9")
            .Range("C" & lRow2).Resize(lRow).Value = Range("B10")
            .Range("D" & lRow2).Resize(lRow).Value = Range("B11")
            .Range("E" & lRow2).Resize(lRow).Value = Range("B12")
            .Range("F" & lRow2).Resize(lRow).Value = Range("B13")
            .Range("G" & lRow2).Resize(lRow).Value = Range("B14")

        End With
     
    Application.ScreenUpdating = True
   
    Range("B5").ClearContents
    Range("E5").ClearContents
    Range("H5").ClearContents
    Range("B8", "B19").ClearContents

 MsgBox ("Submitted Successfully")
End Sub

Picture of the form in image 1
Picture 2 is what is coming out as in the desired tab

 

[Fluff]

It's the part in red that is doing it

.Range("A" & lRow2).Resize(lRow).Value = Range("B8")

you need to remove it from all lines.

 

[Fluff]

You could also slim down the code like

        With Sheets(Range("B5").Value)
            lRow2 = .Range("A" & .Rows.count).End(xlUp).Row + 1
            .Range("A" & lRow2).Resize(, 7).Value = Application.Transpose(Range("B8:B14").Value)
        End With

 

[K_Stevs1]

You could also slim down the code like

        With Sheets(Range("B5").Value)
            lRow2 = .Range("A" & .Rows.count).End(xlUp).Row + 1
            .Range("A" & lRow2).Resize(, 7).Value = Application.Transpose(Range("B8:B14").Value)
        End With

Thank you so much worked perfectly

 

[Fluff]

You're welcome & thanks for the feedback.