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

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

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

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

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

On Jan 19, 11:53*am, anshu minocha wrote:
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- Hide quoted text -

- Show quoted text -

Please note in the above quote due to typo the values of Manager and
status have moved to the next line while describing sheet3: subproject
details

Anshu
Yes, the code can do all that, but I'm not clear on what you want. Your
latest post gave me 2 names for your sheet 3. If you wish, send me your
file or a sample of your file. Fake the data as you wish. I need just the
layout of everything. Include a clear explanation of what you want moved to
where and based on what inputs from the user. Remember that you are talking
to someone who knows nothing about your business. My email is
. Remove the "extra" from this email. Otto

"anshu minocha" wrote in message
...
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