On Jan 18, 6:07*pm, "Otto Moehrbach"
wrote:
Anshu
* * * * I think I understand what you have and want. *First. let me say this about
merged cells. *Don't ever use merged cells. *Ever. *For any reason. *Excel
does not do merged cells well and they will give you gray hair.
* * * * If you want C1, D1, E1 to look like merged cells, do this.. *Make your
entry, like Project Manager, in C1. *Then select C1, D1, E1, all at one
time. *Now do Format - Cells - Alignment - Horizontal, and select "Center
across selection". *Click OK. *That's it. *The appearance is the same and no
cells are merged.
* * * * The following macro will do what you want if I understand you correctly.
Paste it into a regular module. *Insert a button in sheet 1 and assign macro
"ExtractData" to it. *Do all this on a copy of your file until you know this
macro will do what you want. * HTH *Otto
Sub ExtractData()
* * Dim rColA3 As Range, rColA2 As Range
* * Dim rFirst As Range, rLast As Range
* * Dim TheRow As Long

* * 'Copy from sheet 3
* * With Sheets("3")
* * * * Set rColA3 = .Range("A17", .Range("A" & Rows.Count).End(xlUp))
* * * * rColA3.Resize(, 4).Copy Range("A17")
* * End With

* * 'Copy from sheet 2
* * With Sheets("2")
* * * * Set rColA2 = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
* * * * rColA2.Resize(, 6).Sort Key1:=.Range("A2"), Order1:=xlAscending,
Header:=xlNo
* * * * Set rFirst = rColA2.Find(What:=Range("A2"),
After:=rColA2(rColA2.Count), _
* * * * * * LookAt:=xlWhole, SearchOrder:=xlByColumns,
SearchDirection:=xlNext)
* * * * Set rLast = rColA2.Find(What:=Range("A2"), After:=rColA2(1), _
* * * * * * LookAt:=xlWhole, SearchOrder:=xlByColumns,
SearchDirection:=xlPrevious)
* * * * TheRow = .Range(rFirst, rLast).Offset(, 1).Find(What:=Range("B2"), _
* * * * * * LookAt:=xlWhole, SearchOrder:=xlByColumns,
SearchDirection:=xlNext).Row
* * * * Range("C2") = .Cells(TheRow, 3)
* * End With
End Sub

"anshu minocha" wrote in message

...



On Jan 18, 9:44 am, "Otto Moehrbach"
wrote:
Anshu
* * It's very difficult to understand what you have and what you want.
Let
me reword this and you tell me if I have it right.
You have 3 sheets, 1, 2, and 3.
You want data placed into sheet 1 from both sheets 2 & 3.
Sheet 1 has column headers, I presume in row 1.
The column headers in sheet 1 are the same as those in the other sheets
but
sheet 1 doesn't have all the column headers as the other sheets.
I gather that sheets 2 & 3 do not have common column headers except for
the
SubProject ID.
All 3 sheets have a column header "SubProject ID".
Sheet 1 has a list of IDs in the SubProject ID column.
You want data moved when you click a button in sheet 1.
The data to be moved/copied from sheets 2 & 3 must match the IDs in sheet
1.
The data to be moved/copied from sheets 2 & 3 must match the column
headers
in sheet 1.
Is this right? *Otto"anshu minocha" wrote in
message

....

Hi all,

* * * *There are 3 sheets in a single xl file:

Sheet 1: It contains the template to be populated with the information
from the other sheets
* * * * * * It contains only selected columns from sheet 2 and sheet
3 whose values need to be populated in sheet 1.

PART 1: Sheet 3: contains 3rd column as "subproject id":
* * * * * * * * * * * * * and has different columns containing the
subproject details
* * * * * * Aim of code:
* * * * * *1. extract all the subproject ids into sheet1:from sheet
3 *in the defined range of cells in sheet1 under the
* * * * * *same column heading "subproject id"
* * * * * *2. And then pull the *information for all those subproject
ids (the information requires to be pulled only
* * * * * * * *from the selected columns in sheet 3 which are given
the "same column name" in sheet 1)

PART 2: Sheet 2 contains the "Project id" column name and several
other columns containg information of *the
* * * * * * * * * * * * *project pertaining to that project id.
* * * * * *Aim of the code: User enters the project id in template 1
* * * * * * * * * * * * * * * * * * and the POPULATE button should
populate the information in various cells
* * * * * * * * * * * * * * * * * * (In sheet 1:like cell A3:
contains name "Project Status"
* * * * * * * * * * * * * * * * * * * * * * * * * * cell A4: should
extract the value of the Project status from sheet 2 under the
* * * * * * * * * * * * * * * * * * * * * * * * * * column name
"Project status".
* * * * * * * * * * * * * * * * * * And similarly this repeats for a
couple of fileds)

Any help would be appreciated.

Thanks- Hide quoted text -

- Show quoted text -

Hello Otto,

* * * * * * * Let me reframe my explaination with details in response
to your reply:
You have 3 sheets, 1, 2, and 3.
Yes I want data placed into sheet 1 from both sheets 2 & 3.
Yes Sheet 1 has column headers,

* *** But they are not in row 1:
* For eg:Row 16th of sheet 1 has column names: subproject id, Title,
status, Description
* * * * * * and these column names are also present in sheet 3
* * * * * * so I want data extracted(only for the columns seen in
sheet1) for all the subproject ids from sheet3
*This describes the part of data extraction from sheet 3***

Yes,The column headers in sheet 1 are the same as those in the other
sheets
* *but sheet 1 doesn't have all the column headers as the other
sheets.
Yes, sheets 2 & 3 do not have any common column headers not even the
* SubProject ID.

* ***But they have no column header in common***
* ***My initial rows of sheet1: row 1:16 need to extract data from
sheet2
* * * and rows 16 onwards extract data from sheet 3****

Yes,Sheet 1(row 16 onwards) automatically should show the *a list of all
the IDs in the SubProject ID column present in sheet 3
Yes,I want data moved when I click a button in sheet 1 to populate all
the subproject details in sheet1.

This explains one part of my problem i.e extracting data from sheet 3
and populating it in sheet 1 from row 16 onwards

Now Rows 1 to 15 has a template kind of thing:
which requires specific cells to extract values from the sheet 2.

for eg:In sheet 1: cell A1 has column name:Project ID
* * * * * * * *A2:contains the value:2345

* * * * * * * B1:has column name :Phase
* * * * * * * B2:has value1

* * * * * * *now cells C1,D1 and E1 are merged have column
name:Project manager
* * * * * * *cell C2,D2 and E2 should extract the value from sheet 2
for that particular phase 1

* * * * * * *my template has here specific cells which will extract
the value of Project manager for phase 1 for project 2345

* * * * * * ***There is no different project id in sheet 2 but have
different phases:
*eg:Records in sheet 2 a
* * Project id * *Phase * Project Manager *Project Status
* * 2345 * * * * * 1 * * * * * AM * * * * * * * * * * completed
* * 2345 * * * * * 2 * * * * * VM * * * * * * * * * * incomplete
* * 2345 * * * * * 3......
****

And my sheet 1 will extract information for only 1 phase for that
project from sheet 2

Please let me know if this appears to be clear.I really appreciate
your help
Thanks- Hide quoted text -

- Show quoted text


Thanks Otto....I'm not sure whether you received my first email
message
But I was able to automate one part and I need to ask just one more
question

my sheet 3: "Subproject Details" contains

column names:
Row1 WR# Phase SP# Details Priority Manager
Status
Row2 12345 1-0110 N80 abc 1
am Test
12345 1-0210 N90 def 2
bm Complete
12345 2-0210 N98 ghi 1
cm Test
12345 1-0110 N76 jkl 2
dm Test
12345 1-0110 N65 nop 1
em Test

Now sheet3 is "Mytemplate"
the above 15 rows have some data from sheet 2

now Row 16 column names : WR# Phase SP# Manager Status
Row 17 has 2 values: 60625 1-0110

The above 2 values for WR# and phase are inserted by the user
Now on clicking the button:

my O/p should be:

Row 16: WR# Phase SP# Manager Status
Row 17: 12345 1-0110 N80 am Test
N76 dm Test
N75 em Test

so can a macro pull all the records for WR#12345 and phase 1-0110 from
sheet3 and populate in sheet1 for SP#, Manager and Status:

your help would be greatly appreciated
Thanks