Macro to copy row from one sheet to another based on criteria

[jack.stapleton]

Hi,

I know this has been covered a lot but I just can't seem to get my VBA Code working.

I have a workbook that contains 5 sheets.

The sheet with data is called, "HOSPITAL SIT REGISTER" and the data is entered between B7:U61. The 'U' column is a formula that returns yes/no dependant on the entry of other values.

Where the answer is yes to 'U' I want it to copy the whole row into a second sheet, 'Datasets' from A18.

Currently I have this code set up;

Option Explicit

Sub copy_dbo()
Dim i As Long, lrow As Long

Application.ScreenUpdating = False

With Worksheets("HOSPITAL SIT REGISTER")
lrow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 2 To lrow
If .Range("U7:U61" & i).Value = "Yes" Then .Rows(i).Copy Worksheets("Datasets").Range("A18" & Rows.Count).End(xlUp).Offset(18, 0)
Next i
End With

MsgBox "Done"

Application.ScreenUpdating = True

End Sub

It completes and comes up with "Done" but it is not giving the results and there is nothing on the Datasets page.

Also I believe I may have a problem once it works as I need it to clear any previous values each time before running this code.

Thanks

[humdingaling]

are you able to mock up some data to test with
save me data entering something up which may or may not be correct...

[jack.stapleton]

Hi,

Find attached. As you can see I have had to remove most of the data for confidentiality reasons however I have left the dates and times in place.

As you can see there is the column 'U' at the end which will eventually be hidden as a helper column. There is also two sections, one for metro and one for regional. I will need these two sections with 'Yes' in the U column in separate places. Metro will never have more than 7 'Yes' at any time and Region will never have more than 2 'Yes' at any time.

Hope this makes sense.

Thanks,

[humdingaling]

first thing...your lrow is looking at column A....which is blank

is that by design?

[humdingaling]

secondly
it seems your code calls for the whole row to be copied across...are you sure you want to do that?
it looks like you have a heap of formulas in place for on HOSPITAL SIT DATA SHEET from Column N on wards
are you sure you want to be over writing these?

[jack.stapleton]

This is not by design. I must apoligise I have just been trying to copy codes as I am new to using VBA.

The HOSPITAL SIT DATA SHEET won't be touched by this and only updates from the HOSPITAL SIT REGISTER sheet. But yes I need from the row from column B to U copied into the datasets.

Thanks,

[humdingaling]

Please Login or Register  to view this content.

not sure what you were trying to achieve with
Worksheets("Datasets").Range("A18" & Rows.Count).End(xlUp).Offset(18, 0)

also

if you wanting to append onto datasets then because column A is empty
when you copy data across ...end(xlup) goes up to row 15


i changed the code to work with this attached example
if in real data something is different let me know

[jack.stapleton]

Thanks, that worked as required. What would I need to enter to clear the area before running to copy and paste. As each day the data will change.

Thanks,

[humdingaling]

Please Login or Register  to view this content.

Code if you want to append...however you need something in cell (anything) B17 to start otherwise it will start in row 3

[humdingaling]

Thanks, that worked as required. What would I need to enter to clear the area before running to copy and paste. As each day the data will change.

Thanks,

the 1st code writes over
the 2nd code appends below

if you want to hard "wipe" it clean
just add some code in to do so

maybe something like
Worksheets("datasets").Rows("17:30").Clear

at the beginning

[jack.stapleton]

Brilliant Thanks