Copy paste range

[Marc Bell]

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

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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