Send data automatically to another sheet in Excel with code word

Copper Contributor

Hi all,

I want to link cells to different worksheets.  I want to be able to type a row in one sheet, with many different columns; for example

purchase date      cost     item name     expected arrival date

 

Then I want the worksheet to automatically look at the expected arrival date and send the entire row to another sheet in the workbook.  For instance, I want to send all rows with expected May arrival dates to a May worksheet.  If the Expected arrival date gets updated in the 1st worksheet to June, I want it to automatically move out of the May worksheet into the June worksheet.

 

Anyone have any suggestions?

1 Reply

You will only be able to do this with macros and they will depend on your current skillset, how complicated you want to make it and how logical your thinking.  

 

Initially...

Create your spreadsheet with your main (Master) sheet.

Create additional sheets for each of the 12 months of the year, with the same headings from the Master sheet.

 

Create macros to do the following...

  1. Check each monthly sheet in turn and delete all existing records 
  2. Select Main sheet and copy the data
  3. Switch to each sheet in turn and paste ALL the data
  4. Switch back to Jan sheet, show all records excluding "Jan" data and remove them
  5. Switch to Feb sheet, show all records excluding "Feb" data and remove them
  6. repeat for the other months.

 

This assumes that ALL records remain on the master sheet.  If that's not the case, then your macros will need to account for this.

 

Equations won't work if you want to automatically move entries if/when the Expected arrival date gets updated in the 1st worksheet e.g. to June, and you want it to automatically move out of the May worksheet into the June worksheet.