complicated search and extract.... i think
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?
"mike" wrote:
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
|