complicated search and extract.... i think

[Guest]

Hello everybody,
It's been a while since I posted last, but yet again I'm here to ask for
your wisdom and advice.

The task: I have a worksheet, each column contains specific information.
Row 2 is the company location.
I would like to create a macro to search for a location, so for every
occurence of "london" in Row 2. I would then like to copy this information to
a new sheet, rename the sheet and print.
The macro needs to extract information from row 1 and row 5 of each column
and place this in the new sheet.

ie. current sheet...
co. x co. y co. z
row 1 a d f
row 2 b b g
row 3 c e h

the new sheet

co. x b a
co. y b d

I hope that make sense.
Ok, so I know how to write a simple macro and get it going but I don't know
where to begin or how to loop, search, extract, etc.

Any help would be greatly appreciated.
Thanking you

Mike

 

[Guest]

mike,

Do you want to search the location row which is row2?
Then copy the data from row1 and row 5 for the new sheet?
If you need to copy the data from row 5 and row 1 of the same column then
this code may help

assuming your searching row 2 for London

Sub Mike()
Dim r1, r5 As String
Sheets("Sheet4").Select
Range("a21").Select 'Just where I started
If ActiveCell <> "" Then
Do Until ActiveCell = ""
If ActiveCell = "London" Then
r1 = ActiveCell.Offset(-1, 0)
r5 = ActiveCell.Offset(3, 0)
End If
ActiveCell.Offset(0, 1).Select
Loop
End If

Is this part of what you are looking for?

 

[Guest]

Hi JRForm,

Thanks for helping.
That is what I'm looking for, I'll try and let you know what happens.
Thanks.

Mike