jufglanville
New Member
- Joined
- Sep 11, 2017
- Messages
- 23
Hi all, I'm very new to VBA but came across a piece of code that does pretty much exactly what I want but am wanting to modify it slightly but have no idea how. Basically the code looks at a heading from one sheet and looks to see if it exists in another and if it does copies the column contents and places it back on the first sheet.
My problem is that some of the cells in the columns contain blanks and I am using the xlDown function so all data past this point is missed. I am wanting to change this 'xlDown' to the last row number found in column A as this column never contains missing cells.
Below is the code:
Any help would be great, thanks.
My problem is that some of the cells in the columns contain blanks and I am using the xlDown function so all data past this point is missed. I am wanting to change this 'xlDown' to the last row number found in column A as this column never contains missing cells.
Below is the code:
Code:
Private Sub CopyHeaders() Dim header As Range, headers As Range
Set headers = Worksheets("FPS").Range("A1:BK1") '
For Each header In headers
If GetHeaderColumn(header.Value) > 0 Then
Range(header.Offset(1, 0), header.End(xlDown)).Copy Destination:=Worksheets("TempTable").Cells(2, GetHeaderColumn(header.Value))
End If
Next
End Sub
Function GetHeaderColumn(header As String) As Integer
Dim headers As Range
Set headers = Worksheets("TempTable").Range("A1:Y1")
GetHeaderColumn = IIf(IsNumeric(Application.Match(header, headers, 0)), Application.Match(header, headers, 0), 0)
End Function
Any help would be great, thanks.