excel - How to add conditions to my recorded macro? -

i have recorded macro below, used auto-transfer data enter 1 worksheet worksheet when run macro. @ present, have record macro each new worksheet in workbook , run each separate macro depending upon sheet need new data transferred to. know how add conditions macro automatically decide worksheet send new data to, based on text in column g of original worksheet. there 4 different text phrases might contained in column g. appreciated!

sub over_90_days() ' ' over_90_days macro ' transfer info terms on 90 days sheet '  '     activecell.offset(0, -7).columns("a:a").entirecolumn.select     selection.end(xldown).select end sub   sub update() ' ' update macro ' move new data different worksheet '  '     range("table1[[#headers],[name, last]]").select     selection.end(xldown).select     range("a74:b74").select     selection.copy     sheets("90 days or less").select     range("a2").select     selection.end(xldown).select     activecell.offset(1, 0).range("a1").select     activesheet.paste     sheets("all terms").select     activecell.offset(0, 2).range("a1").select     application.cutcopymode = false     selection.copy     sheets("90 days or less").select     activecell.offset(0, 2).range("a1").select     activesheet.paste     sheets("all terms").select     activecell.offset(0, 1).range("a1").select     application.cutcopymode = false     selection.copy     sheets("90 days or less").select     activecell.offset(0, 1).range("a1").select     activesheet.paste     sheets("all terms").select     activecell.offset(0, 2).range("a1").select     application.cutcopymode = false     selection.copy     sheets("90 days or less").select     activecell.offset(0, 1).range("a1").select     activesheet.paste     activewindow.smallscroll down:=3     activesheet.paste     application.cutcopymode = false     activecell.offset(1, 0).rows("1:1").entirerow.select     selection.insert shift:=xldown, copyorigin:=xlformatfromleftorabove end sub 

so here's second less glorious attempt:

sub update()  set mainsheet = thisworkbook.worksheets("all terms") set sheet1 = thisworkbook.worksheets("term pre ojt") set sheet2 = thisworkbook.worksheets("term post ojt") set sheet3 = thisworkbook.worksheets("90 days or less") set sheet4 = thisworkbook.worksheets("over 90 days") dim rnge2 range set rnge2 = "a2"  dim integer = 1 50 myvalue = mainsheet.cells(i, 7).value  if myvalue = "orientation only"  activecell.offset(0, -6).select range(activecell, activecell.offset(0, 4)).copy sheet1.select rnge2.select activecell.offset(1, 0).select activesheet.paste activecell.offset(1, 0).select       'move down 1 row  elseif myvalue = "ojt only"  activecell.offset(0, -6).select range(activecell, activecell.offset(0, 4)).copy sheet2.select rnge2.select activecell.offset(1, 0).select activesheet.paste activecell.offset(1, 0).select  elseif myvalue = "90 days or less"  activecell.offset(0, -6).select range(activecell, activecell.offset(0, 4)).copy sheet3.select rnge2.select activecell.offset(1, 0).select activesheet.paste activecell.offset(1, 0).select  elseif myvalue = "over 90 days"  activecell.offset(0, -6).select range(activecell, activecell.offset(0, 4)).copy sheet1.select rnge2.select activecell.offset(1, 0).select activesheet.paste activecell.offset(1, 0).select  end if next  end sub 

it doesn't work, , have no idea why.

you can select sheets this:

dim myworksheet worksheet set myworksheet = thisworkbook.worksheets("the name of sheet want") 

so, can set myworksheet sheet want in code using name of sheet:

dim myworksheet1 worksheet set myworksheet1 = thisworkbook.worksheets("the name sheet")  dim myworksheet2 worksheet set myworksheet2 = thisworkbook.worksheets("the name of second sheet") 

now, can cell in sheet like:

dim mycell range, mycell2 range, mycell3 range set mycell = myworksheet1.range("g1") // gets g1 set mycell2 = myworksheet1.range("g:g") // gets entire g column set mycell3 = myworksheet1.cells(2,2) // gets b2 

you can see value in cell with:

mycell.value 

and can set value of cell:

mycell.value = "the value want" //a text mycell.value = 2 //a number mycell.value = mycell2.value //the value of cell 

with this, can play around , create own code, selecting cells desire , transfering sheets desire.

if not used programming, search tutorials on loops, if statements, select case statements vba.

all selection appear in recorded macro can replaced range ones showed above, unless want selected cell, use selection recorded.

the copy , paste commands work worksheets shown above too. it's better transfer values via code range.value (this avoids copying format , forumlas, , quicker).

---

an example:

column g in mainsheet contains 3 rows, i'll copy them sheet1 or sheet2 depending on value in cell:

dim mainsheet worksheet, sheet1 worksheet, sheet2 worksheet set mainsheet = thisworkbook.worksheets("mainsheet") set sheet1 = thisworkbook.worksheets("the name of sheet1") set sheet2 = thisworkbook.worksheets("the name of sheet2")      dim integer = 1 3    'this loop run 3 times 1 3     dim myvalue variant     myvalue = mainsheet.cells(i,7).value    'i row / 7 g      'check value of cell in row "i" , column g     if myvalue = "the value want go sheet 1"         sheet1.cells(i,7).value = myvalue    'copy sheet 1 same row , column, can change     else                                   'if value not 1 expected         sheet2.cells(i,7).value = myvalue    'copy sheet 2....     end if                                  'this ends if part of code next 'this ends loop part of code 

---

looking @ second attempt:

in case, believe set rnge2 = "a2" first problem. range object must retrieve sheet: set rnge2 = mainsheet.range("a2")

in code, avoid methods use interface, copy/paste , active cell, these slow , brings more concerns care about.

so i'd suggest following:

'this forces me declare vars. it's personal preference 'this way don't have var unidentified type option explicit  sub update()      'declare every sheet because used option explicit     'option explicit obliges declare everything, prefer way avoid having unknown vars in code     dim mainsheet worksheet, sheet1 worksheet, sheet2 worksheet, sheet3 worksheet, sheet4 worksheet     set mainsheet = thisworkbook.worksheets("all terms")     set sheet1 = thisworkbook.worksheets("term pre ojt")     set sheet2 = thisworkbook.worksheets("term post ojt")     set sheet3 = thisworkbook.worksheets("90 days or less")     set sheet4 = thisworkbook.worksheets("over 90 days")      dim integer     dim r1 integer, r2 integer, r3 integer, r4 integer     dim currentcell range     dim myvalue string      'keeping 1 row per sheet in order move down separately     r1 = 2  'start row in sheet1     r2 = 2 'start row in sheet2     r3 = 2 'start row in sheet3     r4 = 2 'start row in sheet4      = 1 26          'using current cell in mainsheet, move down @ end of loop         set currentcell = mainsheet.cells(i, 7)          'taking value current cell         myvalue = lcase(currentcell.value)          if myvalue = "orientation only"              'created sub avoids repeating code 4 times             'copies row main sheet row r1 in sheet1             call copyto(mainsheet, i, sheet1, r1)             r1 = r1 + 1 'move down 1 row'          elseif myvalue = "ojt only"              call copyto(mainsheet, i, sheet2, r2)             r2 = r2 + 1 'move down 1 row'          elseif myvalue = "90 days or less"              call copyto(mainsheet, i, sheet3, r3)             r3 = r3 + 1 'move down 1 row'          elseif myvalue = "over 90 days"              call copyto(mainsheet, i, sheet4, r4)             r4 = r4 + 1 'move down 1 row'          else             msgbox "cell g" & trim(str(i)) & " has invalid value"         end if          'move down current cell in main sheet         set currentcell = currentcell.offset(1, 0)     next  end sub  sub copyto(mainsheet worksheet, mainrow integer, targetsheet worksheet, targetrow integer)      dim linevalue variant      'take values cell "ai" "ei" in mainsheet     linevalue = mainsheet.range(mainsheet.cells(mainrow, 1), mainsheet.cells(mainrow, 5)).value      'puts value in same size range in target sheet     targetsheet.range(targetsheet.cells(targetrow, 1), targetsheet.cells(targetrow, 5)).value = linevalue  end sub