excel - Copy selected cells from one workbook and copy to another -


i trying create database copy selected range of data main workbook , copy separate workbook.

the code causing issue below. 2nd workbook opens based on value of "w2". new row should inserted new wb , formatted value of selected cells pasted.

'select data copied activecell.resize(1, 4).copy  'open lessons learned db location = range("w2").value set lessons = workbooks.open(location) set ll = sheets("lessons learned") windows("lessons learned database.xlsm").activate sheets("lessons learned").activate  'insert new row range("5:5").activate activecell.offset(1).entirerow.insert 'enter odd or value     range("a7").select     oe = activecell.value     if oe = 1         range("a6").select         activecell.formular1c1 = 0     else         range("a6").select         activecell.formular1c1 = 1     end if  'hide permanently hidden rows -line below gives error 1004     rows("5:5").select     selection.entirerow.hidden = true     columns("a").select     selection.entirecolumn.hidden = true 'format row     range("a6").select     sc = activecell.value         if sc = 1     range("b6:n6").select     selection.interior         .colorindex = 15         .pattern = xlsolid         .patterncolorindex = xlautomatic     end  end if range("b5").select     selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _         :=false, transpose:=false 

any pointer on i'm going wrong appreciated.

its advised not use .select or .activate there other ways accomplish this.
because selecting , activating have caused error 1004.

below have "cleaned up" code defining lessons, ll , location , included mainwb , defined ranges.

by defining range excel .value range there no need use .select or .activate.

as far tested below code works:

sub copymainwbtonewwb()  dim lessons workbook dim ll worksheet dim mainwb workbook dim location string  set mainwb = workbooks("name here")  'open lessons learned db location = mainwb.sheets("sheet name").range("w2").value set lessons = workbooks.open(location) set ll = lessons.sheets("lessons learned")  'insert new row ll.rows(5).offset(1).entirerow.insert shift:=xldown  'enter odd or value if ll.range("a7").value = 1     ll.range("a6").value = 0 else     ll.range("a6").value = 1 end if  'hide permanently hidden rows -line below gives error 1004 ll.rows(5).hidden = true ll.columns(1).hidden = true  'format row  if ll.range("a6").value = 1     ll.range("b6:n6").interior         .colorindex = 15         .pattern = xlsolid         .patterncolorindex = xlautomatic     end end if mainwb.sheets("sheet1").range("a1:a4").copy ll.range("b5").pastespecial paste:=xlpastevalues, operation:=xlnone, _ skipblanks:=false, transpose:=false end sub 

all need change workbook name of mainwb , sheet name collecting valuefor location.


Comments

Popular posts from this blog

java - UnknownEntityTypeException: Unable to locate persister (Hibernate 5.0) -

python - ValueError: empty vocabulary; perhaps the documents only contain stop words -

ubuntu - collect2: fatal error: ld terminated with signal 9 [Killed] -