ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Find and Paste (https://www.excelbanter.com/excel-programming/357697-find-paste.html)

Ronbo
Find and Paste
 
I am trying to copy a range (B1..B12) from Sheet1 to a row on Sheet2. The
row to paste to is based upon the value in Sheet1 (A1) which is 10.

So far I have;

Sheets("Sheet1").Select
If Range("D15") < Range("D17") Then
Range("b1..b12").Select
Selection.Copy
Sheets("sheet2").Select

At this point I need for it to find which row in column (A) has 10 in it and
then go over 3 columns to C and paste.

Thanks a lot for any help.

Jim Thomlinson
Find and Paste
 
Give this a try...

Dim rngFound As Range

Set rngFound = Sheets("Sheet2").Columns("A").Find(What:=10, _
LookAt:=xlWhole, _
LookIn:=xlValues)
If rngFound Is Nothing Then
MsgBox "Sorry. Not found..."
Else
With Sheets("Sheet1")
If .Range("D15") < .Range("D17") Then _
.Range("B1:B12").Copy rngFound.Offset(0, 3)
End With
End If
--
HTH...

Jim Thomlinson


"Ronbo" wrote:

I am trying to copy a range (B1..B12) from Sheet1 to a row on Sheet2. The
row to paste to is based upon the value in Sheet1 (A1) which is 10.

So far I have;

Sheets("Sheet1").Select
If Range("D15") < Range("D17") Then
Range("b1..b12").Select
Selection.Copy
Sheets("sheet2").Select

At this point I need for it to find which row in column (A) has 10 in it and
then go over 3 columns to C and paste.

Thanks a lot for any help.

Ronbo
Find and Paste
 
Thanks Jim for the help. Its exactly what I am looking for. For some reason
it was putting the data in row 11, but by changing the offset to -1 it puts
it in the correct row.

Also, I need to change the What:=10 to What:=Sheet1!A1. What is the correct
syntax for such?





"Jim Thomlinson" wrote:

Give this a try...

Dim rngFound As Range

Set rngFound = Sheets("Sheet2").Columns("A").Find(What:=10, _
LookAt:=xlWhole, _
LookIn:=xlValues)
If rngFound Is Nothing Then
MsgBox "Sorry. Not found..."
Else
With Sheets("Sheet1")
If .Range("D15") < .Range("D17") Then _
.Range("B1:B12").Copy rngFound.Offset(0, 3)
End With
End If
--
HTH...

Jim Thomlinson


"Ronbo" wrote:

I am trying to copy a range (B1..B12) from Sheet1 to a row on Sheet2. The
row to paste to is based upon the value in Sheet1 (A1) which is 10.

So far I have;

Sheets("Sheet1").Select
If Range("D15") < Range("D17") Then
Range("b1..b12").Select
Selection.Copy
Sheets("sheet2").Select

At this point I need for it to find which row in column (A) has 10 in it and
then go over 3 columns to C and paste.

Thanks a lot for any help.

Ronbo
Find and Paste
 
Also, how can I get it to paste values? It is pasting formulas.
again Thanks

"Ronbo" wrote:

Thanks Jim for the help. Its exactly what I am looking for. For some reason
it was putting the data in row 11, but by changing the offset to -1 it puts
it in the correct row.

Also, I need to change the What:=10 to What:=Sheet1!A1. What is the correct
syntax for such?





"Jim Thomlinson" wrote:

Give this a try...

Dim rngFound As Range

Set rngFound = Sheets("Sheet2").Columns("A").Find(What:=10, _
LookAt:=xlWhole, _
LookIn:=xlValues)
If rngFound Is Nothing Then
MsgBox "Sorry. Not found..."
Else
With Sheets("Sheet1")
If .Range("D15") < .Range("D17") Then _
.Range("B1:B12").Copy rngFound.Offset(0, 3)
End With
End If
--
HTH...

Jim Thomlinson


"Ronbo" wrote:

I am trying to copy a range (B1..B12) from Sheet1 to a row on Sheet2. The
row to paste to is based upon the value in Sheet1 (A1) which is 10.

So far I have;

Sheets("Sheet1").Select
If Range("D15") < Range("D17") Then
Range("b1..b12").Select
Selection.Copy
Sheets("sheet2").Select

At this point I need for it to find which row in column (A) has 10 in it and
then go over 3 columns to C and paste.

Thanks a lot for any help.

Jim Thomlinson
Find and Paste
 
Dim rngFound As Range

Set rngFound =
Sheets("Sheet2").Columns("A").Find(What:=Sheets("S heet1").Range("A1").Value, _
LookAt:=xlWhole, _
LookIn:=xlValues)
If rngFound Is Nothing Then
MsgBox "Sorry. Not found..."
Else
With Sheets("Sheet1")
If .Range("D15") < .Range("D17") Then _
.Range("B1:B12").Copy
rngFound.Offset(0, 3).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End With
End If
--
HTH...

Jim Thomlinson


"Ronbo" wrote:

Also, how can I get it to paste values? It is pasting formulas.
again Thanks

"Ronbo" wrote:

Thanks Jim for the help. Its exactly what I am looking for. For some reason
it was putting the data in row 11, but by changing the offset to -1 it puts
it in the correct row.

Also, I need to change the What:=10 to What:=Sheet1!A1. What is the correct
syntax for such?





"Jim Thomlinson" wrote:

Give this a try...

Dim rngFound As Range

Set rngFound = Sheets("Sheet2").Columns("A").Find(What:=10, _
LookAt:=xlWhole, _
LookIn:=xlValues)
If rngFound Is Nothing Then
MsgBox "Sorry. Not found..."
Else
With Sheets("Sheet1")
If .Range("D15") < .Range("D17") Then _
.Range("B1:B12").Copy rngFound.Offset(0, 3)
End With
End If
--
HTH...

Jim Thomlinson


"Ronbo" wrote:

I am trying to copy a range (B1..B12) from Sheet1 to a row on Sheet2. The
row to paste to is based upon the value in Sheet1 (A1) which is 10.

So far I have;

Sheets("Sheet1").Select
If Range("D15") < Range("D17") Then
Range("b1..b12").Select
Selection.Copy
Sheets("sheet2").Select

At this point I need for it to find which row in column (A) has 10 in it and
then go over 3 columns to C and paste.

Thanks a lot for any help.

Ronbo
Find and Paste
 
Thanks A LOT for your time and expertise. Its PERFECT.



"Jim Thomlinson" wrote:

Dim rngFound As Range

Set rngFound =
Sheets("Sheet2").Columns("A").Find(What:=Sheets("S heet1").Range("A1").Value, _
LookAt:=xlWhole, _
LookIn:=xlValues)
If rngFound Is Nothing Then
MsgBox "Sorry. Not found..."
Else
With Sheets("Sheet1")
If .Range("D15") < .Range("D17") Then _
.Range("B1:B12").Copy
rngFound.Offset(0, 3).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End With
End If
--
HTH...

Jim Thomlinson


"Ronbo" wrote:

Also, how can I get it to paste values? It is pasting formulas.
again Thanks

"Ronbo" wrote:

Thanks Jim for the help. Its exactly what I am looking for. For some reason
it was putting the data in row 11, but by changing the offset to -1 it puts
it in the correct row.

Also, I need to change the What:=10 to What:=Sheet1!A1. What is the correct
syntax for such?





"Jim Thomlinson" wrote:

Give this a try...

Dim rngFound As Range

Set rngFound = Sheets("Sheet2").Columns("A").Find(What:=10, _
LookAt:=xlWhole, _
LookIn:=xlValues)
If rngFound Is Nothing Then
MsgBox "Sorry. Not found..."
Else
With Sheets("Sheet1")
If .Range("D15") < .Range("D17") Then _
.Range("B1:B12").Copy rngFound.Offset(0, 3)
End With
End If
--
HTH...

Jim Thomlinson


"Ronbo" wrote:

I am trying to copy a range (B1..B12) from Sheet1 to a row on Sheet2. The
row to paste to is based upon the value in Sheet1 (A1) which is 10.

So far I have;

Sheets("Sheet1").Select
If Range("D15") < Range("D17") Then
Range("b1..b12").Select
Selection.Copy
Sheets("sheet2").Select

At this point I need for it to find which row in column (A) has 10 in it and
then go over 3 columns to C and paste.

Thanks a lot for any help.


All times are GMT +1. The time now is 09:10 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com