CODEWITHSUNDEEP
excelvbaif-statement
user10101843
user10101843

Reputation: 41

Finding column to paste starting at row

I have been looking for some time now and I see posts concerning pasting an entire column starting at a specific row, but in my case my column is based on an if statement for a value. I am still an amateur so i'm guessing the answer has to do with setting an i somewhere to find the correct column, but I do not know how to incorporate that in my case. The code below works I just do not know how to copy starting at row 3 instead of using EntireColumn. Thank you for the help and let me know if I left any information out.

    Sub ColumnCopy()

For Each cell In ActiveWorkbook.Sheets("Sheet1").Range("B5:AZ3000")
    If cell.Value = "Active" Then Cell.EntireColumn.Copy Destination:=ActiveWorkbook.Sheets("Sheet2").Columns(2)
Next

 End Sub

Upvotes: 0

Views: 61

Answers (1)

urdearboy
urdearboy

Reputation: 14590

I would amend some options to the Range.Find method. Case sensitive? Look in xlPart or xlWhole? Etc. This is currently set to search from Columns A - Z to find Active. You can extend this if needed

Option Explicit

Sub FindHeader()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim Found As Range, LRow As Long

Set Found = ws.Range("A5:Z5").Find("Active")

If Not Found Is Nothing Then
    LRow = ws.Cells(ws.Rows.Count, Found.Column).End(xlUp).Row
    ws.Range(ws.Cells(3, Found.Column), ws.Cells(LRow, Found.Column)).Copy
    ThisworkBook.Sheets("Sheet2").Range("B3").PasteSpecial xlPasteValuesAndNumberFormats
End If

End Sub

Upvotes: 1

Related Questions