0

I want to copy an adapted version of a subset of the headers' row of one workbook to a new workbook using VBA.

Initially, my idea was to:

  1. Read original header row range into an array of strings variable
  2. Manipulate the array, namely dropping/eliminating and adding some entries
  3. Write the transformed array to the destination location/workbook

Since I was unable to find a tolerable way to eliminate and add entries into the array, I though maybe it was better to:

  1. Only read the wanted sections of the original header into the array variable
  2. Rename the needed entries and in the array
  3. Write the transformed array to the destination workbook

I have little to no experience in VBA, but such a simple operation as this one seems to be a nightmare.

My try below:

' Initialization
Dim wbkOld As Workbook
Dim wbkNew As Workbook
Set wbkOld = Workbooks.Open(Filename:="filepath", ReadOnly:=True)
Set wbkNew = Workbooks.Add
wbkNew.SaveAs Filename:="filename"

' STEP 1: Only read wanted sections of the original header
' I know the size of the target headers row beforehand
Dim strHeadersArr(1 To 6) As String
' Now the problem:
' The following statement would work if I wanted to load the entire headers row...
strHeadersArr = wbkOld.Sheets(1).UsedRange.Rows(1).Value 
'... but I want to load only a subsection
' (e.g. the first 4 headers/cells and then the last 2 headers/cells, dropping the 5 headers/cells in between)
_unknown code here_

' STEP 2: Rename the needed entries
strHeaderArr(2) = "newColumnName"

' STEP 3: Write down
wkbNew.Sheets(1).Range("A1").Value = strHeaderArr

Also be appreciated if you proposed a different/better way to accomplish what I'm looking for.

Community
  • 1
  • 1
POliveira
  • 196
  • 5
  • 14
  • since you have only 1 to 6 header columns and this thing is not working... how about reading the cells with header name in an array one by one using a loop...? – Abhinav Rawat Sep 07 '17 at 13:31
  • but i thing this can also work ... [See Here](https://stackoverflow.com/questions/37689847/creating-an-array-from-a-range-in-vba) – Abhinav Rawat Sep 07 '17 at 13:34
  • This is a simplified example. Actual header contains many columns more. I understand I could find a way to loop through all the column headers, but that seems too complicated for such a simple need. – POliveira Sep 07 '17 at 14:05
  • so then you should use the link in the second comment, you have the range `wbkOld.Sheets(1).UsedRange.Rows(1)` get a array of values from it... – Abhinav Rawat Sep 07 '17 at 14:09
  • The thing is that I want a subsection of the array, not all of it. I'm looking for a way to only load/read a part of the original range to the variable. Something like `strHeaderArr = wkbOld.Sheets(1).UsedRange(1, 1:4 & end-1:end).Value` – POliveira Sep 07 '17 at 14:29
  • Try `OutputArray()` and `Output2DArray()` from [this answer](https://stackoverflow.com/a/43578980/2165759). – omegastripes Sep 07 '17 at 16:03

0 Answers0