Extracting same information from seperate tabs

[moonpig]

Hi

Help needed!

I need to extract matching information from seperate tabs onto a new tab? Can someone please help?

Further can I do as above say from two different spreadsheets onto a new one?

 

[hallingh]

Is the info coming from a specific row or column from the different sheets? Can you provide some more info as to what you're trying to accomplish?

 

[moonpig]

I am trying to extract specific words from the seperate spreadsheets and collate them into a new spreadsheet... these words are in columb A and B of the sheet, (with different information on rows c d etc) but I just need to extract the first and second columbs
I am trying to combine a seperate sheet with matching information only

 

[hallingh]

Ok, I think i understand now.

So there are two tabs, with information in a number of columns. If the info in columns a and b is the same on both tabs, you want to copy the info from that row into a third tab? Is that right?

Also, do you want to copy over all hte data from the first two sheets or just columns a and b?

 

[moonpig]

Yes, there are several columbs, but I just need to extract columbs A and B into a third tab (the matching items)

 

[hallingh]

Sorry, I just got hit with a ton of work. I was in the process of writing this, so I just finished it up quickly. This is not tested AT ALL, and I was rushing through it, so make sure to test it on a copy of the workbook. If this is messed up and noone gets to it I can fix it up tomorrow. Sorry about that.

Sub DoIt()
Dim i As Long
Dim RowDest As Long
RowDest = 1
For i = 1 To Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
If Sheets("Sheet1").Range("A" & i).Value = Sheets("Sheet2").Range("A" & i).Value And Sheets("Sheet1").Range("B" & i).Value = Sheets("Sheet2").Range("B" & i).Value Then
Sheets("Sheet3").Range("A" & RowDest & ":B" & RowDest).Value = Sheets("Sheet1").Range("A" & i & ":B" & i).Value
RowDest = RowDest + 1
End If
Next i
 
 

End Sub

HTH

Hank

 

[moonpig]

thanks very much for the help Hank, however I am a bit of an Excel 'virgin' can you or someone dumb it down a little?

thanks in advance

 

[hallingh]

Certainly. Sorry about that, like I said I was in a bit of a rush putting this together. To do this you're going to have to go into the vba section of Excel, which is where you can code using visual basic. To get there, click on the developer tab in the ribbon. This will be towards the right of the ribbon. Under the developr tab, click the "View Code" button. This will open a new window. In the window, paste what I wrote for you in my previous post and click the button in the ribbon that looks like a play button from a dvd player. This will run the procedure I gave you.

Like I said, definitely save your workbook before you run the code I gave you. I'm still pretty busy, and haven't tested it. I should be freed up in a couple hours, so I can look into it again for you then if it didn't work.

HTH

Hank