excel - finding a column header, copying the data, and pasting values in another workbook -

i trying copy range of data 1 worksheet "a" "b" code have copying cells copying data "a" , pasting in "a".... i'm not sure issue is...

    sub findazuredataandcopyit()  dim wbb excel.workbook dim wba excel.workbook dim ed excel.worksheet   set wbb = workbooks("source.xlsx") set wba = workbooks("model.xlsb")   dim col long, lastrow long dim rngm range dim rngsku range dim rngpo range if application.countif(wbb.sheets("b").rows(1), "plan_tamaward*") > 0     col = application.match("plan_tamaward*", wbb.sheets("b").rows(1), 0)     lastrow = wbb.sheets("b").cells.find(what:="*", searchdirection:=xlprevious, searchorder:=xlbyrows).row     set rngm = range(cells(2, col), cells(lastrow, col)) else     msgbox "the column named plan_tamaward* not found in row1.", vbexclamation, "column not found!"     exit sub end if  'set range sku  if application.countif(wbb.sheets("b").rows(1), "plan_sku_*") > 0     col = application.match("plan_sku_*", wbb.sheets("b").rows(1), 0)     lastrow = wbb.sheets("b").cells.find(what:="*", searchdirection:=xlprevious, searchorder:=xlbyrows).row     set rngsku = range(cells(2, col), cells(lastrow, col))  else     msgbox "the column named plan_sku* not found in row1.", vbexclamation, "column not found!"     exit sub end if  ' set range po  if application.countif(wbb.sheets("b").rows(1), "plan_sku_*") > 0     col = application.match("rack po #*", wbb.sheets("b").rows(1), 0)     lastrow = wbb.sheets("b").cells.find(what:="*", searchdirection:=xlprevious, searchorder:=xlbyrows).row     set rngpo = range(cells(2, col), cells(lastrow, col))     'do whatever want range here else     msgbox "the column named rack po #* not found in row1.", vbexclamation, "column not found!"     exit sub end if       msgbox "the range is" & rngm.address       msgbox "the range is" & rngsku.address        msgbox "the range is" & rngpo.address      wba.sheets("sourcesheet").range("f4").resize(rngm.rows.count).value = rngm.value     wba.sheets("sourcesheet").range("e4").resize(rngsku.rows.count, 1).value = rngsku.value     wba.sheets("sourcesheet").range("c4").resize(rngpo.rows.count).value = rngpo.value  msgbox "the range is" & rngm.address       msgbox "the range is" & rngsku.address        msgbox "the range is" & rngpo.address end sub 

the code seems run (finding correct columns, assigning variables, , displaying correct range) problem seems lie in these 3 lines:

wba.sheets("sourcesheet").range("c4").resize(rngpo.rows.count).value = rngpo.value

i appreciate help.

you not qualifying worksheet referring in statements such as

set rngm = range(cells(2, col), cells(lastrow, col)) 

those statements therefore set range on activesheet.

you should qualify range , cells:

set rngm = wbb.sheets("b").range(wbb.sheets("b").cells(2, col), wbb.sheets("b").cells(lastrow, col)) 

---

your code after rewriting end looking like:

sub findazuredataandcopyit()      dim wbb excel.workbook     dim wba excel.workbook     dim ed excel.worksheet      set wbb = workbooks("source.xlsx")     set wba = workbooks("model.xlsb")      dim col long, lastrow long     dim rngm range     dim rngsku range     dim rngpo range      'use block save typing 'wbb.worksheets("b").' on , on     wbb.worksheets("b")         'set lastrow once - no need each time range needs set         lastrow = .cells.find(what:="*", _                               searchdirection:=xlprevious, _                               searchorder:=xlbyrows).row          if application.countif(.rows(1), "plan_tamaward*") > 0             col = application.match("plan_tamaward*", .rows(1), 0)             'fully qualify `range` , `cell` (etc) objects             set rngm = .range(.cells(2, col), .cells(lastrow, col))         else             msgbox "the column named plan_tamaward* not found in row1.", vbexclamation, "column not found!"             exit sub         end if          'set range sku         if application.countif(.rows(1), "plan_sku_*") > 0             col = application.match("plan_sku_*", .rows(1), 0)             set rngsku = .range(.cells(2, col), .cells(lastrow, col))         else             msgbox "the column named plan_sku* not found in row1.", vbexclamation, "column not found!"             exit sub         end if          ' set range po         'if application.countif(.rows(1), "plan_sku_*") > 0 ' <-- seems wrong         if application.countif(.rows(1), "rack po #*") > 0  ' <-- maybe this?             col = application.match("rack po #*", .rows(1), 0)             set rngpo = .range(.cells(2, col), .cells(lastrow, col))         else             msgbox "the column named rack po #* not found in row1.", vbexclamation, "column not found!"             exit sub         end if     end      msgbox "the range is" & rngm.address     msgbox "the range is" & rngsku.address     msgbox "the range is" & rngpo.address      'use block save typing 'wba.worksheets("sourcesheet").' 3 times     wba.worksheets("sourcesheet")         .range("f4").resize(rngm.rows.count, 1).value = rngm.value         .range("e4").resize(rngsku.rows.count, 1).value = rngsku.value         .range("c4").resize(rngpo.rows.count, 1).value = rngpo.value     end      msgbox "the range is" & rngm.address     msgbox "the range is" & rngsku.address     msgbox "the range is" & rngpo.address end sub