Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy range using
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy range using
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy range using
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy range using
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy range using
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
RANGE EXCEL copy cell that meets criteria in a range | Excel Worksheet Functions | |||
copy formulas from a contiguous range to a safe place and copy them back later | Excel Programming | |||
Create/copy combo boxes in one range if condition is met in a different range | Excel Programming | |||
Code to copy range vs Copy Entire Worksheet - can't figure it out | Excel Programming | |||
Range COPY function - how to copy VALUES and not formulas | Excel Programming |