vba - Run-time error '1004': Method 'Range' of object '_worksheet' failed while merging - 


option explicit  public sub compile_workbook_data()  dim master_wkbk workbook: set master_wkbk = thisworkbook dim master_sht worksheet: set master_sht = thisworkbook.worksheets("task tracking-internal & org.") dim current_wkbk workbook dim current_sht worksheet dim wkbk_list(1 3) string dim x integer dim last_row integer dim last_col integer  wkbk_list(1) = "sub project_workbook - core services.xlsm" wkbk_list(2) = "sub project_workbook - esp2.0.xlsm" wkbk_list(3) = "sub project_workbook - p2e.xlsm"  x = 1 ubound(wkbk_list)      set current_wkbk = workbooks.open("d:\delta dental\" & wkbk_list(x))                    set current_sht = current_wkbk.worksheets("task tracking-internal & org.")      last_row = current_sht.cells.find("*", searchorder:=xlbyrows, searchdirection:=xlprevious).row     last_col = current_sht.cells.find("*", searchorder:=xlbycolumns, searchdirection:=xlprevious).column      current_sht.range(cells(4, 1), cells(last_row, last_col)).copy      last_row = master_sht.cells.find("*", searchorder:=xlbyrows, searchdirection:=xlprevious).row     master_sht.range("a" & last_row + 1).pastespecial paste:=xlpastevalues      current_wkbk.close false next x  end sub

im getting following error while running merge code:

run-time error '1004': method 'range' of object '_worksheet' failed

the following code code. yet, made bit more verbose. might allow tell error is:

option explicit  public sub compile_workbook_data()  dim master_wkbk workbook dim master_sht worksheet dim current_wkbk workbook dim current_sht worksheet dim wkbk_list(1 3) string dim x integer dim last_row integer dim last_col integer  dim bolfound boolean dim strfilepath string dim strsheetname string dim fso new filesystemobject  set master_wkbk = thisworkbook strsheetname = "task tracking-internal & org." strfilepath = "e:\delta dental\"  bolfound = false each master_sht in master_wkbk.worksheets     if master_sht.name = strsheetname bolfound = true: exit next master_sht if bolfound = false msgbox "couldn't find required sheet in excel file." & chr(10) & "aborting...": exit sub  wkbk_list(1) = "sub project_workbook - core services.xlsm" wkbk_list(2) = "sub project_workbook - esp2.0.xlsm" wkbk_list(3) = "sub project_workbook - p2e.xlsm"  if not fso.driveexists(split(strfilepath, "\")(0)) msgbox "couldn't find required drive" & split(strfilepath, "\")(0) & "." & chr(10) & "aborting...": exit sub if not fso.folderexists(strfilepath) msgbox "couldn't find required folder." & chr(10) & "aborting...": exit sub  x = 1 ubound(wkbk_list)      if dir(strfilepath & wkbk_list(x)) = vbnullstring msgbox "file " & chr(10) & "   " & strfilepath & wkbk_list(x) & chr(10) & "not found." & chr(10) & "aborting...": exit sub      set current_wkbk = workbooks.open("d:\delta dental\" & wkbk_list(x))      bolfound = false     each current_sht in current_wkbk.worksheets         if current_sht.name = strsheetname bolfound = true: exit     next current_sht     if bolfound = false msgbox "couldn't find required sheet in file:" & chr(10) & "   " & strfilepath & wkbk_list(x) & chr(10) & "aborting...": exit sub      last_row = current_sht.cells.find("*", searchorder:=xlbyrows, searchdirection:=xlprevious).row     last_col = current_sht.cells.find("*", searchorder:=xlbycolumns, searchdirection:=xlprevious).column      current_sht.range(cells(4, 1), cells(last_row, last_col)).copy      last_row = master_sht.cells.find("*", searchorder:=xlbyrows, searchdirection:=xlprevious).row     master_sht.range("a" & last_row + 1).pastespecial paste:=xlpastevalues      current_wkbk.close false next x  end sub

note, above code requires reference microsoft scripting runtime. please make sure set before running code.

enter image description here


Comments

Post a Comment

Popular posts from this blog

4x4 Matrix in Python -

im trying code 4x4 matrix in python random integers 1-4. thats easy enough problem want each row , each column 1 time uses of each digit 1-4 example 1 2 3 4 2 3 4 1 3 4 1 2 4 1 2 3 my code 33% of time in loop there happens somthing this 2 1 4 3 3 4 2 1 1 3 x <-------- because of programm cant contiune , end in infinity loop helb how can out? code below """ programm playing game skyline """ random import randrange row1 = [] row2 = [] row3 = [] row4 = [] allrows = [row1, row2, row3, row4] column1 = [] column2 = [] column3 = [] column4 = [] allcolumns = [column1, column2, column3, column4] def board(): in range(4): j = 0 while len(allrows[i]) != 4: x = randrange(1,5) print(i, j) if x not in allrows[i] , x not in allcolumns[j]: allrows[i].append(x) allcolumns[j].append(x) j += 1 else: continue boa...
Read more

wso2is - WSO2 IS 5.0.0 SP1 After restart there is authentication error -

i have configured service provider sso saml2 web sso configuration. the single sign on & logout working fine, when logout , leave browser on login page of wso2 is, , restart wso2 is, when type username/password login in page left open in wso2 is, following error occurs: tid: [0] [is] [2017-03-28 08:03:29,462] debug {org.wso2.carbon.identity.application.authentication.framework.util.frameworkutils} - authentication context null {org.wso2.carbon.identity.application.authentication.framework.util.frameworkutils} tid: [0] [is] [2017-03-28 08:03:29,462] debug {org.wso2.carbon.identity.application.authentication.framework.handler.request.impl.defaultrequestcoordinator} - session data key : cbd70356-49e3-423d-8542-3d4a0c7ad2c6 {org.wso2.carbon.identity.application.authentication.framework.handler.request.impl.defaultrequestcoordinator} tid: [0] [is] [2017-03-28 08:03:29,462] error {org.wso2.carbon.identity.application.authentication.framework.handler.request.impl.defaultreques...
Read more

python - PyInstaller UAC not working in onefile mode -

hello everybody. i have small python project , want make single executable file . using... windows 7 python 3.4 pyinstaller 3.2.1 my pyinstaller command is, pyinstaller -y -w -f -n output_file_name --uac-admin --clean source_file.py this command works properly. single output file not ask admin rights when executed. , there's no shield mark on executable file icon. when remove -f option (equivalent --onefile), output executable file has shield mark on icon , ask me admin rights. not want. want single executable file. i found manifest file (output_file_name.exe.manifest) in dist\output_file_name folder. did... pyinstaller -y -w -f -n output_file_name --manifest output_file_name.exe.manifest --uac-admin --clean source_file.py but command doesn't work. single executable file still not ask admin rights. i have removed pyinstaller , installed recent development version. pip install git+https://github.com/pyinstaller/pyinstaller.git@develop but result...
Read more