Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy paste range
Hello,
I've been trying for hours trying to get this right but i can't. I have data in excel in the range v2 to ac25000. If a cell in v = "unique", then I want data to be copied and pasted into a sheet called journal starting at a blank line at A9 and incremently rising. The code is a bit all over the shop but I just can't get the copy destination part to work. Plemy forease help as head is bleeding from the amount of times I've banged it against the wall. Thanks Marc Here is the code:- Sub wowwee() Dim useme As Range Dim cellw As Integer Dim cellx As Long Dim celly As Long Dim cellz As Variant Dim cellaa As Variant Dim cellab As Currency Dim cellac As Variant Sheets("ANLC").Select Range("v2:v25000").Select For Each rngcell In Selection If rngcell.FormulaR1C1 = "unique" Then cellw = "2200" cellx = rngcell.Offset(0, 2) celly = rngcell.Offset(0, 3) cellz = "" cellaa = "" cellab = rngcell.Offset(0, 6) cellac = "" If Sheets("journal").Range("A9") = "" Then Set Target = Sheets("journal").Range("A9") ElseIf Sheets("journal").Range("A9") < "" And Sheets("journal").Range("A10") = "" Then Set Target = Sheets("journal").Range("A10") ElseIf Sheets("journal").Range("A9") < "" And Sheets("journal").Range("A10") < "" Then Set Target = Sheets("journal").Range("A9").End(xlDown).Offset(1 , 0) End If 'cant get this bit to work Range(cellw, cellx, celly, cellz, cella, cllab, cellac).Copy Destination:=Target Application.CutCopyMode = False End If Next rngcell End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy paste range
Marc,
Difficult to see what you are doing here. Apart from 2 typos in the offending statement, you are trying to pass longs to a range statement. the range can look like Range("A1,B10,C22").Copy or so, but not Range(2200, 5,6,9).Copy it just doesn't make sense. What range (in Excel not VBA speak) would you be expecting to copy. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Marc Bell" wrote in message om... Hello, I've been trying for hours trying to get this right but i can't. I have data in excel in the range v2 to ac25000. If a cell in v = "unique", then I want data to be copied and pasted into a sheet called journal starting at a blank line at A9 and incremently rising. The code is a bit all over the shop but I just can't get the copy destination part to work. Plemy forease help as head is bleeding from the amount of times I've banged it against the wall. Thanks Marc Here is the code:- Sub wowwee() Dim useme As Range Dim cellw As Integer Dim cellx As Long Dim celly As Long Dim cellz As Variant Dim cellaa As Variant Dim cellab As Currency Dim cellac As Variant Sheets("ANLC").Select Range("v2:v25000").Select For Each rngcell In Selection If rngcell.FormulaR1C1 = "unique" Then cellw = "2200" cellx = rngcell.Offset(0, 2) celly = rngcell.Offset(0, 3) cellz = "" cellaa = "" cellab = rngcell.Offset(0, 6) cellac = "" If Sheets("journal").Range("A9") = "" Then Set Target = Sheets("journal").Range("A9") ElseIf Sheets("journal").Range("A9") < "" And Sheets("journal").Range("A10") = "" Then Set Target = Sheets("journal").Range("A10") ElseIf Sheets("journal").Range("A9") < "" And Sheets("journal").Range("A10") < "" Then Set Target = Sheets("journal").Range("A9").End(xlDown).Offset(1 , 0) End If 'cant get this bit to work Range(cellw, cellx, celly, cellz, cella, cllab, cellac).Copy Destination:=Target Application.CutCopyMode = False End If Next rngcell End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy paste range
An easy way to do what you describe is to use an autofilter to identify the
rows to copy: Sub Macro1() Dim rng as Range, rng1 as Range Sheets("ANLC").Select Set rng = Range("V1:AC25000") rng.AutoFilter Field:=1, Criteria1:="unique" Set rng1 = ActiveSheet.AutoFilter.Range Set rng1 = rng.Offset(1, 0).Resize(rng.Rows.Count - 1) rng1.Copy Destination:=Sheets("journal").Range("A9") ActiveSheet.AutoFilterMode = False End Sub -- Regards, Tom Ogilvy "Marc Bell" wrote in message om... Hello, I've been trying for hours trying to get this right but i can't. I have data in excel in the range v2 to ac25000. If a cell in v = "unique", then I want data to be copied and pasted into a sheet called journal starting at a blank line at A9 and incremently rising. The code is a bit all over the shop but I just can't get the copy destination part to work. Plemy forease help as head is bleeding from the amount of times I've banged it against the wall. Thanks Marc Here is the code:- Sub wowwee() Dim useme As Range Dim cellw As Integer Dim cellx As Long Dim celly As Long Dim cellz As Variant Dim cellaa As Variant Dim cellab As Currency Dim cellac As Variant Sheets("ANLC").Select Range("v2:v25000").Select For Each rngcell In Selection If rngcell.FormulaR1C1 = "unique" Then cellw = "2200" cellx = rngcell.Offset(0, 2) celly = rngcell.Offset(0, 3) cellz = "" cellaa = "" cellab = rngcell.Offset(0, 6) cellac = "" If Sheets("journal").Range("A9") = "" Then Set Target = Sheets("journal").Range("A9") ElseIf Sheets("journal").Range("A9") < "" And Sheets("journal").Range("A10") = "" Then Set Target = Sheets("journal").Range("A10") ElseIf Sheets("journal").Range("A9") < "" And Sheets("journal").Range("A10") < "" Then Set Target = Sheets("journal").Range("A9").End(xlDown).Offset(1 , 0) End If 'cant get this bit to work Range(cellw, cellx, celly, cellz, cella, cllab, cellac).Copy Destination:=Target Application.CutCopyMode = False End If Next rngcell End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy & paste range | Excel Discussion (Misc queries) | |||
Copy range and paste as picture | Excel Discussion (Misc queries) | |||
VBA Code- Copy & Paste in Blank Range | Excel Discussion (Misc queries) | |||
copy range and paste into every 3rd cell of new range | New Users to Excel | |||
copy / paste values for certain range | Excel Programming |