excel vba - RT-1004 when copying data from destination to Source workbooks -


i use code copy data workbook import report. however, month progresses , amount of data grows, amount of time run sub (in last week of january took 3 minutes process 900 rows of data):

sub extract_sort_1602_february()  dim ans long  ans = msgbox("is february 2016 swivel master file checked out of sharepoint , open on desktop?", vbyesno + vbquestion + vbdefaultbutton1, "master file open") if ans = vbno or iswbopen("swivel - master - february 2016") = false     msgbox "the required workbook not open. procedure terminate.", vbokonly + vbexclamation, "terminate procedure"     exit sub end if  application.screenupdating = false      ' line autofits columns c, d, o, , p     range("c:c,d:d,o:o,p:p").columns.autofit      ' unhides hidden rows     cells.entirerow.hidden = false  dim lr long      lr = range("b" & rows.count).end(xlup).row 2 step -1         if range("b" & lr).value <> "2"             rows(lr).entirerow.delete         end if     next lr  application.run "'swivel - master - february 2016.xlsm'!unfilter"  activeworkbook.worksheets("extract").sort     .sortfields         .clear         .add key:=range("b2:b2000"), sorton:=xlsortonvalues, order:=xlascending, dataoption:=xlsortnormal         .add key:=range("d2:d2000"), sorton:=xlsortonvalues, order:=xlascending, dataoption:=xlsortnormal         .add key:=range("o2:o2000"), sorton:=xlsortonvalues, order:=xlascending, dataoption:=xlsortnormal         .add key:=range("j2:j2000"), sorton:=xlsortonvalues, order:=xlascending, dataoption:=xlsortnormal         .add key:=range("k2:k2000"), sorton:=xlsortonvalues, order:=xlascending, dataoption:=xlsortnormal         .add key:=range("l2:l2000"), sorton:=xlsortonvalues, order:=xlascending, dataoption:=xlsortnormal     end     .setrange range("a2:ae2000")     .apply end cells.wraptext = false sheets("extract").range("a2").select      dim lastrow integer, integer, erow integer      lastrow = activesheet.range("a" & rows.count).end(xlup).row     = 2 lastrow         if cells(i, 2) = "2"              ' opposed selecting cells, copy them directly             range(cells(i, 1), cells(i, 31)).copy              ' opposed "activating" workbook, , selecting sheet, paste cells directly             workbooks("swivel - master - february 2016.xlsm").sheets("swivel")                 erow = .cells(.rows.count, 1).end(xlup).offset(1, 0).row                 .cells(erow, 1).pastespecial xlpasteall             end             application.cutcopymode = false         end if     next  application.screenupdating = true end sub

i asked in code review more efficient way achieve intended results , came this:

sub extract_sort_1602_february()  dim ans long  ans = msgbox("is february 2016 swivel master file checked out of sharepoint , open on desktop?", vbyesno + vbquestion + vbdefaultbutton1, "master file open") if ans = vbno or iswbopen("swivel - master - february 2016") = false     msgbox "the required workbook not open. procedure terminate.", vbokonly + vbexclamation, "terminate procedure"     exit sub end if  application.screenupdating = false      ' line autofits columns c, d, o, , p     range("c:c,d:d,o:o,p:p").columns.autofit      ' unhides hidden rows     cells.entirerow.hidden = false  dim lr long      lr = range("b" & rows.count).end(xlup).row 2 step -1         if range("b" & lr).value <> "2"             rows(lr).entirerow.delete         end if     next lr  application.run "'swivel - master - february 2016.xlsm'!unfilter"     sourceworksheet.sort         .sortfields             .clear             .add key:=range("b2:b2000"), sorton:=xlsortonvalues, order:=xlascending, dataoption:=xlsortnormal             .add key:=range("d2:d2000"), sorton:=xlsortonvalues, order:=xlascending, dataoption:=xlsortnormal             .add key:=range("o2:o2000"), sorton:=xlsortonvalues, order:=xlascending, dataoption:=xlsortnormal             .add key:=range("j2:j2000"), sorton:=xlsortonvalues, order:=xlascending, dataoption:=xlsortnormal             .add key:=range("k2:k2000"), sorton:=xlsortonvalues, order:=xlascending, dataoption:=xlsortnormal             .add key:=range("l2:l2000"), sorton:=xlsortonvalues, order:=xlascending, dataoption:=xlsortnormal         end         .setrange range("a2:ae2000")         .apply     end cells.wraptext = false sheets("extract").range("a2").select  dim sourceworkbook workbook set sourceworkbook = workbooks("tempimport.xlsx") dim destinationworkbook workbook set destinationworkbook = workbooks("swivel - master - february 2016.xlsm") dim sourceworksheet worksheet set sourceworksheet = sourceworkbook.sheets("extract") dim destinationworksheet worksheet set destinationworksheet = destinationworkbook.sheets("swivel") dim lastrow integer lastrow = sourceworksheet.range("a" & rows.count).end(xlup).row dim sourcerow integer dim destinationrow integer destinationrow = destinationworksheet.cells(rows.count, 1).end(xlup) + 1   sourcerow = 2 lastrow     if cells(sourcerow, 2) = "2"         destinationworksheet.rows(destinationrow) = sourceworksheet.rows(sourcerow) ' run-time error occurs         destinationrow = destinationrow + 1     end if next sourcerow  call extractsave  application.screenupdating = true end sub

but there a

run-time error '1004': application-defined or object-defined error

for line:

destinationworksheet.rows(destinationrow) = sourceworksheet.rows(sourcerow)

i have included 2 snapshots of source data , target workbook.this source workbook

this target workbook (some columns hidden same source workbook)

this sub used clear filters prior copy/paste.

sub unfilter()  dim variant each in thisworkbook.worksheets     if she.filtermode she.showalldata next  end sub

try code (on temp copy of workbooks):

sub extract_sort_1602_february()  dim ans long dim lr long dim urng range dim worksheet   ans = msgbox("is february 2016 swivel master file checked out of sharepoint , open on desktop?", vbyesno + vbquestion + vbdefaultbutton1, "master file open")  if ans = vbno or iswbopen("swivel - master - february 2016") = false      msgbox "the required workbook not open. procedure terminate.", vbokonly + vbexclamation, "terminate procedure"      exit sub  end if  dim sourceworkbook workbook  set sourceworkbook = workbooks("tempimport.xlsx") dim destinationworkbook workbook  set destinationworkbook = workbooks("swivel - master - february 2016.xlsm") dim sourceworksheet worksheet  set sourceworksheet = sourceworkbook.sheets("extract") dim destinationworksheet worksheet  set destinationworksheet = destinationworkbook.sheets("swivel")   application.screenupdating = false application.enableevents = false application.calculation = xlcalculationmanual      ' line autofits columns c, d, o, , p     sourceworksheet.range("c:c,d:d,o:o,p:p").columns.autofit      ' unhides hidden rows     sourceworksheet.cells.entirerow.hidden = false        lr = sourceworksheet.range("b" & rows.count).end(xlup).row 2 step -1         if sourceworksheet.range("b" & lr).value <> "2"          if urng nothing           set urng = sourceworksheet.rows(lr)          else           set urng = union(urng, sourceworksheet.rows(lr))          end if         end if     next lr      if not urng nothing urng.delete      'application.run "'swivel - master - february 2016.xlsm'!unfilter"     each in destinationworkbook.worksheets         if she.filtermode she.showalldata     next       sourceworksheet.sort         .sortfields             .clear             .add key:=range("b2:b2000"), sorton:=xlsortonvalues, order:=xlascending, dataoption:=xlsortnormal             .add key:=range("d2:d2000"), sorton:=xlsortonvalues, order:=xlascending, dataoption:=xlsortnormal             .add key:=range("o2:o2000"), sorton:=xlsortonvalues, order:=xlascending, dataoption:=xlsortnormal             .add key:=range("j2:j2000"), sorton:=xlsortonvalues, order:=xlascending, dataoption:=xlsortnormal             .add key:=range("k2:k2000"), sorton:=xlsortonvalues, order:=xlascending, dataoption:=xlsortnormal             .add key:=range("l2:l2000"), sorton:=xlsortonvalues, order:=xlascending, dataoption:=xlsortnormal         end         .setrange range("a2:ae2000")         .apply     end      sourceworksheet.cells.wraptext = false      dim lastrow integer     lastrow = sourceworksheet.range("a" & rows.count).end(xlup).row     'dim sourcerow integer     dim destinationrow integer     destinationrow = destinationworksheet.cells(rows.count, 1).end(xlup).row + 1       sourceworksheet.range("a2:aa" & lastrow).copy destinationworksheet.range("a" & destinationrow)      'for sourcerow = 2 lastrow     '    if cells(sourcerow, 2) = "2"     '        destinationworksheet.rows(destinationrow) = sourceworksheet.rows(sourcerow) ' run-time error occurs     '        destinationrow = destinationrow + 1     '    end if     'next sourcerow      call extractsave  application.screenupdating = true application.enableevents = true application.calculation = xlcalculationautomatic  end sub

Comments

Post a Comment

Popular posts from this blog

java - pagination of xlsx file to XSSFworkbook using apache POI -

right in code, reading xlsx file, xssfworkbook, , writing database. but, when size of xlsx file increases, causes outofmemory error. can not increase server size, or divide xlsx file pieces. tried loading workbook using file (instead of inputstream), didn't either. i looking way read 10k rows @ time (instead of entire file @ once) , iteratively write workbook , database. is there way apache poi? poi contains called "eventmodel" designed purpose. it's mentioned in faq : the ss eventmodel package api reading excel files without loading whole spreadsheet memory. require more knowledge on part of user, reduces memory consumption more tenfold. based on awt event model in combination sax. if need read-only access, best way it. however, may want double check first if issue somewhere else. check out this item : i think poi using memory! can do? 1 comes quite lot, reason isn't might think. so, first thing check - what's source of prob...
Read more

Unlimited choices in BASH case statement -

consider simple bash snippet: case $option in 1) image=${options[0]%.tar} ;; 2) image=${options[1]%.tar} ;; 3) image=${options[2]%.tar} ;; 4) image=${options[3]%.tar} ;; *) echo "invalid option" exit 1 esac in real script numbers goes 30. makes pretty long. can somehow specify cases variable? something this: case $option in $i) image=${options[$(($i-1))]%.tar} any pointer appreciated. you can match against multiple patterns in single clause: case $option in 1|2|3: echo "$option one, 2 or three" ;; esac if still typing you, can use simple pattern matching: case $option in # following matches # - single-digit numbers 0-9 # - two-digit numbers starting either 1 or 2 # - number 30 [0-9]|[12][0-9]|30) image=${options[$(($option-1))]%.tar} ;; *) echo "invalid option" 1>&2 exit 1 esac
Read more

apache - How do I stop my index.php being run twice for every user -

we've got webapp initial html served using apache , php. i noticed index.php being run twice every request. seems caused / effected rewrite rules in our .htaccess - rewritecond %{http_host} ^(www\.|hotels\.)ourdomain\.com$ [nc,or] rewritecond %{server_name} ^(www\.|hotels\.)ourdomain\.com$ [nc] rewriterule (.*) http://ourdomain.com/$1 [r=301] rewriterule ^hotels/([^/]+)/?\??(.*)$ ?d=$1&$2 [qsa] the final rule moving parameter url path query string. i.e. http://ourdomain.com/hotels/vegas?someparam=1 becomes http://ourdomain.com?d=vegas&someparam=1 if go directly query string version index.php run once. if use url redirected, index.php run twice (i'm checking adding error_log('end of index.php') file). so example going http://ourdomain.com/hotels/paris hits file twice http://ourdomain.com?d=paris hits once. i've seen this question , had @ blog mentioned , , can't find empty string url's (i've tried using yslow process). ...
Read more