Help with a VBA I’m working on

[Nova88]

I’m trying to create a VBA script to help with a monthly report I have to complete.
I have quite a few sheets in a workbook with a named table in each sheet, these are named SheetName_Table with headers of Address and month names Jan to Dec. For example sheet - Level1 , Table - Level1_Table. The script has to run through a report generated by a lighting control program and return the values from its own table (Report_Table). The values come from 3 columns named Status, Test Requirement & Gear fault. If Status is a ✔ then return that value if it says see test requirement then return that column value if it says see gear fault return the gear fault column. I already have an Xlookup formula to do this but I’d like to automate it to loop through the report finding the Light Address in the workbook and return the relevant values from the above columns into each pages table for this month. If anyone can help I’d appreciate it. Screenshots won’t attach as they are apparently too big

 

[Zot]

Hard to imagine and might lead to wrong suggestion. You can create a mock up sample for helpers to visualize or you might not get any reply.

 

[Nova88]

These are some screen shots i was able to upload (Finally)

 

[Nova88]

Sample File.xlsx
	A	B	C	D	E	F	G	H
1079	Promenade	LCM 2000 Bus 1 Ballast 11 Inverter 25	May	12/10/2024	✔
1080	Promenade	LCM 2000 Bus 1 Ballast 9 Inverter 26	Mar	6/10/2024	✔
1081	Promenade	LCM 2000 Bus 1 Ballast 8 Inverter 27	Jul	20/7/2024	See test requirement			38
1082	Promenade	LCM 2000 Bus 1 Ballast 7 Inverter 28	Jun	18/10/2024	See gear fault	13	0
1083	Promenade	LCM 2000 Bus 1 Inverter 29	Nov	5/10/2024	✔
1084	Promenade	LCM 2000 Bus 1 Inverter 30	Oct	27/10/2024	✔
1085	Promenade	LCM 2000 Bus 2 Ballast 9 Inverter 22	Jan	27/10/2024	✔
1086	Promenade	LCM 2000 Bus 2 Ballast 8 Inverter 23	Feb	5/10/2024	✔
1087	Promenade	LCM 2000 Bus 2 Ballast 7 Inverter 24	Jun	13/10/2024	✔
1088	Promenade	LCM 2000 Bus 2 Ballast 6 Inverter 25	Oct	27/10/2024	✔
1089	Promenade	LCM 2000 Bus 2 Ballast 5 Inverter 26	Jul	19/10/2024	✔
1090	Promenade	LCM 2000 Bus 2 Ballast 4 Inverter 27	Sep	25/10/2024	✔
1091	Promenade	LCM 2000 Bus 2 Ballast 3 Inverter 28	Jun	18/10/2024	✔
1092	Promenade	LCM 2000 Bus 2 Ballast 2 Inverter 29	Jun	4/10/2024	✔
1093	Promenade	LCM 2000 Bus 2 Ballast 1 Inverter 30	Aug	20/10/2024	✔
1094	Promenade	LCM 2030 Bus 1 Ballast 23 Inverter 41	May	12/10/2024	✔
1095	Promenade	LCM 2030 Bus 1 Ballast 24 Inverter 42	May	6/10/2024	✔
1096	Promenade	LCM 2030 Bus 1 Ballast 26 Inverter 43	May	4/10/2024	✔
1097	Promenade	LCM 2030 Bus 1 Ballast 25 Inverter 44	Aug	20/10/2024	✔
1098	Promenade	LCM 2030 Bus 1 Ballast 21 Inverter 45	Oct	22/10/2024	See gear fault	13	152
1099	Promenade	LCM 2030 Bus 1 Ballast 6 Inverter 46	Oct	27/10/2024	✔
1100	Promenade	LCM 2030 Bus 1 Ballast 19 Inverter 48	Aug	20/10/2024	✔
1101	Promenade	LCM 2030 Bus 1 Ballast 16 Inverter 50	Oct	27/10/2024	✔
1102	Promenade	LCM 2030 Bus 1 Ballast 15 Inverter 51	Oct	5/10/2024	See test requirement			39
1103	Promenade	LCM 2030 Bus 1 Ballast 12 Inverter 52	Sep	11/10/2024	See test requirement			39
1104	Promenade	LCM 2030 Bus 1 Ballast 10 Inverter 54	Jul	19/10/2024	✔
1105	Promenade	LCM 2030 Bus 1 Ballast 9 Inverter 55	Jun	13/10/2024	✔
Report

Emergency%20Light%20Testing%20Log%20Book%202024(AutoRecovered).xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
7	Unit Location	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov
8	LCM 2000 Bus 1 Ballast 11 Inverter 25	✔	✔	✔	✔	✔	✔	✔	✔	✔	✔
9	LCM 2000 Bus 1 Ballast 9 Inverter 26	✔	✔	✔	✔	✔	✔	✔	✔	✔	✔
10	LCM 2000 Bus 1 Ballast 8 Inverter 27	13	13	13	13	13	13	✔	38	38	38
11	LCM 2000 Bus 1 Ballast 7 Inverter 28	✔	✔	9	9	9	13	13	13	13	13
12	LCM 2000 Bus 1 Inverter 29	✔	✔	✔	✔	✔	✔	✔	✔	✔	✔
13	LCM 2000 Bus 1 Inverter 30	37	✔	✔	✔	✔	✔	✔	✔	✔	✔
14	LCM 2000 Bus 2 Ballast 9 Inverter 22	✔	✔	✔	✔	✔	✔	✔	✔	✔	✔
15	LCM 2000 Bus 2 Ballast 8 Inverter 23	✔	✔	✔	✔	✔	✔	✔	✔	✔	✔
16	LCM 2000 Bus 2 Ballast 7 Inverter 24	✔	✔	✔	✔	✔	✔	✔	✔	✔	✔
17	LCM 2000 Bus 2 Ballast 6 Inverter 25	✔	✔	✔	✔	✔	✔	✔	✔	✔	✔
18	LCM 2000 Bus 2 Ballast 5 Inverter 26	✔	✔	✔	✔	✔	✔	✔	✔	✔	✔
19	LCM 2000 Bus 2 Ballast 4 Inverter 27	✔	✔	✔	✔	✔	✔	✔	✔	✔	✔
20	LCM 2000 Bus 2 Ballast 3 Inverter 28	✔	✔	✔	✔	✔	✔	✔	✔	✔	✔
21	LCM 2000 Bus 2 Ballast 2 Inverter 29	✔	✔	✔	✔	✔	✔	✔	✔	✔	✔
22	LCM 2000 Bus 2 Ballast 1 Inverter 30	✔	✔	✔	✔	✔	✔	✔	✔	✔	✔
23	LCM 2030 Bus 1 Ballast 23 Inverter 41	14	14	✔	✔	✔	✔	✔	✔	✔	✔
24	LCM 2030 Bus 1 Ballast 24 Inverter 42	✔	✔	✔	✔	✔	✔	✔	✔	✔	✔
25	LCM 2030 Bus 1 Ballast 26 Inverter 43	✔	✔	✔	✔	✔	✔	✔	✔	✔	✔
26	LCM 2030 Bus 1 Ballast 25 Inverter 44	✔	✔	✔	✔	✔	✔	✔	✔	✔	✔
27	LCM 2030 Bus 1 Ballast 21 Inverter 45	✔	✔	✔	✔	✔	13	13	13	13	13
28	LCM 2030 Bus 1 Ballast 6 Inverter 46	✔	✔	✔	✔	✔	✔	✔	✔	✔	✔
29	LCM 2030 Bus 1 Ballast 19 Inverter 48	✔	✔	✔	✔	✔	✔	✔	✔	✔	✔
Promenade

Cells with Data Validation
Cell	Allow	Criteria
C8:M29	List	=Tick

Sample File.xlsx
	D
1	Tick:
2	✔
3	✘
4	3
5	9
6	10
7	13
8	14
9	16
10	18
11	23
12	36
13	37
14	38
15	39
16	44
17	45
18	61
19	75
20	79
21	80
22	81
23	88
24	89
25	90
26	91
27	92
28	95
29	D0
30	D1
31	D2
32	G9
33
34
Data

 

[Zot]

You have a workbook with many worksheets. One of the sheet called Result. Other sheet has its own name and one of them is Promenade as shown in example. Other sheet names are not stated here. You want to loop through Status column and transfer the finding to each individual sheets named in column Area (as in picture)

Hope my understanding is correct up this point.

In Promenade sheet, you have Unit Location and month columns. Does the month related to the column Month in sheet Report and has nothing to do with date in sheet Report column D? I think once this is automated, Data Validation is no longer needed, right?

 

[Nova88]

Yeah there’s a lot more sheets in the workbook, I didn’t want to include them all as there’s 30 of them. It needs to loop through the sheet names in Column A finding the addresses that are in column B in those sheets and return the result from column E if it’s a tick, if it as another result then return the result from the column it has written. Yeah preferably if it works I can get rid of the data validation. The months that are in column C are only needed for the yellow filled boxes which I’m going to do a different code for, so it needs to match the month from column D and put the data in the month column on the sheet that matches the floor. I know it seem mad but I go through this manually every month and it takes a fair bit of time. I do have an xlookup formula that speeds it up a bit but I’d like to have other colleagues be able click a button to run a code and not break all the work I’ve done on this book

 

[Zot]

Sorry for late reply. I was busy, I'm not sure if I fully understand everything but here is the preliminary code

Sub CompileReport()

Dim eRow As Long, nCol As Long
Dim cell As Range, rngArea As Range, Found As Range, rngFind As Range
Dim ws As Worksheet, wsReport As Worksheet
Dim wb As Workbook

Set wb = ThisWorkbook
Set wsReport = wb.Sheets("Report")
eRow = wsReport.Range("A1").End(xlDown).Row                ' Get End of row for column A in Sheet Report
Set rngArea = wsReport.Range("A2", "A" & eRow)

For Each cell In rngArea
    Set ws = wb.Sheets(cell.Value)
    eRow = ws.Range("B1").End(xlDown).Row
    Set rngFind = ws.Range("B2", "A" & eRow)
    Set Found = rngFind.Find(wsReport.Range("B" & cell.Row))
    If Not Found Is Nothing Then
        nCol = Month(wsReport.Range("D" & cell.Row)) + 2        ' Add 2 to offset number generated by Month function
        ws.Cells(Found.Row, nCol) = wsReport.Range("E" & cell.Row)
    End If
Next
       
End Sub

Time to go home. End of working hour in my time zone

 

[Nova88]

Sorry for late reply. I was busy, I'm not sure if I fully understand everything but here is the preliminary code

Sub CompileReport()

Dim eRow As Long, nCol As Long
Dim cell As Range, rngArea As Range, Found As Range, rngFind As Range
Dim ws As Worksheet, wsReport As Worksheet
Dim wb As Workbook

Set wb = ThisWorkbook
Set wsReport = wb.Sheets("Report")
eRow = wsReport.Range("A1").End(xlDown).Row                ' Get End of row for column A in Sheet Report
Set rngArea = wsReport.Range("A2", "A" & eRow)

For Each cell In rngArea
    Set ws = wb.Sheets(cell.Value)
    eRow = ws.Range("B1").End(xlDown).Row
    Set rngFind = ws.Range("B2", "A" & eRow)
    Set Found = rngFind.Find(wsReport.Range("B" & cell.Row))
    If Not Found Is Nothing Then
        nCol = Month(wsReport.Range("D" & cell.Row)) + 2        ' Add 2 to offset number generated by Month function
        ws.Cells(Found.Row, nCol) = wsReport.Range("E" & cell.Row)
    End If
Next
      
End Sub

Time to go home. End of working hour in my time zone

Worked like a charm. Thanks for the help. Really appreciate it

 

[Zot]

Worked like a charm. Thanks for the help. Really appreciate it

You're welcomed.