Find a text and select the entire used column

[Jeyaraj2021]

Hi,

I need to find a text (ex: 90day) from header and based on this search i need to select the until the last used column(not the entire column) and then need to copy and paste.

But this 90day text can appear in any of the column, so the vba to find the specific text and select same last used column

Hope am clear on my questions
Thanks,
Jeyaraj M

 

[maabadi]

Welcome To MrExcel Message Board.
1. Are your header row is Row 1?
2. if your lastrow at column for e.g. is 25 then Copy row 1 to 25 at that column?
3. where you want paste?
4. Are you familiar with VBA?

 

[Jeyaraj2021]

1)Yes row header in Row1 (the text 90day may appear in any column as it is dynamic) so cant able to say the specific column. it has to find the text 90day and then select that particular column (until last used)
2)yes to select only the last used column
3) eg copy from sheet 1 to sheet 2
4) yes

 

[maabadi]

Try this:

Sub FindColumn()
Dim i As Long, j As Long, Lr As Long, LC As Long
LC = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
For j = 1 To LC
If Sheets("Sheet1").Cells(1, j).Value = "90day" Then
Lr = Sheets("Sheet1").Cells(Rows.Count, j).End(xlUp).Row
Range(Sheets("Sheet1").Cells(1, j), Sheets("Sheet1").Cells(Lr, j)).Copy Sheets("Sheet2").Range("A1")
End If
Next j
End Sub

 

[Jeyaraj2021]

Try this:

Sub FindColumn()
Dim i As Long, j As Long, Lr As Long, LC As Long
LC = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
For j = 1 To LC
If Sheets("Sheet1").Cells(1, j).Value = "90day" Then
Lr = Sheets("Sheet1").Cells(Rows.Count, j).End(xlUp).Row
Range(Sheets("Sheet1").Cells(1, j), Sheets("Sheet1").Cells(Lr, j)).Copy Sheets("Sheet2").Range("A1")
End If
Next j
End Sub

This worked for me , made some tweak based on my report. Thanks for your support

 

[maabadi]

You're Welcome & Thanks for Feedback.