Find and Paste

[Ronbo]

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]

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]

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]

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]

Dim rngFound As Range

Set rngFound =
Sheets("Sheet2").Columns("A").Find(What:=Sheets("Sheet1").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]

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("Sheet1").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.