ChrisMac1
New Member
- Joined
- Jul 15, 2024
- Messages
- 21
- Office Version
- 365
- Platform
- Windows
Hi there, I am hoping you can help me with a conundrum that's driving me nuts currently.
Please note...
I am in the UK so expect responses during daylight hours local time. I cannot share this ACTUAL workbook due to the confidential nature of it's contents, and using DROPBOX etc is a pain as my work laptop is locked down tight, just FYI. I will attempt to attach a link to a test file shortly.
General idea...
So the workbook I have has a tab called DATA. Into this is pasted a report from a system on a regular basis. I want to keep the previous weeks' comments and status text, that's found in two columns on four different sheets, on the new report.
The way my report creating macros work...
Via some macros, the rows of data from the DATA tab are copied to one of four other sheets, which sheet depends on some variables. After the macros have run, all the rows from the DATA sheet have been copied across the four sheets. Let's say each reporting sheet is named OR, AN, AA, SG.
On each row of all four reporting sheets, as well as the DATA sheet, there are two columns STATUS and COMMENTS.
Every sheet has one row of headers with filters.
What I need...
Every time I paste in new data to the DATA tab, (the entire tab is cleared and and new data pasted in each week) I need a macro to run and compare every new entry against all those existing entries on each of the 4 sheets OR, AN, AA and SG. If the macro finds the same JOB REFERENCE number in column A of the data sheet, in column A of one of the sheets OR, AN, AA and SG, then I need the macro to copy the cells from AG and AH in that row of the sheet and paste them into the corresponding columns and row back on the DATA tab. This will mean I have last weeks comments for that job on this weeks data.
Example...
Assuming I've managed to get the example I created attached, you will see a rough replica of the report. In this example, on sheets OR, EN, AA and SG there are last weeks jobs. Each job has a unique number. On the DATA tab I have added the new data for this week. The new macro has run and you can see that it found four jobs that were on one of the other four tabs, AAAA, HHHH, OOOO and VVVV. As those jobs are on the report tabs the macro has copied the STATUS and COMMENTS from columns AG and AH of the report tabs, back to the same columns on the DATA tab.
When I run my report building macros (already built and working) all the jobs on each reporting tab will be deleted. Then all the jobs on the data tab, some now with the comments and statuses, will be copied onto the correct tab. Users will see new a new report but some entries will retain their comments and statuses from last week.
Please note...
I am in the UK so expect responses during daylight hours local time. I cannot share this ACTUAL workbook due to the confidential nature of it's contents, and using DROPBOX etc is a pain as my work laptop is locked down tight, just FYI. I will attempt to attach a link to a test file shortly.
General idea...
So the workbook I have has a tab called DATA. Into this is pasted a report from a system on a regular basis. I want to keep the previous weeks' comments and status text, that's found in two columns on four different sheets, on the new report.
The way my report creating macros work...
Via some macros, the rows of data from the DATA tab are copied to one of four other sheets, which sheet depends on some variables. After the macros have run, all the rows from the DATA sheet have been copied across the four sheets. Let's say each reporting sheet is named OR, AN, AA, SG.
On each row of all four reporting sheets, as well as the DATA sheet, there are two columns STATUS and COMMENTS.
Every sheet has one row of headers with filters.
What I need...
Every time I paste in new data to the DATA tab, (the entire tab is cleared and and new data pasted in each week) I need a macro to run and compare every new entry against all those existing entries on each of the 4 sheets OR, AN, AA and SG. If the macro finds the same JOB REFERENCE number in column A of the data sheet, in column A of one of the sheets OR, AN, AA and SG, then I need the macro to copy the cells from AG and AH in that row of the sheet and paste them into the corresponding columns and row back on the DATA tab. This will mean I have last weeks comments for that job on this weeks data.
Example...
Assuming I've managed to get the example I created attached, you will see a rough replica of the report. In this example, on sheets OR, EN, AA and SG there are last weeks jobs. Each job has a unique number. On the DATA tab I have added the new data for this week. The new macro has run and you can see that it found four jobs that were on one of the other four tabs, AAAA, HHHH, OOOO and VVVV. As those jobs are on the report tabs the macro has copied the STATUS and COMMENTS from columns AG and AH of the report tabs, back to the same columns on the DATA tab.
When I run my report building macros (already built and working) all the jobs on each reporting tab will be deleted. Then all the jobs on the data tab, some now with the comments and statuses, will be copied onto the correct tab. Users will see new a new report but some entries will retain their comments and statuses from last week.
