Get rows from another tab

**imsims** · 03-12-2010, 04:47 AM

Hi all!

I have a excel-document with two tabs in it.
Tab2 has 6 columns and 383 rows.
Column "A", has a number in it.
The other columns contains various information that's not really important.

Tab1 concists only of a singel column, column "A", and 62 rows.

All of the numbers in column "A" in Tab1 are also found somewhere in column "A" in Tab2.

What I want to do is to single out the numbers from column "A" in Tab1 in column "A" in Tab2.
The result I'm looking for is a single tab with the 62 rows in Tab1, but now with the other 5 columns of corresponding information that's in Tab2.

This is something I'll just have to do once so I don't need a advanced function or whatever.
Maybe I can just search for the Tab1-numbers in Tab2 in someway?
Then delete all the other rows and save?

Thanks alot to whoever gives me an answer to this!!!

**Marcol** · 03-12-2010, 05:31 AM

Are the numbers in Tab1 col"A" found only once in Tab2 col"A"?

EDIT:- Code Added

If so try

Option Explicit

Sub CopyRowsSheet2toSheet1()
    Dim shtCopyTo As Worksheet, shtCopyFrom As Worksheet
    Dim LastRow As Long, FoundRow As Range, Counter As Long
    Dim Match As Variant
    
    Set shtCopyTo = ThisWorkbook.Sheets("Sheet1")
    Set shtCopyFrom = ThisWorkbook.Sheets("Sheet2")
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For Counter = 2 To LastRow
        Match = Cells(Counter, "A")
        Set FoundRow = shtCopyFrom.Range("A:A").Find(Match, Range("A2"), xlValues, xlWhole)
        If Not FoundRow Is Nothing Then
            shtCopyFrom.Rows(FoundRow.Row).Copy shtCopyTo.Cells(Counter, "A")
        Else
            shtCopyTo.Cells(Counter, "A").Interior.ColorIndex = 3 'red
        End If
    Next
    
    Set shtCopyTo = Nothing
    Set shtCopyFrom = Nothing
End Sub

If no match is found then the cell in "Sheet1" will be highlighted Red

Change "Sheet1" and "Sheet2" to suit your sheet names

**teylyn** · 03-12-2010, 06:03 AM

Hi imsims,

welcome to the forum.

You can create a new sheet, let's call it Sheet3
In cell A1 of this sheet, enter

='Tab1'!A1

If your "Tab1" in your actual file has a different name, adjust the formula.

Copy this cell down to row 62.

In B1 of your Sheet3 put this formula:

=vlookup(A1,'Tab2'!$A$1:$A$400,column(B1),false)

If your "Tab2" in your actual file has a different name, adjust the formula.

Copy cell B1 across to cell F1
Copy B1:F1 down to B62:F62

Now you should have the six columns populated for the 62 codes in column A

hth

**imsims** · 03-12-2010, 06:30 AM

Thanks for helping me out!!!
Unfortunately I'm a total noob at this (obviously) and I don't know where to put that code or if I have to exicute it in some way?

**imsims** · 03-12-2010, 06:36 AM

I think I'm getting close with your solution teylyn! Thanks!
I forgot to mention that the very first row in each tab has titles in them. Should I delete them or change "A1" to "A2" where the info actually starts?

Also, when I paste this =vlookup(A1,'Tab2'!$A$1:$A$400,column(B1),false) in B1 as you told me, I get an error. I changed Tab2 to Blad2 which is the actual name of the tab. But it still gives me an error.
When you say "Copy this cell down..", do you mean Ctrl C -> Ctrl V in each cell or just click,drag and expand the cell all the way down to row 62?

Thanks to both of you!!!

**Marcol** · 03-12-2010, 06:47 AM

In that case go Teylyns' way.

But if you want to try the code route

I/. Open the visual basic editor.....Tools>Macro>Visual Basic Editor
2/. In the editor .......Insert>Module
3/. Paste all the code in this module
4/. Change "Sheet1" and "Sheet2" to suit your sheet names
5/. Close the Editor
6/. In Excel .......Tools>Macros... There will only be one macro Press Run
or Alt+f8.....Press Run

Hope this helps in the future

P.S.
Changed code to allow for header

**imsims** · 03-12-2010, 07:13 AM

DUDE!
That macro worked great!
I appreciat that so much! It's great that there are guys out there like you.
Thank to teylyn too!
Take care

**Marcol** · 03-12-2010, 07:35 AM

Happy to have helped

If your problem is resolved, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!