I am an insolvency professional. I keep details of processes to be conducted on different companies alongwith their time lines. The different columns of dates are conditionally formatted so as to show the processes overdue by 2 days or due in next two days in green. The earlier dates are reflected in red and dates beyond the two day window in yellow.
I maintain different workbooks for different companies. The different sheets have data based on compliance required, process to be conducted, the different reporting formats and a master data sheet to copy data to formats for reporting etc.
I want to prepare a dashboard which should show all processes, reports compliances etc due in the last two days and next two days. Thus my urgent work for the day is available on one sheet for all companies.
The following is assumed:
- Your "dashboard" sheet is called Master. The other sheet names don't matter nor the amount of them.
- The dates are located in column A.
The code below will go through all your sheets' column A and will look for dates equal to today or 4 days in the future and paste them in the first available row on the Master sheet.
Here is the code:
Sub RunMe()
Dim ws As Worksheet, lRow As Integer
For Each ws In Worksheets
If ws.Name <> "Master" Then
lRow = ws.Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Range("A2:A" & lRow)
If cell.Value >= Date And cell.Value <= Date + 4 Then
cell.EntireRow.Copy Sheets("Master").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next cell
End If
Next ws
End Sub
I am sorry for not providing the required details. I could not attach the input sheets because I could not locate the option. The date cells are dispersed all over the sheet and not limited to one column. The dates are in 6 columns at F, H, J, L M O X. Can we change this code to cover all these columns? In one cell before the row, I also need to paste the name of the company which is at cell F3.
I am sorry for the inconvenience. This would be a great help. Thank you so much for the efforts and pains taken by you. With best regards.
I've heard before that using a Windows VBA code on a Mac will not produce the expected results. Unfortunatly, since I'm not familiar with the specific differences between Mac and Windows VBA, I won't be able to help you.
I am sorry for not providing the required details. I could not attach the input sheets because I could not locate the option. The date cells are dispersed all over the sheet and not limited to one column. The dates are in 6 columns at F, H, J, L M O X. Can we change this code to cover all these columns? In one cell before the row, I also need to paste the name of the company which is at cell F3.
I am sorry for the inconvenience. This would be a great help. Thank you so much for the efforts and pains taken by you. With best regards.
Mukesh Verma
I tried putting some dates in column A but the code did not work on my mac. However, it did work on my desktop which is a window based OS.
Does the OS also necessitate a change in coding? If so, I am sorry, I should have told this in advance.
Best regards
Mukesh Verma
- Posts
- 2586
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 20, 2020
390I've heard before that using a Windows VBA code on a Mac will not produce the expected results. Unfortunatly, since I'm not familiar with the specific differences between Mac and Windows VBA, I won't be able to help you.
Best regards,
Trowa