Get subset of row into a variable using excel vba -

i want copy adapted version of subset of headers' row of 1 workbook new workbook using vba.

initially, idea to:

1. read original header row range array of strings variable
2. manipulate array, namely dropping/eliminating , adding entries
3. write transformed array destination location/workbook

since unable find tolerable way eliminate , add entries array, though maybe better to:

1. only read wanted sections of original header array variable
2. rename needed entries , in array
3. write transformed array destination workbook

i have little no experience in vba, such simple operation 1 seems nightmare.

my try below:

' initialization dim wbkold workbook dim wbknew workbook set wbkold = workbooks.open(filename:="filepath", readonly:=true) set wbknew = workbooks.add wbknew.saveas filename:="filename"  ' step 1: read wanted sections of original header ' know size of target headers row beforehand dim strheadersarr(1 6) string ' problem: ' following statement work if wanted load entire headers row... strheadersarr = wbkold.sheets(1).usedrange.rows(1).value  '... want load subsection ' (e.g. first 4 headers/cells , last 2 headers/cells, dropping 5 headers/cells in between) _unknown code here_  ' step 2: rename needed entries strheaderarr(2) = "newcolumnname"  ' step 3: write down wkbnew.sheets(1).range("a1").value = strheaderarr 

also appreciated if proposed different/better way accomplish i'm looking for.

wiki