Search for a Value then Copy and Paste the data within that Column

ksstormhunter · Mar 23rd 2013

I have a value located in sheet5.range("b18"), which is a four letter text, such as KHDC

I need to search sheet22 in Row 1 through all the columns until it finds KHDC, There is other data located within each row 1 and column. An example is Station ID: KHDC Lat: When the value is found copy that column starting at row 1 to 150 and paste into sheet 7 range "a45".

The value in B18 will not always be KHDC, but can change based on user input.

Smallman · Mar 23rd 2013

Re: Search for a Value then Copy and Paste the data within that Column

Hi Ksstormhunter

Here is an example using 3 sheets which you should be able to adapt to your needs.

Code

Option Explicit


Sub FindStr()
    Dim Found As Range
    Dim str As String
    Dim fn As Integer
    
    str = Sheet1.[B18]
    Set Found = Sheet2.Cells.Find(str)
      If Not Found Is Nothing Then
        fn = Found.Column
        Sheet2.Range(Sheet2.Cells(1, fn), Sheet2.Cells(Sheet2.Cells(Rows.Count, fn).End(xlUp).Row, fn)).Copy Sheet3.[A45]
      End If
End Sub

Display More

Just change the sheet names to suit, if you can't get it to work I will attach a workbook which shows that it does in fact work.

Take care

Smallman

Search for a Value then Copy and Paste the data within that Column

Participate now!

Share