Copy/Paste Values
 

I am entering parameters in worksheet 1 (i.e. Jones) and corresponding data
in worksheet 2 is based, through vlookup commands on these parameters. I
would like to enter code (preferably at the click of a button on worksheet 1)
to go to worksheet 2, look down Column A for the number 1 (there will only be
one) and copy paste values that data on that row only and then return back to
worksheet 1. This automation would certainly speed up this lengthy process.
Any thoughts?

Worksheet 2
0 Smith 9.98 5.67 4.35
1 Jones 6.57 3.21 8.65
0 Ranz 2.98 7.57 2.22

Copy/Paste Values
 

Why not use vlookup commands on sheet1 as well?

--
Regards,
Tom Ogilvy

"briank" wrote in message
...
I am entering parameters in worksheet 1 (i.e. Jones) and corresponding
data
in worksheet 2 is based, through vlookup commands on these parameters. I
would like to enter code (preferably at the click of a button on worksheet
1)
to go to worksheet 2, look down Column A for the number 1 (there will only
be
one) and copy paste values that data on that row only and then return back
to
worksheet 1. This automation would certainly speed up this lengthy
process.
Any thoughts?

Worksheet 2
0 Smith 9.98 5.67 4.35
1 Jones 6.57 3.21 8.65
0 Ranz 2.98 7.57 2.22

Copy/Paste Values
 

The way that sheet 1 was set up by another manager involves pivot tables and
only one choice or parameter can be entered at a time. I'm sure a better
setup was possible but at the moment I have to deal with someone else's
development. Is this possible to construct the code to allow for this
automation?

"Tom Ogilvy" wrote:

Why not use vlookup commands on sheet1 as well?

--
Regards,
Tom Ogilvy

"briank" wrote in message
...
I am entering parameters in worksheet 1 (i.e. Jones) and corresponding
data
in worksheet 2 is based, through vlookup commands on these parameters. I
would like to enter code (preferably at the click of a button on worksheet
1)
to go to worksheet 2, look down Column A for the number 1 (there will only
be
one) and copy paste values that data on that row only and then return back
to
worksheet 1. This automation would certainly speed up this lengthy
process.
Any thoughts?

Worksheet 2
0 Smith 9.98 5.67 4.35
1 Jones 6.57 3.21 8.65
0 Ranz 2.98 7.57 2.22

Copy/Paste Values
 

You say there will only be one occurance of the number and start right out
with an example that has duplicate numbers.

Assume you enter a number in A1 of sheet1

Private Sub Commandbutton1_Click()
Dim sh1 as Worksheet
Dim sh2 as Worksheet
Dim rng as Range, rng1 as Range
Dim res as Variant
Set sh1 = Worksheets(1)
Set sh2 = Worksheets(2)
set rng = sh2.Range(sh2.Cells(1,1), sh2.Cells(rows.count,1).end(xlup))
res = Application.Match(sh1.Range("A1"),rng,0)
if iserror(res) then
msgbox "Not found"
else
set rng1 = rng(res)
rng1.resize(1,20).copy Destination:=sh1.Range("B1")
end if
End Sub
--
Regards,
Tom Ogilvy

"briank" wrote in message
...
The way that sheet 1 was set up by another manager involves pivot tables
and
only one choice or parameter can be entered at a time. I'm sure a better
setup was possible but at the moment I have to deal with someone else's
development. Is this possible to construct the code to allow for this
automation?

"Tom Ogilvy" wrote:

Why not use vlookup commands on sheet1 as well?

--
Regards,
Tom Ogilvy

"briank" wrote in message
...
I am entering parameters in worksheet 1 (i.e. Jones) and corresponding
data
in worksheet 2 is based, through vlookup commands on these parameters.
I
would like to enter code (preferably at the click of a button on
worksheet
1)
to go to worksheet 2, look down Column A for the number 1 (there will
only
be
one) and copy paste values that data on that row only and then return
back
to
worksheet 1. This automation would certainly speed up this lengthy
process.
Any thoughts?

Worksheet 2
0 Smith 9.98 5.67 4.35
1 Jones 6.57 3.21 8.65
0 Ranz 2.98 7.57 2.22

Copy/Paste Values
 

Tom,
Yes, my earlier verbage was not precise. Let me clarify. I have in
worksheet(2) several rows of vlookups. Only one of these rows (in Column A)
has the number 1 (conditional statement) - all others have zero. It is this
number 1 that I would like the code to search for (i.e. row 8) and thus go to
row 8 and copy/paste values from Column A to Column P. After this, the
parameters in worksheet(1) will be changed and the start the process all over
again (roughly 100 times). Much thanks in advance for helping me in this
matter.

"Tom Ogilvy" wrote:

You say there will only be one occurance of the number and start right out
with an example that has duplicate numbers.

Assume you enter a number in A1 of sheet1

Private Sub Commandbutton1_Click()
Dim sh1 as Worksheet
Dim sh2 as Worksheet
Dim rng as Range, rng1 as Range
Dim res as Variant
Set sh1 = Worksheets(1)
Set sh2 = Worksheets(2)
set rng = sh2.Range(sh2.Cells(1,1), sh2.Cells(rows.count,1).end(xlup))
res = Application.Match(sh1.Range("A1"),rng,0)
if iserror(res) then
msgbox "Not found"
else
set rng1 = rng(res)
rng1.resize(1,20).copy Destination:=sh1.Range("B1")
end if
End Sub
--
Regards,
Tom Ogilvy

"briank" wrote in message
...
The way that sheet 1 was set up by another manager involves pivot tables
and
only one choice or parameter can be entered at a time. I'm sure a better
setup was possible but at the moment I have to deal with someone else's
development. Is this possible to construct the code to allow for this
automation?

"Tom Ogilvy" wrote:

Why not use vlookup commands on sheet1 as well?

--
Regards,
Tom Ogilvy

"briank" wrote in message
...
I am entering parameters in worksheet 1 (i.e. Jones) and corresponding
data
in worksheet 2 is based, through vlookup commands on these parameters.
I
would like to enter code (preferably at the click of a button on
worksheet
1)
to go to worksheet 2, look down Column A for the number 1 (there will
only
be
one) and copy paste values that data on that row only and then return
back
to
worksheet 1. This automation would certainly speed up this lengthy
process.
Any thoughts?

Worksheet 2
0 Smith 9.98 5.67 4.35
1 Jones 6.57 3.21 8.65
0 Ranz 2.98 7.57 2.22

Copy/Paste Values
 

Private Sub Commandbutton1_Click()
Dim sh1 as Worksheet
Dim sh2 as Worksheet
Dim rng as Range, rng1 as Range
Dim res as Variant
Set sh1 = Worksheets(1)
Set sh2 = Worksheets(2)
set rng = sh2.Range(sh2.Cells(1,1), sh2.Cells(rows.count,1).end(xlup))
res = Application.Match(sh1.Range("A1"),rng,0)
if iserror(res) then
msgbox "Not found"
else
set rng1 = rng(res)
rng1.resize(1,20).copy
sh1.cells(rows.count,"P").End(xlup)(2).PasteSpecia l xlValues
end if
End Sub



--
Regards,
Tom Ogilvy


"briank" wrote in message
...
Tom,
Yes, my earlier verbage was not precise. Let me clarify. I have in
worksheet(2) several rows of vlookups. Only one of these rows (in Column
A)
has the number 1 (conditional statement) - all others have zero. It is
this
number 1 that I would like the code to search for (i.e. row 8) and thus go
to
row 8 and copy/paste values from Column A to Column P. After this, the
parameters in worksheet(1) will be changed and the start the process all
over
again (roughly 100 times). Much thanks in advance for helping me in this
matter.

"Tom Ogilvy" wrote:

You say there will only be one occurance of the number and start right
out
with an example that has duplicate numbers.

Assume you enter a number in A1 of sheet1

Private Sub Commandbutton1_Click()
Dim sh1 as Worksheet
Dim sh2 as Worksheet
Dim rng as Range, rng1 as Range
Dim res as Variant
Set sh1 = Worksheets(1)
Set sh2 = Worksheets(2)
set rng = sh2.Range(sh2.Cells(1,1), sh2.Cells(rows.count,1).end(xlup))
res = Application.Match(sh1.Range("A1"),rng,0)
if iserror(res) then
msgbox "Not found"
else
set rng1 = rng(res)
rng1.resize(1,20).copy Destination:=sh1.Range("B1")
end if
End Sub
--
Regards,
Tom Ogilvy

"briank" wrote in message
...
The way that sheet 1 was set up by another manager involves pivot
tables
and
only one choice or parameter can be entered at a time. I'm sure a
better
setup was possible but at the moment I have to deal with someone
else's
development. Is this possible to construct the code to allow for this
automation?

"Tom Ogilvy" wrote:

Why not use vlookup commands on sheet1 as well?

--
Regards,
Tom Ogilvy

"briank" wrote in message
...
I am entering parameters in worksheet 1 (i.e. Jones) and
corresponding
data
in worksheet 2 is based, through vlookup commands on these
parameters.
I
would like to enter code (preferably at the click of a button on
worksheet
1)
to go to worksheet 2, look down Column A for the number 1 (there
will
only
be
one) and copy paste values that data on that row only and then
return
back
to
worksheet 1. This automation would certainly speed up this
lengthy
process.
Any thoughts?

Worksheet 2
0 Smith 9.98 5.67 4.35
1 Jones 6.57 3.21 8.65
0 Ranz 2.98 7.57 2.22