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.

 

[DanteAmor]

Welcome to the forum!

On the first sheet you have a form or something similar.
Or what do you have on the first sheet?
Each row on the first sheet is a record. How to know which record to copy to the hospital sheet?

What data do you want to copy and exactly where do you want to paste them?
You could give some examples.

 

[KStevens]

Welcome to the forum!

On the first sheet you have a form or something similar.
Or what do you have on the first sheet?
Each row on the first sheet is a record. How to know which record to copy to the hospital sheet?

What data do you want to copy and exactly where do you want to paste them?
You could give some examples.

Hi,

On the first page is like this the hospital and ward are both drop down lists with all four sites and all wards, each site has their own sheet with the 'A' column and row 2 titles going across the pages. I have put a site page example below this one.

Hospital GGH Ward Antenatal Ward Manager/ Junior Sister Staff Nurses Student Nurses Specialist Nurse Consultant Senior Doctors e.g Registrars Junior Doctors FY1, FY2 HCA.s Therapies Hotel Services, Porters etc Others i.e SW, ECG, Techs, Admin Ambulance, Lab Staff Opportunities for Hand Hygiene 5 9 2 Hand Hygiene Tool No of times Hand Hygiene performed 5 5 2 Compliance Rate for Each Dept. 100% 56% 100% 1) Before Pt Contact Type of Decontamination Missed 2) Before a clean/ aseptic procedure 3) After Body fluid Exposure, 4) After pt contact 5)After contact with the Environment No Staff Observed 6 Bare Below Elbows Observation No Bare Below Elbows 4 Compliance Rate for Each Dept. 67% Total Opportunities of Handwashing [H] 16 Total number of times Hand Hygiene Performed [0] 12 Area/Ward HH Compliance Rate 75% Total No Staff Observed BBE 6 No. Staff BBE 4 Area/Ward BBE Compliance Rate 67% <colgroup><col width="112" style="width: 84pt;"><col width="71" span="4" style="width: 53pt;"><col width="103" style="width: 77pt;"><col width="76" style="width: 57pt;"><col width="71" span="6" style="width: 53pt;"></colgroup><tbody> </tbody>
Site page example
Ward Manager/Junior Sister Staff Nurses Ward Opportunity for compliance (insert %) Total Opportunities Total No of times decontaminated hands Total Staff observed Bare Below Elbow (BBE) No of Staff BBE Area/Ward BBE Compliance % Opps No of times Decontaminated Hands Staff % Compliance No Staff Observed No BBE Compliance % 1) Before Pt Contact 2) Before a clean aseptic Proceedure 3) After Body Fluid Exposure 4.)After Pt Contact 5) After Contact with Pt immediate Surroundings Opps No of times Decontaminated Hands Staff % Compliance No Staff Observed No BBE Compliance % 1) Before Pt Contact 2) Before a clean aseptic Proceedure 3) After Body Fluid Exposure 4.)After Pt Contact 5) After Contact with Pt immediate Surroundings A&E Antenatal Cadog <colgroup><col width="71" span="25" style="width: 53pt;"></colgroup><tbody> </tbody>

<tbody>
</tbody>

Sorry if images not aloud. All figures are made up and nothing is confidential.

Thanks

K. Stevens

 

[DanteAmor]

I don't understand what data to copy and where to paste it.

You can upload a file with complete examples.
Important, on the sheets you must explain what you need.


You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[KStevens]

https://www.dropbox.com/s/gm3s8n1n4yi37ag/TEST HH Audit 2019.xlsx?dl=0

Thank you for you response. I have added in the notes on the first page.

 

[DanteAmor]

Sorry, but I am not understanding.
You want to copy data from the "HH Audit" sheet to the hospital sheet, in your example to the "GGH" sheet
You want to copy the data in yellow, but I don't understand in which cell I should paste each yellow cell.


In the "HH Audit" sheet, in rows 21 and 22 you have some data, is that useful?


I give you some examples and I explain how you continue with the macro.

Sub Copy_Data()
  Dim hh As Worksheet, exist As Boolean, h As Worksheet, sh As Worksheet
  Dim f As Range
  
  Set hh = Sheets("HH Audit")
  If hh.Range("C1") = "" Then
    MsgBox "Enter Hospital", vbCritical
    Exit Sub
  End If
  If hh.Range("F1") = "" Then
    MsgBox "Enter Ward", vbCritical
    Exit Sub
  End If
  exist = False
  For Each h In Sheets
    If LCase(h.Name) = LCase(hh.Range("C1").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("A:A").Find(hh.Range("F1").Value, , xlValues, xlWhole)
  If f Is Nothing Then
    MsgBox "The Ward does not exist", vbCritical
  Else
    'cell destination             cell origin
    sh.Cells(f.Row, "B").Value = hh.Range("B12").Value
    sh.Cells(f.Row, "C").Value = hh.Range("B13").Value
    sh.Cells(f.Row, "D").Value = hh.Range("B14").Value
    '...
    'Continue with the other cells
    '...
  
  End If
    




End Sub

 

[KStevens]

thank you for all your help. I really appreciate it.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

Let me know if you have any doubt.