Excel VBA - Delete Data from a Worksheet If Selection from Dropdown List is Changed -

follow question answered question: excel vba - run macro based on range of dropdown lists.

current: personal expense spreadsheet , using column g on master worksheet classify line item expenses imported .csv provided credit union. each cell in column g has dropdown list name of other worksheets in workbook: power, gas, groceries, etc. currently, when make selection column g dropdown list, copies a1:f1 of current row , pastes next empty row of whatever worksheet selected, e.g. power or gas or groceries. of working fine.

problem: however, if re-classify line expense, e.g. original selection gas , change power again copy a1:f1 of current row , move power worksheet. great need remove line copied our gas tab.

possible solution?: way can think of adding this... if dropdown not blank , change original selection need find exact text copy of a1:f1 (a1: date, b1: no., c1: description, d1: debit, e1: credit, f1: notes - these ("should") never duplicate) original selection worksheet (gas) , delete cells , move below rows. i'm asking for please write above scenario in code , show me in current code (i understand vba @ novice level - @ best).

here current code runs once dropdown value changed:

private sub worksheet_change(byval target range) dim rng range, c range set rng = intersect(target, range("g2:g1001")) if not rng nothing     each c in rng.cells         select case c.value             case "power": power c             case "gas": gas c             case "water": water c             case "groceries, etc.": groceriesetc c             case "eating out": eatingout c             case "amazon": amazon c             case "home": home c             case "entertainment": entertainment c             case "auto": auto c             case "medical": medical c             case "dental": dental c             case "income": income c             case "other": other c         end select     next c end if end sub 

here case macro fired off above code (there similar macro each case):

sub gas(c range)  dim rng range  set rng = c.entirerow.range("a1:f1") '<< a1:f1 here *relative c.entirerow*  'copy values worksheets("gas").cells(rows.count, 1).end(xlup)     .offset(1, 0).resize(1, rng.cells.count).value = rng.value end  end sub 

any suggestions?

try this. need tweak bit, should going. have added global variable can store previous value dropdown list.
in selectionchange have tried create error handling take care of multiple cells selected. if 1 cell selected value bound global variable. can use variable find sheet of previous value in dropdown list, loop through sheet, , delete value.

first have added gas, power, etc. subs. make them dynamic.

sub power(c range)      dim rng range      set rng = nothing     set rng = range("a" & c.row & ":f" & c.row) '<< a1:f1 here *relative c.entirerow*      'copy values     worksheets("power").cells(rows.count, 1).end(xlup)         .offset(1, 0).resize(1, rng.cells.count).value = rng.value          ' copy formating master sheet         worksheets("master")             range("a" & c.row & ":f" & c.row).copy         end         .offset(1, 0).pastespecial xlpasteformats         application.cutcopymode = false      end  end sub 

under master sheet (not module), have added this:

' add absolute top of sheet, must outside procedure (sub) option explicit public cbxoldval string dim prevval variant  private sub worksheet_selectionchange(byval target range) if target.rows.count > 1 exit sub if target.columns.count > 1 exit sub  cbxoldval = target.value end sub  private sub worksheet_activate()     if selection.rows.count = 1 , selection.columns.count = 1         prevval = selection.value     else         prevval = selection     end if end sub 

add worksheet_change event.

private sub worksheet_change(byval target range) dim rng range, c range set rng = intersect(target, range("g2:g1001"))  if not intersect(target, columns("g")) nothing     if prevval <> "" or cbxoldval <> ""         if cbxoldval = target.value             msgbox "you have click on cell " & vbnewline & "and click on " & target.address & " change value", vbexclamation, "error"             cells(target.row, target.column) = prevval             exit sub         elseif target.value = "" or target.value = prevval exit sub         end if     end if end if  if not rng nothing ' loop 

then have added code worksheet_change event. add after end select.

    if cbxoldval = ""     ' nothing      else          worksheets(cbxoldval)              dim integer             dim strfinda string, strfindb string, strfindc string             dim strfindd string, strfinde string, strfindf string             strfinda = sheets("master").range("a" & c.row)             strfindb = sheets("master").range("b" & c.row)             strfindc = sheets("master").range("c" & c.row)             strfindd = sheets("master").range("d" & c.row)             strfinde = sheets("master").range("e" & c.row)             strfindf = sheets("master").range("f" & c.row)              = 1 100    ' replace lastrow              if .cells(i, 1).value = strfinda _             , .cells(i, 2).value = strfindb _             , .cells(i, 3).value = strfindc _             , .cells(i, 4).value = strfindd _             , .cells(i, 5).value = strfinde _             , .cells(i, 6).value = strfindf _                          .rows(i).entirerow.delete             msgbox "deleted row " &             goto skip:              end if              next           end     end if skip: