VBA Coding Help please

KStevens

New Member
Joined
Aug 26, 2019
Messages
4
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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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.
 
Last edited:
Upvote 0
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.
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 1001"]
<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>[TR]
[TD="class: xl88, width: 112"] [/TD]
[TD="class: xl89, width: 71"]Hospital[/TD]
[TD="class: xl115, width: 142, colspan: 2, align: center"]GGH[/TD]
[TD="class: xl90, width: 71"]Ward[/TD]
[TD="class: xl96, width: 103"]Antenatal[/TD]
[TD="class: xl91, width: 76"] [/TD]
[TD="class: xl91, width: 71"] [/TD]
[TD="class: xl91, width: 71"] [/TD]
[TD="class: xl91, width: 71"] [/TD]
[TD="class: xl91, width: 71"] [/TD]
[TD="class: xl91, width: 71"] [/TD]
[TD="class: xl92, width: 71"] [/TD]
[/TR]
[TR]
[TD="class: xl93"][/TD]
[TD="class: xl85, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C5D9F1]#C5D9F1[/URL] , align: center"]Ward Manager/ Junior Sister[/TD]
[TD="class: xl86, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C5D9F1]#C5D9F1[/URL] , align: center"]Staff Nurses[/TD]
[TD="class: xl87, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C5D9F1]#C5D9F1[/URL] , align: center"]Student Nurses[/TD]
[TD="class: xl86, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C5D9F1]#C5D9F1[/URL] , align: center"]Specialist Nurse [/TD]
[TD="class: xl87, width: 103, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C5D9F1]#C5D9F1[/URL] , align: center"]Consultant[/TD]
[TD="class: xl95, width: 76, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C5D9F1]#C5D9F1[/URL] , align: center"]Senior Doctors e.g Registrars[/TD]
[TD="class: xl72, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C5D9F1]#C5D9F1[/URL] , align: center"]Junior Doctors FY1, FY2[/TD]
[TD="class: xl71, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C5D9F1]#C5D9F1[/URL] , align: center"]HCA.s[/TD]
[TD="class: xl72, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C5D9F1]#C5D9F1[/URL] , align: center"]Therapies[/TD]
[TD="class: xl71, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C5D9F1]#C5D9F1[/URL] , align: center"]Hotel Services, Porters etc [/TD]
[TD="class: xl73, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C5D9F1]#C5D9F1[/URL] , align: center"]Others i.e SW, ECG, Techs, Admin Ambulance, Lab Staff[/TD]
[TD="class: xl74, width: 71, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl75, width: 112, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C5D9F1]#C5D9F1[/URL] , align: center"]Opportunities for Hand Hygiene [/TD]
[TD="class: xl76, align: center"]5[/TD]
[TD="class: xl76, align: center"]9[/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"]2[/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl112, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C5D9F1]#C5D9F1[/URL] , align: center"]Hand Hygiene Tool [/TD]
[/TR]
[TR]
[TD="class: xl75, width: 112, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C5D9F1]#C5D9F1[/URL] , align: center"]No of times Hand Hygiene performed [/TD]
[TD="class: xl76, align: center"]5[/TD]
[TD="class: xl76, align: center"]5[/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"]2[/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl77, width: 112, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C5D9F1]#C5D9F1[/URL] , align: center"]Compliance Rate for Each Dept.[/TD]
[TD="class: xl83, align: center"]100%[/TD]
[TD="class: xl83, align: center"]56%[/TD]
[TD="class: xl83, align: center"] [/TD]
[TD="class: xl83, align: center"] [/TD]
[TD="class: xl83, align: center"]100%[/TD]
[TD="class: xl83, align: center"] [/TD]
[TD="class: xl83, align: center"] [/TD]
[TD="class: xl83, align: center"] [/TD]
[TD="class: xl83, align: center"] [/TD]
[TD="class: xl83, align: center"] [/TD]
[TD="class: xl83, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl94, width: 112, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCD5B4]#FCD5B4[/URL] , align: center"]1) Before Pt Contact[/TD]
[TD="class: xl78, align: center"] [/TD]
[TD="class: xl78, align: center"] [/TD]
[TD="class: xl78, align: center"] [/TD]
[TD="class: xl78, align: center"] [/TD]
[TD="class: xl78, align: center"] [/TD]
[TD="class: xl78, align: center"] [/TD]
[TD="class: xl78, align: center"] [/TD]
[TD="class: xl78, align: center"] [/TD]
[TD="class: xl78, align: center"] [/TD]
[TD="class: xl78, align: center"] [/TD]
[TD="class: xl78, align: center"] [/TD]
[TD="class: xl106, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCD5B4]#FCD5B4[/URL] , align: center"]Type of Decontamination Missed [/TD]
[/TR]
[TR]
[TD="class: xl94, width: 112, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCD5B4]#FCD5B4[/URL] , align: center"]2) Before a clean/ aseptic procedure[/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl94, width: 112, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCD5B4]#FCD5B4[/URL] , align: center"]3) After Body fluid Exposure, 4) After pt contact 5)After contact with the Environment [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[TD="class: xl76, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl79, width: 112, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCC0DA]#CCC0DA[/URL] , align: center"]No Staff Observed[/TD]
[TD="class: xl80, align: center"]6[/TD]
[TD="class: xl80, align: center"] [/TD]
[TD="class: xl80, align: center"] [/TD]
[TD="class: xl80, align: center"] [/TD]
[TD="class: xl80, align: center"] [/TD]
[TD="class: xl80, align: center"] [/TD]
[TD="class: xl80, align: center"] [/TD]
[TD="class: xl80, align: center"] [/TD]
[TD="class: xl80, align: center"] [/TD]
[TD="class: xl80, align: center"] [/TD]
[TD="class: xl80, align: center"] [/TD]
[TD="class: xl109, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B1A0C7]#B1A0C7[/URL] , align: center"]Bare Below Elbows Observation[/TD]
[/TR]
[TR]
[TD="class: xl79, width: 112, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCC0DA]#CCC0DA[/URL] , align: center"]No Bare Below Elbows[/TD]
[TD="class: xl80, align: center"]4[/TD]
[TD="class: xl80, align: center"] [/TD]
[TD="class: xl80, align: center"] [/TD]
[TD="class: xl80, align: center"] [/TD]
[TD="class: xl80, align: center"] [/TD]
[TD="class: xl80, align: center"] [/TD]
[TD="class: xl80, align: center"] [/TD]
[TD="class: xl80, align: center"] [/TD]
[TD="class: xl80, align: center"] [/TD]
[TD="class: xl80, align: center"] [/TD]
[TD="class: xl80, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl81, width: 112, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCC0DA]#CCC0DA[/URL] , align: center"]Compliance Rate for Each Dept.[/TD]
[TD="class: xl83, align: center"]67%[/TD]
[TD="class: xl83, align: center"] [/TD]
[TD="class: xl83, align: center"] [/TD]
[TD="class: xl83, align: center"] [/TD]
[TD="class: xl83, align: center"] [/TD]
[TD="class: xl83, align: center"] [/TD]
[TD="class: xl83, align: center"] [/TD]
[TD="class: xl83, align: center"] [/TD]
[TD="class: xl83, align: center"] [/TD]
[TD="class: xl83, align: center"] [/TD]
[TD="class: xl83, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl82, width: 112, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8DB4E2]#8DB4E2[/URL] , align: center"]Total Opportunities of Handwashing [H][/TD]
[TD="class: xl78, align: center"]16[/TD]
[TD="class: xl97, colspan: 11, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl82, width: 112, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8DB4E2]#8DB4E2[/URL] , align: center"]Total number of times Hand Hygiene Performed [0][/TD]
[TD="class: xl76, align: center"]12[/TD]
[/TR]
[TR]
[TD="class: xl82, width: 112, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8DB4E2]#8DB4E2[/URL] , align: center"]Area/Ward HH Compliance Rate[/TD]
[TD="class: xl83, align: center"]75%[/TD]
[/TR]
[TR]
[TD="class: xl81, width: 112, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCC0DA]#CCC0DA[/URL] , align: center"]Total No Staff Observed BBE[/TD]
[TD="class: xl84, align: center"]6[/TD]
[/TR]
[TR]
[TD="class: xl81, width: 112, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCC0DA]#CCC0DA[/URL] , align: center"]No. Staff BBE[/TD]
[TD="class: xl84, align: center"]4[/TD]
[/TR]
[TR]
[TD="class: xl81, width: 112, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCC0DA]#CCC0DA[/URL] , align: center"]Area/Ward BBE Compliance Rate[/TD]
[TD="class: xl83, align: center"]67%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Site page example[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 1775"]
<colgroup><col width="71" span="25" style="width: 53pt;"></colgroup><tbody>[TR]
[TD="class: xl97, width: 497, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7DEE8]#B7DEE8[/URL] , colspan: 7, align: center"] [/TD]
[TD="class: xl91, width: 639, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7DEE8]#B7DEE8[/URL] , colspan: 9, align: center"]Ward Manager/Junior Sister[/TD]
[TD="class: xl93, width: 639, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7DEE8]#B7DEE8[/URL] , colspan: 9, align: center"]Staff Nurses[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFFF]#CCFFFF[/URL] , align: center"]Ward[/TD]
[TD="class: xl77, width: 71, bgcolor: silver, align: center"]Opportunity for compliance (insert %)[/TD]
[TD="class: xl75, width: 71, align: center"]Total Opportunities[/TD]
[TD="class: xl75, width: 71, align: center"]Total No of times decontaminated hands[/TD]
[TD="class: xl75, width: 71, align: center"]Total Staff observed Bare Below Elbow (BBE)[/TD]
[TD="class: xl75, width: 71, align: center"]No of Staff BBE[/TD]
[TD="class: xl75, width: 71, align: center"]Area/Ward BBE Compliance % [/TD]
[TD="class: xl82, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7DEE8]#B7DEE8[/URL] , align: right"]Opps[/TD]
[TD="class: xl82, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7DEE8]#B7DEE8[/URL] , align: right"]No of times Decontaminated Hands[/TD]
[TD="class: xl84, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7DEE8]#B7DEE8[/URL] , align: center"]Staff % Compliance[/TD]
[TD="class: xl83, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFFF]#CCFFFF[/URL] , align: center"]No Staff Observed[/TD]
[TD="class: xl83, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFFF]#CCFFFF[/URL] , align: center"]No BBE[/TD]
[TD="class: xl78, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFFF]#CCFFFF[/URL] , align: center"]Compliance %[/TD]
[TD="class: xl73, width: 71, bgcolor: #FFFF99"]1) Before Pt Contact[/TD]
[TD="class: xl74, width: 71, bgcolor: #FFFF99"]2) Before a clean aseptic Proceedure[/TD]
[TD="class: xl74, width: 71, bgcolor: #FFFF99"]3) After Body Fluid Exposure 4.)After Pt Contact 5) After Contact with Pt immediate Surroundings[/TD]
[TD="class: xl85, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7DEE8]#B7DEE8[/URL] , align: right"]Opps[/TD]
[TD="class: xl85, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7DEE8]#B7DEE8[/URL] , align: right"]No of times Decontaminated Hands[/TD]
[TD="class: xl89, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7DEE8]#B7DEE8[/URL] , align: center"]Staff % Compliance[/TD]
[TD="class: xl86, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFFF]#CCFFFF[/URL] , align: center"]No Staff Observed[/TD]
[TD="class: xl86, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFFF]#CCFFFF[/URL] , align: center"]No BBE[/TD]
[TD="class: xl86, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFFF]#CCFFFF[/URL] , align: center"]Compliance %[/TD]
[TD="class: xl87, width: 71, bgcolor: #FFFF99"]1) Before Pt Contact[/TD]
[TD="class: xl88, width: 71, bgcolor: #FFFF99"]2) Before a clean aseptic Proceedure[/TD]
[TD="class: xl88, width: 71, bgcolor: #FFFF99"]3) After Body Fluid Exposure 4.)After Pt Contact 5) After Contact with Pt immediate Surroundings[/TD]
[/TR]
[TR]
[TD="class: xl79"]A&E[/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[/TR]
[TR]
[TD="class: xl81"]Antenatal[/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[/TR]
[TR]
[TD="class: xl80"]Cadog[/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[TD="class: xl90"] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

Thanks

K. Stevens
 
Upvote 0
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.
 
Upvote 0
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.

Code:
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
 
Upvote 0
I'm glad to help you. Thanks for the feedback.

Let me know if you have any doubt.
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,314
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top