Previous Topic Next Topic

Mac: Copy rows to Master sheet based on cell condition

Closed
mvdiamond Posts 3 Registration date Sunday September 29, 2019 Status Member Last seen October 3, 2019 - Updated on Oct 3, 2019 at 12:02 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Oct 3, 2019 at 12:01 PM
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.

I know nothing about VBA or macros.

Please help me.

mvdiamond
Related:

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 30, 2019 at 12:28 PM
Hi Mvdiamond,

Not much details provided.

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


Best regards,
Trowa
0
mvdiamond Posts 3 Registration date Sunday September 29, 2019 Status Member Last seen October 3, 2019
Sep 30, 2019 at 11:36 PM
Dear TrowaD,

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
0
mvdiamond Posts 3 Registration date Sunday September 29, 2019 Status Member Last seen October 3, 2019
Oct 1, 2019 at 02:24 PM
Dear Trowa,

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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Oct 3, 2019 at 12:01 PM
Hi Mukesh,

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.

Best regards,
Trowa
0