Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Mac: Copy rows to Master sheet based on cell condition [Closed]

Report
Posts
4
Registration date
Sunday September 29, 2019
Status
Member
Last seen
October 3, 2019
 -
Posts
2670
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 22, 2020
 -
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
See more

1 reply

Answer 1 / 1
Posts
2670
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 22, 2020
 446
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
Report
Posts
4
Registration date
Sunday September 29, 2019
Status
Member
Last seen
October 3, 2019
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
Report
Posts
4
Registration date
Sunday September 29, 2019
Status
Member
Last seen
October 3, 2019
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
Report
Posts
2670
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 22, 2020
 446
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
Ask a question