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

KStevens660 

Please test on a copy of your workbook:

Sub Copy_Data()
  Dim hh As Worksheet
  Dim sh As Worksheet
  Dim r As Long
  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

  On Error Resume Next
  Set sh = Sheets(CStr(hh.Range("C4").Value))
  On Error GoTo 0
  If sh Is Nothing 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
    r = sh.UsedRange.Rows.Count + 1
    'cell destination                       cell origin
    sh.Range("A" & r).Resize(1, 19).Value = Application.Transpose(hh.Range("C5:C23").Value)
  End If

  Set sh = Sheets("Data")
  r = sh.UsedRange.Rows.Count + 1
  'cell destination                       cell origin
  sh.Range("A" & r).Resize(1, 19).Value = Application.Transpose(hh.Range("C5:C23").Value)

  If hh.Range("C28") = "a specific word" Then
    Set sh = Sheets("B")
    r = sh.UsedRange.Rows.Count + 1
    'cell destination                       cell origin
    sh.Range("A" & r).Resize(1, 19).Value = Application.Transpose(hh.Range("C5:C23").Value)
  End If
End Sub