Excel VBA: Run-time error '438'

Question

Hi can anyone provide some assistance in this case.

I want the worksheets("register") to copy data from worksheets("source") on the last black cell in worksheets("register") based on the heading sequences on the worksheets("register")

Q1.-Do I need loop in this case?

Q2-my code below has a run time error 438-object doesn't support this property or method. I think the way how I refered the lastrow in the highlighted line is not right, but I don't how to correct it.

Any help will be much appreciated!

Sub CopysourcedatetoRegister()

    Dim LastRow As Long

    LastRow = Worksheets("Register").Cells(Rows.Count, 1).End(xlUp).Offset(1).Row

    Dim header As Range, headers As Range

    Set headers = Worksheets("source data").Range("A1:v1")

    For Each header In headers

        If GetHeaderColumn(header.Value) > 0 Then

Range(header.Offset(1, 0), header.End(xlDown)).Copy Destination:=Worksheets("register").Cells(LastRow, 1).GetHeaderColumn(header.Value)

        End If

    Next

End Sub

Function GetHeaderColumn(header As String) As Integer

    Dim headers As Range

    Set headers = Worksheets("register").Range("A1:Z1")

    GetHeaderColumn = IIf(IsNumeric(Application.Match(header, headers, 0)), Application.Match(header, headers, 0), 0)

End Function

***Subject edited for clarity by the moderator.***

[Original Title: vba]

Answer 1

If I am interpreting correctly what you are attempting to do then like the code below.

In the following line of your code Rows.Count is referencing the ActiveSheet; not necessarily Worksheet Register.

LastRow = Worksheets("Register").Cells(Rows.Count, 1).End(xlUp).Offset(1).Row

Should be like the following line but personally I prefer to use With / End With as per my example code.

LastRow = Worksheets("Register").Cells(**Worksheets("Register").**Rows.Count, 1).End(xlUp).Offset(1).Row

Note that the space and underscore at the end of a line is a line break in an otherwise single line of code. (Used below after Copy).

The bold code in the Destination was the major correction to your code..

Sub CopysourcedatetoRegister()

     Dim LastRow As Long

     With Worksheets("Register")

        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Row

     End With

     Dim header As Range, headers As Range

     Set headers = Worksheets("source data").Range("A1:v1")

    For Each header In headers

         If GetHeaderColumn(header.Value) > 0 Then

            Range(header.Offset(1, 0), header.End(xlDown)).Copy _

                Destination:=Worksheets("register").Cells(LastRow, GetHeaderColumn(header.Value))

         End If

     Next

 End Sub

Function GetHeaderColumn(header As String) As Integer

     Dim headers As Range

     Set headers = Worksheets("register").Range("A1:Z1")

     GetHeaderColumn = IIf(IsNumeric(Application.Match(header, headers, 0)), Application.Match(header, headers, 0), 0)

 End Function

Answer 2

thanks it worked. I am brand new to the Macro. Truly appreciated your help and explanation!