Hi Team,
I want to copy specific headers . Below code copy 3 headers from array value.
I want your help in creating function if in future,columns to Copy Increases it should cover. any other method I am ok. like dictionary
Needs to make this line dynamic by creating a function :=> Array(1, 5, 10))
Sub working code
Dim ar As Variant
ar = Sheet1.Range("A1:Z19").value
Sheet2.Range("A1").Resize(UBound(Ar), 3).Value = Application.Index(Ar, Evaluate("Row(1:" & UBound(Ar) & ")"), Array(1, 5, 10))
end sub
My attempted Code...
I want to copy specific headers . Below code copy 3 headers from array value.
I want your help in creating function if in future,columns to Copy Increases it should cover. any other method I am ok. like dictionary
Needs to make this line dynamic by creating a function :=> Array(1, 5, 10))
Sub working code
Dim ar As Variant
ar = Sheet1.Range("A1:Z19").value
Sheet2.Range("A1").Resize(UBound(Ar), 3).Value = Application.Index(Ar, Evaluate("Row(1:" & UBound(Ar) & ")"), Array(1, 5, 10))
end sub
My attempted Code...
Rich (BB code):
Sub Copy_Columns()
Dim headerName As String
headerName = "Region,Due Date,Invoice No,Delivery Date,Document Date" ' Headers to Copy
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)
Dim ar As Variant
'Call function
ar = HeaderCopy(ws, headerName, 1)
Sheet2.Range("A1").Resize(UBound(ar), 3).Value = Application.Index(ar, Evaluate("Row(1:" & UBound(ar) & ")"), Array(10, 5, 1))
End Sub
Function HeaderCopy(ByVal ws As Worksheet, headerName As String, Optional ByVal hrow As Long = 1) As Variant
Dim rg As Range
Set rg = ws.Range("A1").CurrentRegion
Dim myarray As Variant
'myarray = rg.Value
'Dim dict As New Scripting.Dictionary
Dim Arr_header As Variant
Arr_header = Split(headerName, ",")
Dim m As Variant
Dim i As Long
For i = 0 To UBound(Arr_header,1)
m = Application.Match(Arr_header(i), ws.Rows(hrow), 0)
myarray(i) = m 'getting error type mismatch.
Next i
HeaderCopy = myarray
End Function