excel vba - Non-contiguous named range into an array, then into row in different sheet -

i'm trying data posted non-contiguous range row in separate sheet. before built non-contiguous range, code worked perfectly. i've tried several things loop through, nothing tried work. won't copy ranged data sits. it's been years since i've done coding , re-learning curve seems holding me back.... logic isn't coming me. help!

sub updatelogworksheet()  dim historywks worksheet dim inputwks worksheet  dim nextrow long dim ocol long  dim mycopy range dim mytest range dim mydata range  dim lrsp long  set inputwks = worksheets("input") set historywks = worksheets("dataentry") ocol = 3 'order info pasted on data sheet, starting in column  'check duplicate vin in database if inputwks.range("checkvin") = true   lrsp = msgbox("vin in database. update record?", vbquestion + vbyesno, "duplicate vin")   if lrsp = vbyes     updatelogrecord   else     msgbox "please change vin unique number."   end if  else    'cells copy input sheet - contain formulas    set mycopy = inputwks.range("vehicleentry") 'non-contiguous named range    historywks       nextrow = .cells(.rows.count, "a").end(xlup).offset(1, 0).row   end    inputwks       'mandatory fields tested in hidden column       set mytest = mycopy.offset(0, 2)        if application.count(mytest) > 0           msgbox "please fill in cells!"           exit sub       end if   end    historywks       'enter date , time stamp in record       .cells(nextrow, "a")           .value =           .numberformat = "mm/dd/yyyy hh:mm:ss"       end       'enter user name in column b       .cells(nextrow, "b").value = application.username       'copy vehicle data , paste onto data sheet        mycopy.copy       .cells(nextrow, ocol).pastespecial paste:=xlpastevalues, transpose:=true       application.cutcopymode = false   end    'clear input cells contain constants   clear end if  end sub 

this example explain how achieve want. please amend code suit needs.

let's say, have sheet1 looks shown below. colored cells make non contiguous range.

now paste code given below in module , run it. output generated in sheet2 , sheet3

code

sub sample()     dim rng range, acell range     dim myar() variant     dim n long, long      '~~> change relevant sheet     sheet1         '~~> non contiguous range         set rng = .range("a1:c1,b3:d3,c5:g5")          '~~> count of cells in range         n = rng.cells.count          '~~> resize array hold data         redim myar(1 n)          n = 1          '~~> store values range         '~~> array         each acell in rng.cells             myar(n) = acell.value             n = n + 1         next acell     end      '~~> output data in sheet      '~~> vertically output sheet 2     sheet2.cells(1, 1).resize(ubound(myar), 1).value = _     application.worksheetfunction.transpose(myar)      '~~> horizontally output sheet 3     sheet3.cells(1, 1).resize(1, ubound(myar)).value = _     myar end sub 

vertical output

horizontal output

hope above example helps in achieving want.