VBA code to extract data from sheet if criteria is met

[wcf84]

Hi there I am an excel novice and am wanting to create data output to another sheet if criteria is met. I am attempting to create a list of tasks to be undertaken based on the day of the week. I have a cell in sheet 1 which displays the day of the week in along with cells containing job details. If the day of task completion is equal to today I would like an output to a new sheet 2 which copies the entire line of the task details. I hope this makes sense!

 

[kevin9999]

You probably don't need VBA for this, and if you can provide copies of both your input (source) and output (destination) sheets I'm sure you'll find an answer here. Ideally, provide a copy of your sheets using the XL2BB - Excel Range to BBCode or alternatively, share a copy of your workbook (sensitive data hidden/disguised) via Google Drive, Dropbox or similar file sharing platform.

 

[wcf84]

Here goes!

Here is the source sheet. I am just trying to copy across the information that matches the day so when columnm EDD = TUE and the Day is Tue as in row 1 - I require the entire row which has information with tues in row to a new sheet labelled 'discharges' Essentially creating a list of information relating to client discharges. Then when day rotates I can then go to discharge tab press a created button to run a macro and it will pull all of the WED matches etc.

Tuesday, 29 August 2023				Tue
RM		NAME	SURGEON	PHYSICIAN	OTHER	FALLS RISK	NFR	DIET	DESTINATION	EDD
43A
43B
44A		GUNNING	A. CHU			NO		LWD	Home	TBC
44B
45A		SIDES	Z. CHOW			NO			Home	TUE
45B		BABU	Z. CHOW			NO			Home	TUE
46A									Home
46B
47A
47B		SUTHERLAND	Z. CHOW		RTW@1700				Home	TUE
48		MANN	F. O'DONOGHUE	A. RIDGES		NO		LWD	Home	TBC
49		ELIZABETH	I. MEYER			NO		LWD	Home	WED
50		SOMERFIELD	A. CHU			NO		LWD	Home	FRI
51		VESETAS	I. MEYER			NO		DD	Home	WED
52		MUNDY	M. MAMMEN		RTW@2000				Home	TUE
53		DADGE	S. BEWSHER	V. MALIPATEL		NO		LWD	Epworth - Camberwell	WBA
54		PATTERSON	M. MAMMEN		RTW@2100				Home	TUE
55		TSE	M. NIKFARJAM		RTW 20000				Home	TUE

 

[kevin9999]

Thank you for providing a sample using the XL2BB add in. I mentioned that you may not need VBA for this, and with 365 (as per your profile) you have access to the Filter function. With the demonstration below, whatever day (short form) you put in cell E1 on the source sheet, the relevant records will be displayed on the discharge sheet. You may need to adjust the ranges to suit your actual data. You only need to enter the formula once into cell A2 on the discharge sheet. See if this satisfies your needs.

Source sheet:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Tuesday, 29 August 2023				Tue
2
3	RM		NAME	SURGEON	PHYSICIAN	OTHER	FALLS RISK	NFR	DIET	DESTINATION	EDD
4	43A
5	43B
6	44A		GUNNING	A. CHU			NO		LWD	Home	TBC
7	44B
8	45A		SIDES	Z. CHOW			NO			Home	TUE
9	45B		BABU	Z. CHOW			NO			Home	TUE
10	46A									Home
11	46B
12	47A
13	47B		SUTHERLAND	Z. CHOW		RTW@1700				Home	TUE
14	48		MANN	F. O'DONOGHUE	A. RIDGES		NO		LWD	Home	TBC
15	49		ELIZABETH	I. MEYER			NO		LWD	Home	WED
16	50		SOMERFIELD	A. CHU			NO		LWD	Home	FRI
17	51		VESETAS	I. MEYER			NO		DD	Home	WED
18	52		MUNDY	M. MAMMEN		RTW@2000				Home	TUE
19	53		DADGE	S. BEWSHER	V. MALIPATEL		NO		LWD	Epworth - Camberwell	WBA
20	54		PATTERSON	M. MAMMEN		RTW@2100				Home	TUE
21	55		TSE	M. NIKFARJAM		RTW 20000				Home	TUE
22
source

Discharge sheet:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	RM		NAME	SURGEON	PHYSICIAN	OTHER	FALLS RISK	NFR	DIET	DESTINATION	EDD
2	45A	0	SIDES	Z. CHOW	0	0	NO	0	0	Home	TUE
3	45B	0	BABU	Z. CHOW	0	0	NO	0	0	Home	TUE
4	47B	0	SUTHERLAND	Z. CHOW	0	RTW@1700	0	0	0	Home	TUE
5	52	0	MUNDY	M. MAMMEN	0	RTW@2000	0	0	0	Home	TUE
6	54	0	PATTERSON	M. MAMMEN	0	RTW@2100	0	0	0	Home	TUE
7	55	0	TSE	M. NIKFARJAM	0	RTW 20000	0	0	0	Home	TUE
8
discharges

Cell Formulas
Range	Formula
A2:K7	A2	=FILTER(source!$A$4:$K$22,source!K4:K22=source!E1,"")
Dynamic array formulas.

 

[wcf84]

Thank you!

 

[kevin9999]

Thank you!

Happy to help, and thanks for the feedback

 

[wcf84]

Plot twist (sorry) - I had this working beautifully at home on office 365. Taken it to work today and its Excel 2016! Is there a work around?

 

[kevin9999]

The formula alternative for the Filter function can get quite complicated, and not really my area of interest. For a VBA option, please try the following: it is based on your source sheet being exactly as you provided in your sample, with the day (short form) in cell E1. Change the sheet names as appropriate.

Option Explicit
Option Compare Text
Sub Discharge_Day()
    Dim ws1 As Worksheet, ws2 As Worksheet, s As String
    Dim r As Range, c As Range, Dday As Range
    Set ws1 = Worksheets("source")          '<-- *** Change sheet names to suit ***
    Set ws2 = Worksheets("discharges")
    s = ws1.Range("E1").Value2
    
    For Each c In ws1.Range("K4", ws1.Cells(Rows.Count, "K").End(xlUp))
        If c = s Then
            Set Dday = c.Offset(, -10).Resize(1, 11)
            If r Is Nothing Then Set r = Dday Else Set r = Union(r, Dday)
        End If
    Next c
    If r Is Nothing Then MsgBox "No records meet the criteria": Exit Sub
    ws2.UsedRange.Offset(1).Clear
    r.Copy ws2.Range("A2")
End Sub