0


I have a workbook that will be making a new sheet daily and deleting an old one. I would like to cut the "Worksheet_Change" sub and move it to the new sheet? How can I move this code programmatically so that it is not a manual process. Also is there a way to programmatically make the "Worksheet_Change" function activate once with VBA vs. having to click in a cell?

Thanks in Advance,
Midas

Midas
  • 13
  • 1
  • 5
  • Create a copy of the worksheet, which will preserve the code in its module. For firing the Change event handler, you need to provide more detail like under what circumstance should it trigger? – David Zemens Aug 02 '17 at 15:10
  • @David Zemens It is an worksheet change event. It looks for when a column in the worksheet changes and then fires the code. Is there a way to copy the code to a different sheet vs having to always keep the same sheet? The sheet I am wanting to move it too will have different information on it. – Midas Aug 02 '17 at 15:12
  • https://stackoverflow.com/questions/24701517/copy-vba-code-from-one-worksheet-to-another-using-vba-code this shows you how to copy code from one sheet to another. It's still not clear when you want to fire the Change event if there is not actually a Change event that occurs. – David Zemens Aug 02 '17 at 15:13
  • because I want for it to be a change event after the first time. I just want for it to have worked once beforehand. Is there a way to cut instead of copy? – Midas Aug 02 '17 at 15:58
  • After *what* "first time"? How do you want the "first time" to happen? You can use the VBE Extensibility library also to remove code from modules. There is no direct "cut" method, but you can remove modules or lines within modules. I would suggest making a go at it, and asking a new question if you have specific implementation problems or errors. – David Zemens Aug 02 '17 at 15:59

1 Answers1

1

You could create a template worksheet withn your VBA code consisting your Worksheet_Change functions. Make it hidden. Then every day you may save it as a new one and delete the old one.

helpME1986
  • 933
  • 3
  • 12
  • 26
  • Is there a way to just copy the backend code to a different worksheet? The sheet I add in everyday is not blank. It always has new information on it. It would be nice to be able to just add the back end code. – Midas Aug 02 '17 at 15:08
  • 1
    https://stackoverflow.com/questions/18497527/copy-vba-code-from-a-sheet-in-one-workbook-to-another – helpME1986 Aug 02 '17 at 15:10