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

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" wrote:

> 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

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

"joel" wrote:

> 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" wrote:
>
> > 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
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" wrote:

> 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" wrote:
>
> > 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

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)



"BrianW" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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