VBA Code to find matching cell text in a different sheet and copy paste that column

sadar · May 8th 2015

Hello,

I am new to VBA, and stuck in this project im working on. I use Excel 2007

I have 3 sheets, first one New Data, second one Static Data, third New Columns.

Basically, in sheet New Data I will have my new data set.

In sheet Static Data I will have 10 differents key words in cells E4 to E9. I want to be able to add new variables to be found, thats why it has to search for the text in cells in Column E and not for a specific text o value.

What I need to do is search in the headers of the New Data -Thus Row 1 of sheet New Data-, find where the 10 key words from the sheet Static Data are (or however many I want to have in column E of sheet Static Data) , and once found copy the column where its located and paste it one next to the other in the third sheet, New Columns.

To sum up, loop through the range of values with text on column E sheet Static Data until it finds it in row 1 on sheet New Date, copy paste the column where the text is found to sheet New Columns, until it reaches the end of the cells with text in column E on the sheet Static Data.

Any of your help is appreciated guys!

thanks in advance

sadar

sadar · May 8th 2015

Re: VBA Code to find matching cell text in a different sheet and copy paste that colu

Built a cone

Hey guys, so I have built this so far. I want to find Sheets("LookData").Range("E4") in sheet "RawData". It does find it. If it finds it, I just want to export the column where its located to sheet "NewRows". Thats where I think im failing. Any advice? Not naming the correct variable?

Can you help on that?

Code

[/COLOR]Sub Find_First()    Dim FindString As String    Dim Rng As Range    FindString = Sheets("LookData").Range("E4").Value    If Trim(FindString) <> "" Then        With Sheets("RawData").Range("A:A")            Set Rng = .Find(What:=FindString, _                            After:=.Cells(.Cells.Count), _                            LookIn:=xlValues, _                            LookAt:=xlWhole, _                            SearchOrder:=xlByRows, _                            SearchDirection:=xlNext, _                            MatchCase:=False)[B]                            'until here it works[/B]            If Not Rng Is Nothing Then                Columns(Rng.Column).EntireColumn.Copy _          Destination:=Sheets("NewRows").Range("A1")            Else                MsgBox "Nothing found"            End If        End With    End IfEnd Sub[COLOR=#333333]

[/COLOR]

**S O** · May 8th 2015

Re: VBA Code to find matching cell text in a different sheet and copy paste that colu

Hi sadar, welcome to OzGrid.

Please edit your post and wrap your code in tags, either by highlighting it and pressing the [#] button in the text editor, or by typing the tags out like so:

[NOPARSE]

Code

some code here

[/NOPARSE]

It may also help if you could attach a workbook with an example of what you are trying to achieve.

sadar · May 8th 2015

Re: VBA Code to find matching cell text in a different sheet and copy paste that colu

Done, btw this images probably help!

So,

I need to loop through the 3 cells in Sheet Static Data in the first row of the first Sheet NewData. Once found, just copy paste them in the third sheet New Columns.

Thanks a lot for any help!

[Blocked Image: http://i58.tinypic.com/28s39tf.png]

[Blocked Image: http://i58.tinypic.com/2r7boty.png]

RESULT

[Blocked Image: http://i60.tinypic.com/11uymo8.png]

**Reafidy** · May 9th 2015

Re: VBA Code to find matching cell text in a different sheet and copy paste that colu

Try this:

Code

Sub HTH()
    
    Dim rCell As Range
    Dim rFind As Range
    Dim iColumn As Integer
    
    For Each rCell In Sheets("StaticData").Range("E4", Sheets("StaticData").Cells(Rows.Count, "E").End(xlUp))
    
       Set rFind = Sheets("NewData").Rows(1).Find(What:=Trim(rCell.Value), _
            LookIn:=xlValues, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False)
            
            If Not rFind Is Nothing Then
            iColumn = iColumn + 1
                Sheets("NewData").Columns(rFind.Column).Copy Sheets("NewColumns").Columns(iColumn)
            End If
    Next rCell




End Sub

Display More

sadar · May 9th 2015

Re: VBA Code to find matching cell text in a different sheet and copy paste that colu

Thanks a lot works well!

One question, whats 'trim' doing in the formula? And what would change from End(xlUp)) to xlDown?

thanks a lot again!

**Reafidy** · May 10th 2015

Re: VBA Code to find matching cell text in a different sheet and copy paste that colu

You were using trim in your sample code so I presumed it was a requirement and that your headings may have white spaces. xlup looks for the next used cell above the last cell in the column.

VBA Code to find matching cell text in a different sheet and copy paste that column

Participate now!

Share