excel - VBA Select last empty cell in Month column using today's date and paste data -

i'm trying use vba script trigger when checkbox checked copies data 1 particular cell , pastes in last empty cell of month column using today's date. here code far, , i've tested check box triggering copy , paste function. can't figure out finding correct column using today's date , selecting next empty cell in column. columns labeled on second sheet using long month names (text data).

sub checkboxupdated() dim mnth string dim fndrng dim cb checkbox   mnth = monthname(month(date)) sheet2 'has 'with' work correctly     set fndrng = cells.find(what:=mnth, after:=a1, _     lookat:=xlpart, searchorder:=xlbyrows, searchdirection:=xlnext, _     matchcase:=true) end      on error resume next     set cb = activesheet.drawingobjects(application.caller)     on error goto 0      if not cb nothing         if cb.value = 1             sheets("sheet1").range(cb.linkedcell).offset(0, -4).copy             sheets("sheet2").activate             fndrng.offset(4, 0).select             activesheet.paste         end if     end if  end sub 

any appreciated, thanks!!!!

two things noticed immediately.

1. within first with...end statement, set fndrng = cells.find ... missing prefix period assigns worksheet parent statement. should set fndrng = .cells.find...

2. the close of with sheet2 extended down encompass more of code, releasing dependence on things activesheet , select.

consider rewrite.

sub checkboxupdated()     dim mnth string, fndrng range, cb checkbox      on error resume next      mnth = monthname(month(date))      sheet2 'has 'with' work correctly         set fndrng = cells.find(what:=mnth, after:=a1, lookat:=xlpart, _                                 searchorder:=xlbyrows, searchdirection:=xlnext, _                                 matchcase:=true)          set cb = .drawingobjects(application.caller)         on error goto 0          if not cb nothing             if cb.value = 1                 sheets("sheet1").range(cb.linkedcell).offset(0, -4).copy _                     destination:=fndrng.offset(4, 0)             end if         end if      end  end sub 

i changed method of copy & paste more direct method in keeping expansion of with/end statement.

see how avoid using select in excel vba macros more methods on getting away relying on select , activate accomplish goals.