I want to copy some columns with headers from a worksheet to another one. I've created an array that looks for the different headers needed so I can copy and paste the entire column into the new tab. I know I have an error somewhere because I'm getting a type mismatch error and possibly other types as well. Can someone take a look and see what I'm missing/have wrong?
Dim rngCell As Range
Dim strHeader() As String
Dim intColumnsMax As Integer
Sheets.Add.Name = "Material Master"
Sheets.Add.Name = "BOM"
intColumnsMax = Sheets("HW Zpure Template").UsedRange.Columns.Count
ReDim strHeader(1 To intColumnsMax)
strHeader(1) = "MATERIAL"
strHeader(2) = "MATERIAL TYPE"
strHeader(3) = "MATERIAL DESCRIPTION"
For Each rngCell In Rows(4)
For i = 1 To intColumnsMax
If strHeader(i) = rngCell.Value Then
rngCell.EntireColumn.Copy
Sheets("Material Master").Select
ActiveSheet.Paste Destination:=Worksheets("Material Master").Cells(1, i)
Sheets("HW Zpure Template").Select
End If
Next i
Next