i'll try keep short, , use actual data/code have better feel i'm trying accomplish.
what i'm trying do: take large dataset , divide out several workers (their daily tasks). involves creating tabs(done), having manager input amount of rows each user (only 1 time variable inside macro i'm doing now) , having excel keep track of number on don't have manually hardcode cells copy/paste.
old version:
sheets("propfiltered").select range("a1:j1250").select 'notice hard code cell range. want done automatically. selection.specialcells(xlcelltypevisible).select selection.copy sheets("april").select 'do not want hard code named sheets. grab employee1 , on variables. range("a1").select activesheet.paste call wraptext sheets("autofiltered").select range("a1:j1250").select selection.specialcells(xlcelltypevisible).select selection.copy sheets("christie e.").select range("a1").select activesheet.paste call wraptext sheets("autofiltered").select range("a1251:j2251").select selection.specialcells(xlcelltypevisible).select selection.copy sheets("cori").select range("a1").select activesheet.paste call wraptext sheets("propfiltered").select range("a1251:j1501").select selection.specialcells(xlcelltypevisible).select selection.copy sheets("cori").select range("a1002").select 'when user has both auto , property, has account first "paste" , done automatically. activesheet.paste call wraptext new version (wip):
sub filltabs(tabname string, numberofautoclaims integer, numberofpropertyclaims integer) 'does make sense use separate sub if have keep track of changing variable (the "what cell should copy because i've done first 1250" variable) dim whatnumberauto integer dim whatnumberprop integer dim whatnumberautoadjusted integer dim settingtheacellauto string dim settingthejcellauto string dim settingtheacellprop string dim settingthejcellprop string dim settingthepastecellauto string dim settingthepastecellprop dim employee1 string dim employee2 string dim employee3 string dim employee4 string employee1 = "april h.,0,1000" 'i have on 20 employees add in 3 variables each, best method? need use each variable (separated comma) employee2 = "christie e.,500,0" employee3 = "cori m.,1000,250" employee4 = "cody s.,1000,250" split(employee1,",") 'this example of splitting employee1 variable make 3 pieces of data useable. there better method? whatnumberauto = 1 'this keep track of "what number should copy now" possible have change if run sub separately each employee? whatnumberprop = 1 settingtheacellauto = "a" & whatnumberauto settingthejcellauto = "j" & numberofautoclaims settingtheacellprop = "a" & whatnumberprop settingthejcellprop = "j" & numberofpropertyclaims 'sheets(tabname).select if numberofautoclaims > 0 whatnumberautoadjusted = whatnumberauto + numberofautoclaims 'settingthepastecellauto = "a" & (numberofautoclaims + 1) sheets("autofiltered").select range("settingtheacellauto:settingthejcellauto").select selection.specialcells(xlcelltypevisible).select selection.copy sheets(tabname).select range("a1").select activesheet.paste call wraptext else if numberofautoclaims = 0 , numberofpropertyclaims = 0 debug.print "this user has no auto or property claims assigned" end if if numberofpropertyclaims = 0 end sub settingthepastecellprop = "a" & (numberofautoclaims + 1) sheets("propfiltered").select range("settingtheacellprop:settingthejcellprop").select selection.specialcells(xlcelltypevisible).select selection.copy sheets(tabname).select range(settingthepastecellprop).select activesheet.paste call wraptext end if newest version:
howmanytabsdoyouneed = 21 'if want add or remove tabs, must change number , add/subtract "tabname(1)" section below. redim tabname(1 howmanytabsdoyouneed) string tabname(1) = "heather,0,0" tabname(2) = "paul,0,0" tabname(3) = "cics,0,0" tabname(4) = "flpip,0,0" tabname(5) = "april h.,0,1000" tabname(6) = "christopher h.,0,1000" ......skipping bits of code 'begin populating employee's sheets. dim autoacount integer dim propacount integer dim autoapastecount integer dim propapastecount integer dim autojcount integer dim propjcount integer dim autorangea range dim autorangej range dim proprangea range dim proprangej range dim propapastecountrange range autoacount = 1 propacount = 1 autojcount = 1 propjcount = 1 propapastecount = 1 = 1 howmanytabsdoyouneed splittabname = split(tabname(i), ",") if splittabname(1) <> "0" set autorangea = range("a" & autoacount) set autorangej = range("j" & splittabname(1)) sheets("autofiltered").select range(autorangea & ":" & autorangej).select selection.copy sheets("splittabname(0)").select activesheet.paste autoacount = autoacount + splittabname(1) propapastecount = splittabname(1) end if if splittabname(2) <> "0" set proprangea = range("a" & propacount) 'msgbox proprangea set proprangej = range("j" & splittabname(2)) set propapastecountrange = range("a" & propapastecount) ' sheets("propfiltered").range("proprangea:proprangej") ' .copy sheets("propfiltered").select range(proprangea & ":" & proprangej).select selection.copy sheets("splittabname(0)").select range(propapastecountrange).select activesheet.paste propacount = propacount + splittabname(2) end if next
