Excel VBA - Creating new sheets and pasting stuff in raise 1004 Error -


i have issue excel vba code below. 1004 error when block commented out active.

error result:

  • the first sheet in array created, header row pasted in , formatted correctly
  • second sheet created, no header row pasted
  • error stops script @ point -> no further sheets created

i cant figure out problem is, can help?

dim ws1 worksheet: set ws1 = activeworkbook.sheets("agestock")  dim newsheets(1 4) string newsheets(1) = "chem - also" newsheets(2) = "lbs - lbla" newsheets(3) = "lbs - lbfg" newsheets(4) = "chemicals" dim sheetname variant  'copy header row ws1 ws1.cells(1, 1).entirerow.copy  'create new worksheets each sheetname in newsheets     sheets.add after:=worksheets(worksheets.count)     activesheet.name = sheetname      'paste header row     activesheet.cells(1, 1).select     activesheet.paste      ' autofit columns & zoom & scroll     activewindow.zoom = 90     activesheet.columns("a:y").autofit     activesheet.columns("b").columnwidth = 60  'description     activesheet.columns("e").columnwidth = 12  'was     activesheet.columns("f").columnwidth = 12  'now      'this section breaks     'format was, now, avgcost, value currency   '    activesheet.columns("e").numberformat = "$#,##0.00"  'was '    activesheet.columns("f").numberformat = "$#,##0.00"  'now '    activesheet.columns("h").numberformat = "$#,##0.00"  'avgcost '    activesheet.columns("l").numberformat = "$#,##0.00"  'value  next sheetname ws1.activate

this because excel's copying , pasting vba horrible. i'd suggest using following macro instead. note assign range, , copy directly destination (no messy selection takes place). makes things lot more reliable, because excel handles whole copy operation, not windows. think. makes things work me though.

public sub test()     dim newsheets(1 4) string     newsheets(1) = "chem - also"     newsheets(2) = "lbs - lbla"     newsheets(3) = "lbs - lbfg"     newsheets(4) = "chemicals"     dim sheetname variant     dim ws1 excel.worksheet     dim rngcopytarget excel.range     set ws1 = thisworkbook.sheets(1)      'copy header row ws1      set rngcopytarget = ws1.cells(1, 1).entirerow     'create new worksheets     each sheetname in newsheets         sheets.add after:=worksheets(worksheets.count)         activesheet.name = sheetname          'paste header row         rngcopytarget.copy activesheet.range(rngcopytarget.address)          ' autofit columns & zoom & scroll         activewindow.zoom = 90         activesheet.columns("a:y").autofit         activesheet.columns("b").columnwidth = 60  'description         activesheet.columns("e").columnwidth = 12  'was         activesheet.columns("f").columnwidth = 12  'now          'this section breaks         'format was, now, avgcost, value currency         activesheet.columns("e").numberformat = "$#,##0.00"  'was         activesheet.columns("f").numberformat = "$#,##0.00"  'now         activesheet.columns("h").numberformat = "$#,##0.00"  'avgcost         activesheet.columns("l").numberformat = "$#,##0.00"  'value      next sheetname     ws1.activate end sub

Comments

Post a Comment

Popular posts from this blog

cakephp - simple blog with croogo -

i not problem, made simple blog in croogo, problem is not adding or modifying or delete, because arabic language ?? function add in controller `public function admin_add() { $this->set('title_for_layout', __('add part')); if (!empty($this->request->data)) { $this->part->create(); if ($this->part->save($this->request->data)) { $this->session->setflash(__('the part has been saved'), 'default', array('class' => 'success')); $this->redirect(array('action' => 'index'));//, $this->part->id)); } else { $this->session->setflash(__('the part not saved. please, try again.'), 'default', array('class' => 'error')); } } }` => model `class part extends appmodel { public $name = 'part'; var $belongst...
Read more

How to group boxplot outliers in gnuplot -

i have large set of data points. try plot them boxplot, of outliers exact same value , represented on line beside each other. found how set horizontal distance between outliers in gnuplot boxplot , doesn't much, apparently not possible. is possible group outliers together, print 1 point , print number in brackets beside indicate how many points there are? think make more readable in graph. for information, have 3 boxplots 1 x value , times 6 in 1 graph. using gnuplot 5 , played around pointsize, doesn't reduce distance anymore. hope can help! edit: set terminal pdf set output 'dat.pdf' file0 = 'dat1.dat' file1 = 'dat2.dat' file2 = 'dat3.dat' set pointsize 0.2 set notitle set xlabel 'x' set ylabel 'y' header = system('head -1 '.file0); n = words(header) set xtics ('' 1) set [i=1:n] xtics add (word(header, i) i) set style data boxplot plot file0 using (1-0.25):1:(0.2) boxplot lw 2 lc rgb '#8b0000...
Read more

bash - Performing variable substitution in a string -

i have string contains use of variable, , i'd substitute variable's value string. right best have is #!/bin/bash foo='$name; echo bar' name="the name" expanded="$(eval echo "$foo")" echo "$expanded" which has obvious defects: prints the name bar while i'd print the name; echo bar instead of eval can bash's regex matching bash's string replacement: foo='$name; echo bar' name="the name" [[ "$foo" =~ \$([[:alnum:]]+) ]] && s="${!bash_rematch[1]}" && expanded="${foo/\$${bash_rematch[1]}/$s}" echo "$expanded" name; echo bar
Read more