Reg:Trying to write a macro in excel

[anshu minocha]

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

 

[Otto Moehrbach]

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]

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 sheet1.
The data to be moved/copied from sheets 2 & 3 must match the column headers
in sheet 1.











- 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,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 nameroject ID
A2:contains the value:2345

B1:has column name hase
B2:has value1

now cells C1,D1 and E1 are merged have column
nameroject 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 are:
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

 

[Otto Moehrbach]

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]

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 understandyou 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 knowthis
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



















- 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

 

[anshu minocha]

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

 

[Otto Moehrbach]

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
(e-mail address removed). Remove the "extra" from this email. Otto