copy range using

[BrianW]

In the code below I want cell A3272 in Range("A10:A3272") to be determined
by a cell value of 3 in column L in Sheet(RSum). This cell with value 3 is
constantly moving as rows are to Sheets(RSum). Range "A9:L9" has formulas
linked directly to cells in Sheet(RSUM) so I want to copy down to the value
of 3.
Just to mention there is data after (RSum) A3272 that I dont want showing in
Sheets("Print").

Application.CutCopyMode = False
Sheets("Print").Select
Range("A9:L9").Select
Selection.Copy
Range("A10:A3272").Select
ActiveSheet.Paste
Range("B9").Select
Application.CutCopyMode = True
End Sub

 

[joel]

with sheets("RSum)
set c = .Columns("C").find(What:=3, _
lookin:=xlvalues,lookat:=xlwhole)
if c is nothing then
msgbox("Cannot find the value 3")
else
LastRow = c.row
Sheets("Print").Range("A9:L9").Copy
Destination:= .Range("A10:A" & LastRow)
end with

 

[BrianW]

Hi Joel
Thank you for your reply
I should of mentioned I'm a real novice at this. When I replace my code with
yours the first row - with sheets("RSum) and Destination:= .Range("A10:A" &
LastRow) change color to red. Do I need to add more code?

Cheers

 

[BrianW]

Joel
I have managed to get the code working


With Sheets("RSum")
Set c = .Columns("O").Find(What:=3, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find the value 3")
Else
LastRow = c.Row
Sheets("Print").Range("A9:L9").Copy
Destination = .Range("A10:A" & LastRow)

' Application.CutCopyMode = False
' Sheets("Print").Select
' Range("A9:L9").Select
' Selection.Copy
' Range("A10:A3272").Select
' ActiveSheet.Paste
Range("B9").Select
' Application.CutCopyMode = True
End If
End With
End Sub
End Sub

However works to selecting Sheets("Print").Range("A9:L9") but doesn't copy
down

Cheers bw

 

[joel]

There are to w posible causes

1) LastRow isn't being set properly. Add a message box

msgbox(LastRow)

I don't think this is the reason.

2) Your Ranges are wrong

Sheets("Print").Range("A9:L9").Copy
Destination = .Range("A10:A" & LastRow)

Do you really want this? I just copied your code.

Sheets("Print").Range("A9:L9").Copy
Destination = .Range("A" & LastRow & :L" & LastRow)

or this

Sheets("Print").Range("A9").Copy
Destination = .Range("A10:A" & LastRow)