Excel VBA Copy Paste column from sheet 1 to sheet 2 if first row countain the word "End Date"

Question

I am trying to make a macro that reads the first row of Sheet 1 and copy pastes the whole column of the cell that contains the word "End Date" to Sheet 2 column B.

Your help is much appreciated! Thanks again

Answer 1

You can give this a test. It will scan your header column for "End Date" and then copy the data below (header is not copied) and paste the data on Sheet2 Range B2 (I'm assuming you have a header here as well).

You will need to put this in a module, modify sheet names (Sheet1 & Sheet2).

Sub EndDate()

Dim WS As Worksheet
Set WS = ThisWorkbook.Sheets("Sheet1")
Dim i As Integer, LCol As Long, LRow As Long

With WS
    LCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    For i = 1 To LCol
        If .Cells(1, i) = "End Date" Then
            LRow = .Cells(.Rows.Count, i).End(xlUp).Row
            .Range(.Cells(2, i), .Cells(LRow, i)).Copy
            ThisWorkbook.Sheets("Sheet2").Range("B2").PasteSpecial
            Exit Sub
        End If
    Next i
End With

End Sub