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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 04:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com