To create a variable  with longer life, put it outside the subroutine (i.e.
at the top of the module). It will then live as long as the module does -
which for worksheet and normal modules is until you close the workbook.

Public and private  only control the visibility of the item, not its life.