VBA to copy and paste information from c4:c25 in worksheet to specific tabs based on C4

Hi

1. I have previously managed to copy and paste cells from one worksheet (named: Form) to another Worksheet (selected in Cell C4). I have managed to get it to copy the column into a row on the correct sheet, but I can't get it to go to the next available line.

2. I also want it to go to another tab, where all the data will be stored (in the same workbook, tab = "Data")

3. If one of the Cells (C28) = a specific word I want the data to also be copy and pasted into the tab i.e "B"

this is my current code:

Sub Copy_Data()
  Dim hh As Worksheet, exist As Boolean, h As Worksheet, sh As Worksheet
  Dim f As Range
  Set hh = Sheets("Form")
  
 If hh.Range("C4") = "" Then
    MsgBox "Enter Hospital", vbCritical
    Exit Sub
  End If
  
  If hh.Range("c5") = "" Then
    MsgBox "Enter Ward", vbCritical
    Exit Sub
  End If
  
  exist = False
  For Each h In Sheets
    If LCase(h.Name) = LCase(hh.Range("C4").Value) Then
      Set sh = h
      exist = True
      Exit For
    End If
    
  Next
  If exist = False Then
    MsgBox "The sheet does not exist", vbCritical
    Exit Sub
  End If
  
  Set f = sh.Range("A1").Find(hh.Range("c4").Value, , xlValues, xlWhole)
  If f Is Nothing Then
    MsgBox "This does not exist", vbCritical

  Else
    'Copy data to Site Sheet'
    'cell destination             cell origin
    sh.Cells(Rows.Count, 1, "a:u").Value = hh.Range("C4:c23").Value
   
 End If
 End Sub

Any help is much appreciated.

The code in the attachment is different than the listed one.

Try instead of:

    sh.Cells(Rows.Count, 1, "a:u").Value = hh.Range("C4:c23").Value

such code

       sh.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(1, 19).Value = Application.Transpose(hh.Range("C5:C23").Value)

Check if column names in Ward1 are the same as rownames in Form, (at some point one item is skipped)
So treat above as a idea not a ready made solution (I started from C5 because c4 describes sheet name and is not present in a sheet

       sh.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(1, 19).Value = Application.Transpose(hh.Range("C5:C23").Value)
       sheets("Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(1, 19).Value = Application.Transpose(hh.Range("C5:C23").Value)
       if hh.Range("C18")="Urinary tract" then  sheets("Urinary Tract").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(1, 19).Value = Application.Transpose(hh.Range("C5:C23").Value)