If status shows open, Data to be copied over to new sheet

[michael35]

Hi

I need some help on the attached file to copy over 7 columns which are highlighted in green to sheet2 if the status in column P in sheet1 is "open". The items that are copied over needs to be sorted from earliest date based on column B date/time created. If the record in sheet1 has changed from open to any other status, the item in sheet2 needs to be removed. I have placed some samples in sheet2 for expected results

[JLGWhiz]

Your sample does not match your narrative. Which do you want to go by? There are more than seven columns highlighted in green. The column you want to sort on is not included in the Sheet 2 sample. Please clarify.

[michael35]

Hi JLGWhiz,

I've indicated the columns that are to be copied over below. Items that are listed as status open in Sheet1, column P will have the below data copied over. For sorting it will use sheet2, column B from earliest date/Time created. Apologies for not putting it in detail.

Sheet1, Column B will be copied over to Sheet2, Column D
Sheet1, Column C will be copied over to Sheet2, Column A
Sheet1, Column D will be copied over to Sheet2, Column B
Sheet1, Column O will be copied over to Sheet2, Column C
Sheet1, Column G will be copied over to Sheet2, Column E
Sheet1, Column R will be copied over to Sheet2, Column F
Sheet1, Column S will be copied over to Sheet2, Column G

[JLGWhiz]

To eliminate any items which might have had the status change since the last update, the macro starts by clearing all the old data. That way, only currently open items will appear on the sheet 2 data. I will leave the sorting to you since your format appears to containg merged cells.

Please Login or Register  to view this content.

[JLGWhiz]

If post #4 satisfied your issue, you should mark the thread as "Solved" using the 'Thread Tools' button' near the top of the window.

[michael35]

Thank you very much.

To learn about the code, Can i also check if the section here is reflecting the condition that if status is shown as "Open" the data gets copied over over. How can i amend the condition to be instead of "open", it will if there are any text entered in this same column P, data gets copied.

Please Login or Register  to view this content.

[JLGWhiz]

Change to:

Please Login or Register  to view this content.

It will filter on any value in column 16.

When copying filtered data with VBA, the 'SpecialCells(xlCellTypeVisible)' method needs to be used so the rows hidden by the filter will not be copied also. The code in post #4 used several copy actions because the ranges being copied were erratic between the copy and paste positions and could not be copied as a group. The Offset method was used to position the pasted values so they would maintain the integrity of their original relationship on each copied row.

[michael35]

Got it. Thank you for assisting me to understand how it works.

[JLGWhiz]

Got it. Thank you for assisting me to understand how it works.

You're welcome,
Regards, JLG