vba to find a cell containing a value then select a specific range below it

[cjcass]

Hi,
I'm looking for a macro that will look at the value (text) in cell A1 of Sheet1 then go to Sheet2 and search for the same value (it will be unique) in Row 4 of Sheet2 and then when it finds the location in Row 4 select the column which is one column to the right and rows 10:200 of that column.
Example: Sheet1 A1 contains the word 'London', it searches row 4 in Sheet2 for 'London', finds it at BS4 and then selects the range BT10:BT200.
Hope this makes sense?
Thanks

 

[Peter_SSs]

Can you tell us what you are going to do with the range once selected?
I ask because it is rare that you need to actually select a range to work with it and selecting slows your code.

 

[cjcass]

Hi,
I'm going to copy it and paste it into another sheet

 

[Peter_SSs]

Hi,
I'm going to copy it and paste it into another sheet

OK, you didn't tell us what sheet is to get copied to or what range on that sheet so you will have to modify the blue part of this code but hopefully this gets you close (without selecting that range. )

Sub FindAndCopyRange()
  Dim rFound As Range
  
  Set rFound = Sheets("Sheet2").Rows(4).Find(What:=Sheets("Sheet1").Range("A1").Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
  If Not rFound Is Nothing Then rFound.Offset(6, 1).Resize(191).Copy Destination:=Sheets("Sheet3").Range("B8")
End Sub

 

[cjcass]

That's great, just one final bit, I'd like to copy just the values if poss. how would I adjust the code to suit this?
Thanks.

 

[Peter_SSs]

I'd like to copy just the values if poss.

try

Sub FindAndCopyRange()
  Dim rFound As Range
  
  Set rFound = Sheets("Sheet2").Rows(4).Find(What:=Sheets("Sheet1").Range("A1").Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
  If Not rFound Is Nothing Then
    rFound.Offset(6, 1).Resize(191).Copy
    Sheets("Sheet3").Range("B8").PasteSpecial xlValues
    Application.CutCopyMode = False
  End If
End Sub

 

[cjcass]

Brilliant, many thanks for your help.

 

[Peter_SSs]

Brilliant, many thanks for your help.

You are very welcome.

 

[Travis Kunnen]

Hello

I am seeking a similar problem to the above, but with a twist.
I have a large Excel data base of bacteria Genus' and i need to match the name in one worksheet, to another worksheet, copy that whole row (or a selection, say 4 columns), and paste into another worksheet.
Example... I have 3 worksheets A, B, C

Worksheet A has 1000's of rows of names and many columns of data to each row name, but the name is always in Column A and will be alphabetical.

Worksheet B has the pasted names in Column A (also alphabetical) im looking to match with in Worksheet A, column A.
Worksheet C, is for pasting, or can be pasted into B, but for now easier into worksheet C.

So... open worksheet B, cell A1.. that word there "ABAB", use this cell as the search term to search in worksheet A, cell A1... if a match... copy all of worksheet A Row 1, and paste into worksheet C Row 1.

Repeat, open worksheet B, cell A2... that word there, "GHGH, use this cell as the search term to search in worksheet A, cell A2 = negative, cell A3 = negative, cell A4 match. Copy entire row A4, paste into worksheet C row 2..... Continue until blank empty cell in worksheet B, Column A.... this can be hundreds to 1000's of rows long.

So the search word / term will be different for each search, and terms shouldnt be repeated, cause it will be alphabetical.
This is my problem, and I am currently doing this manually.. term by term.

 

[Joe4]

Hello

I am seeking a similar problem to the above, but with a twist.
I have a large Excel data base of bacteria Genus' and i need to match the name in one worksheet, to another worksheet, copy that whole row (or a selection, say 4 columns), and paste into another worksheet.
Example... I have 3 worksheets A, B, C

Worksheet A has 1000's of rows of names and many columns of data to each row name, but the name is always in Column A and will be alphabetical.

Worksheet B has the pasted names in Column A (also alphabetical) im looking to match with in Worksheet A, column A.
Worksheet C, is for pasting, or can be pasted into B, but for now easier into worksheet C.

So... open worksheet B, cell A1.. that word there "ABAB", use this cell as the search term to search in worksheet A, cell A1... if a match... copy all of worksheet A Row 1, and paste into worksheet C Row 1.

Repeat, open worksheet B, cell A2... that word there, "GHGH, use this cell as the search term to search in worksheet A, cell A2 = negative, cell A3 = negative, cell A4 match. Copy entire row A4, paste into worksheet C row 2..... Continue until blank empty cell in worksheet B, Column A.... this can be hundreds to 1000's of rows long.

So the search word / term will be different for each search, and terms shouldnt be repeated, cause it will be alphabetical.
This is my problem, and I am currently doing this manually.. term by term.

You should post your question to a new thread of its own, instead of posting to a thread that is 7 years old.
That way, it will appear as a new question on the "Unanswered threads" listing that many people use to look for new questions to help out on.
You can include a link back to this thread if you think that would be helpful to your question.