Macro - Get adjacent cell values based on a condition & save as a different workbook

[ManInRed]

Hi,

here's my requirement.

1) find a Column Name " Factor Name"
2) Look down in the same column for a particular value such as "ABC" or "DEC" on all the cell values below this column name (which is Factor Name), say A10
3) if there is a match, copy all the values from adjacent cell values, Say A11 to A21

i am trying to do it using functions such as MATCH & OFFSET

so, the Macro should do the following
with or Without opening the workbooks in that directory, navigate to the multiple Sheets (which is given as Input) of that workbook and extract the data as per above 3 steps and save it as new workbook

Please assist me. Many thanks

Best Regards

[HaHoBe]

Hi, ManInRed,

this code starts on the active sheet and searches the header in row 1 and will copy to Sheet3:

Please Login or Register  to view this content.

Ciao,
Holger

[ManInRed]

Hi Holger,

thanks for your reply.
the code didnt go into the first IF condition..

it executed the statement and it came out..


Regards
MIR

[HaHoBe]

Hi, ManInRed,

the code didnt go into the first IF condition..

So either the seach item isnīt found in the first row of the sheet (you could alter the area to search to like Rows("1:4") for the first 4 rows in the sheet) or the search doesnīt need to search for the whole item to match but for a partly match so that the parameter LookAt:=xlWhole should be LookAt:=xlPart if the item is preceeded by a blank like in your first mentioning of the search string (which I thought to be a typo).

Ciao,
Holger

[ManInRed]

yes, now it worked. i did the change as you had mentioned.
so it do not copy the header. it copies the contents below.

if in case if i want to copy teh header as well. ie., the column name which consists of Factor Name also to be copied. - with that statement it didnt work out well..

i am tryign out other options

[HaHoBe]

Hi, ManInRed,

you already havbe set a range to that value so it should not really be a big problem. My suggestion might be

Please Login or Register  to view this content.

Ciao,
Holger

[ManInRed]

thanks Holger. i will tell you how it goes tomorrow.

i also need to do folowing tasks
a) need to get unique values based on one column (Factor Name)
b) and get the contents from 5 different sheets based on that Factor name

need to work on that tomorrow

Best Regards

[ManInRed]

Hi Holger,

the code is not working now. couldn't find the issue.
there is no error and there are no contents copied..!! Please help

Dim rngHeader As Range
Dim rngDetail As Range

ThisWorkbook.Activate
ThisWorkbook.Sheets("Test Cycle 1").Select

Set rngHeader = Rows("1:300").Find(What:="Factor Name", LookAt:=xlWhole)

If Not rngHeader Is Nothing Then
Set rngDetail = rngHeader.EntireColumn.Find(What:="ABC", LookAt:=xlWhole)
If Not rngDetail Is Nothing Then
rngHeader.Copy Destination:=Sheets("Sheet3").Range("D1")
rngDetail.Offset(1, 0).Resize(10, 1).Copy Destination:=Sheets("Sheet3").Range("D2")
End If
End If

Set rngDetail = Nothing
Set rngHeader = Nothing

[HaHoBe]

Hi, ManInRed,

you should add code-tags to the procedure as requested by the Forum Rules.

As I donīt know your data and what might be the issue with the code not working you should attach a workbook without sensitive data to have a look at.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Ciao,
Holger

[ManInRed]

Book1.xls

Hi Holger,

PFA, attached workbook about the problem

pls let me know, if you can download the workbook.

Thanks
MIR

[HaHoBe]

Hi, ManInRed,

in the attachment you have Sheet1 whereas the code looks for Sheet 1. I can reproduce the error and the reason might be that the code is placed behind Sheet3 whereas I would it expect it to be placed in a standard module which you would need to insert.

The code may be like

Please Login or Register  to view this content.

I placed the code in Module1 and ran it from there without problems except that you reduced the number of rows to copy to just one.

Ciao,
Holger