VBA Excel Generate Unique Pages From Template and List -

-

i have been working on sometime (i not experienced in vba @ all), keep getting errors here are.

setup: simplicity purposes have 2 worksheets in workbook. first, "daily order", list of products, each row being different product (approx. 1,000), each column indicating different information product (ie id, cost, weight, etc).
second, "template", pricing template that, when given product information, generate pricing table.

objective: create vba macro loop through each row of "daily order" worksheet , each row make copy of template sheet , append information new sheet.

what doesn't work:

sub generatepricebook()  dim rw range  dim temp worksheet dim ws worksheet dim daily worksheet  set daily = worksheets("daily order") set temp = worksheets("template")  temp.activate  each rw in daily.rows     temp.copy after:=sheets(sheets.count)      set ws = sheets(sheets.count)      ws.name = rw.value      ws         .range("a6").formula = "='daily order'!b" & rw.row         .range("b6").formula = "='daily order'!b" & rw.row         .range("a3").formula = "='daily order'!q" & rw.row         .range("e36").formula = "='daily order'!m" & rw.row         .range("e36").formula = "='daily order'!y" & rw.row         .range("e37").formula = "='daily order'!l" & rw.row     end  next rw  end sub

types of errors:

i have spent considerable time trying isolate out areas think might troublesome, keep ending either 424 errors or 1004 errors.

again, appreciate help. thank you!

i think you're looking for:

sub generatepricebook()      dim wsdaily worksheet     dim wstemp worksheet     dim lvisibility xlsheetvisibility     dim strsheetname string     dim rindex long     dim long      set wsdaily = sheets("daily order")     set wstemp = sheets("template")      lvisibility = wstemp.visible          'in case template sheet hidden     wstemp.visible = xlsheetvisible      rindex = 2 wsdaily.cells(rows.count, "a").end(xlup).row         'ensure valid sheet name         strsheetname = wsdaily.cells(rindex, "a").text         = 1 7             strsheetname = replace(strsheetname, mid(":\/?*[]", i, 1), " ")         next         strsheetname = trim(left(worksheetfunction.trim(strsheetname), 31))          'make sure sheet name doesn't exist         if not evaluate("isref('" & strsheetname & "'!a1)")             wstemp.copy after:=sheets(sheets.count)             sheets(sheets.count)                 .name = strsheetname                 .range("a6").formula = "='" & wsdaily.name & "'!b" & rindex                 .range("b6").formula = "='" & wsdaily.name & "'!b" & rindex     'you referencing same cell in a6?                 .range("a3").formula = "='" & wsdaily.name & "'!q" & rindex                 .range("e36").formula = "='" & wsdaily.name & "'!m" & rindex                 .range("e36").formula = "='" & wsdaily.name & "'!y" & rindex    'you putting second formula in e36?                 .range("e37").formula = "='" & wsdaily.name & "'!l" & rindex             end         end if     next rindex     wstemp.visible = lvisibility  'set template sheet original visible state      set wsdaily = nothing     set wstemp = nothing  end sub

Comments

Post a Comment

Popular posts from this blog

java - Run a .jar on Heroku -

-
i trying run .jar file on heroku, , can't find instructions anywhere on how this. have read wants me compile uploading heroku. possible, , if is, how this? if helps, trying run minecraft craftbukkit server, , don't have pom.xml file yet. you didn't provide details i'll try give , people reaches question way it. first of all, create heroku account , create heroku application (assumed have done that). second, need generate application jar , upload heroku. so, open terminal , install heroku cli (assuming unix terminal): $ sudo npm install -g heroku-cli you need have npm , nodejs installed, can check link: https://www.digitalocean.com/community/tutorials/how-to-install-node-js-on-ubuntu-16-04 once done that, can use npm update node version acceptable heroku-cli: $ sudo npm install -g n $ sudo n 7.10.0 and run: $ sudo npm install -g heroku-cli in order deploy jars, need install deploy plugin: $ sudo heroku plugins:install heroku-cli-deploy ...
Read more

java - Jtable duplicate Rows -

-
Image
when add new rows in jtable duplicate rows many times this method update jtable public final void tableaucomande(){ ((defaulttablemodel)tabcommande.getmodel()).getdatavector().clear(); session s=hibernateutil.getsessionfactory().opensession(); criteria cr1= s.createcriteria(commande.class); list <commande> lcs=cr1.list() ; criteria cr2= s.createcriteria(lignecommande.class); list <lignecommande> lck=cr2.list() ; if(lcs.size()!=0 && lck.size()!=0) { (commande lc:lcs) { for(lignecommande lig:lck){ ((defaulttablemodel)tabcommande.getmodel()).addrow(new object[]{ lc.getid_cmd(),lc.getnumcmd(),lc.getdate_cmd(),lc.getclient().getnomprenom(), lig.getproduit().getlib_prd(),lig.getquantite(),lig.getproduit().getid_prd() }); } } // tabcommande.setrowselectioninterval(tabcommande.getrowcount()-1,tabcommande.getrowcount()-1); } } this code add add data rows displaye...
Read more

validation - How to pass paramaters like unix into windows batch file -

-
i need pass various parameters .cmd file unix format file.cmd -configuration=value -source=value -flag but, try this: startlocal @echo off cls setlocal set cmdline=%* set configuration= set source= set badargs= set validation= goto main :splitargs echo splitargs(%*) if "%*" neq "" ( /f "tokens=1,2,* delims== " %%i in ("%*") call :assignkeyvalue %%i %%j & call :splitargs %%k ) goto :eof :assignkeyvalue echo assignkeyvalue(%1, %2) if /i %1==-configuration ( set configuration=%2 ) else if /i %1==-source ( set source=%2 ) else ( rem append unrecognised [key,value] badargs echo unknown key %1 set badargs=%badargs%[%1, %2] ) goto :eof :validate echo validating set validation=fail if defined configuration ( echo -configuration ok if defined source ( echo -source ok if not defined badargs ( set validation=success ) ) ) goto :eof :main cl...
Read more