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

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

Hi JRForm,

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

Mike

"JRForm" wrote:

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