| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find Cut and paste | Excel Discussion (Misc queries) | |||
| find and paste | Excel Programming | |||
| find and paste | Excel Programming | |||
| I need to find a macro to find data cut and paste to another colu. | Excel Programming | |||
| Paste value of Find | Excel Programming | |||
Hybrid Mode
