VBA Code to pull data from one sheet to another in excel

[SSF1590]

Hello,

I am looking for a code to pull data (a whole row) from sheet 4 to sheet 1 based on a criteria. Sheet one should only show the entries that show status as open. This status is in the last column M of sheet 4. Data starts from A20 and should look for the open entries in the sheet 4 where there are also entries with closed status. The list in sheet 1 should update if possible every time the workbook is open (if not maybe with button) as sheet 4 is continuously changing when adding new entries and closing open ones. Is it possible to do this with a VBA code?

Thank you very much in advance.

 

[MarcM2]

I think that's possible.

The trick to make it run is knowing where to write the sub. You need to write it to the worksheet, not a module. In VBA, double click on the worksheet on the left. Then use the dropdown menus above the code editor to change to "Worksheet" and "Change".

You'll see your sub heading change, and this code will execute whenever someone changes a value on the sheet, adds or delete rows, etc. Note that the code will not run if you make a superficial change to the sheet like change border styles or cell colors.

How you copy and paste the row will depend on what you want to do with the date on Sheet1. Also, what do you want to do when the status changes to "Closed." You'll need to be careful to no loose any data, because you'll have it in two different locations.

 

[SSF1590]

Thank you very much for you reply o this. I would like to remove what is closed from sheet 1. As the main data will be stored in sheet 4. But I have no idea how to pull the open ones to sheet 1. Even if it is using a button to refresh the data will be helpful. But I do not know how to develop the code to look specifically for the open ones in sheet 4.