VBA Coding help please

[kstevens]

Hi I have not done any coding in a very long time but I need to work out how to create a code for the following;

I want to enter information on to the first worksheet, then the code to copy and paste the data to the selected hospital and ward entered in the cells on top of the worksheet. Each hospital has its own worksheet with each ward listed in column A.

Can someone please help me get started with this code or give a me link that will allow me to follow and create my own combined code?

Any help is much appreciated.

[kstevens]

Copy of Excel Workbook

[yujin]

Hi, kstevens.
Here's the code that you can activate the target hospital's worksheet and select the target ward's row.
You may modify this code to achieve your goal.



Sub TEST()
Dim Hospital As String
Dim Ward As String
Dim sh As Worksheet
Dim cell As Range

With Worksheets("HH Audit")
Hospital = .Range("C2").Value
Ward = .Range("F2").Value
End With

For Each sh In Worksheets
If sh.Name = Hospital Then
Exit For
End If
Next sh

Set cell = sh.Range("A:A").Find(What:=Ward, LookAt:=xlWhole)
If cell Is Nothing Then
MsgBox Hospital & "'s " & Ward & " is not found."
Else
sh.Activate
cell.EntireRow.Select
End If
End Sub