union range

[Curt]

have not used union before trying following not good HELP
Thanks

Union(Range(Target.Offset(0,-11,8,7,5))),target.offset(0,0).Copy _
Destination:=rngPaste
What I want to do is to copy cells -11,8,7,5 and the target cell. The target
cell is text all others are numeric. Target cell is the trigger for this.

Thanks Again

 

[Bob Phillips]

Maybe

Union(Range(Target.Offset(0,-11,8,7,5)),Target).Copy _
Destination:=rngPaste


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Curt]

wrong nbr arguments compile lites up .Offset have tried this where next?

 

[JE McGimpsey]

The VBA Offset method only takes 2 arguments (see VBA Help), so it's
hard to see what you're trying to do here, or even how many cells you're
trying to copy...

Perhaps:

With Target
Union(.Offset(0, -11), .Offset(0, 7).Resize(1, 2), _
.Offset(0, 5), .Cells).Copy _
Destination:=rngPaste
End With

????

 

[Curt]

What I want to do is to copy cells -11,8,7,5 and the target cell. The target
cell is text all others are numeric. Target cell is the trigger for this.
Does the (0 ,5), .cells).copy take in the target cell
This is what I have
Dim wksSummary As Worksheet
Dim rngPaste As Range
Set wksSummary = Sheets("Announcer")
Set rngPaste = wksSummary.Cells(65536, "A").End(xlUp).Offset(1, 0)
Application.EnableEvents = False
Set rngPaste = rngPaste.Offset(1, 0)
Union(Range(Target.Offset(0, -11, 8, 7, 5)), Range(Target)).Copy _
Destination:=rngPaste

Will try your example
Thanks Again

 

[JE McGimpsey]

But what does

cells -11,8,7,5 and the target cell

mean to you? It doesn't make sense as VBA.

Is it 5 different cells: e.g.,

(1) target
(2) target.Offset(-11, 0) or target.Offset(0, -11)
(3) target.Offset(8, 0) or target.Offset(0, 8)
(4) target.Offset(7, 0) or target.Offset(0, 7)
(5) target.Offset(5, 0) or target.Offset(0, 5)


Or is it three cells: e.g., target, target.Offset(-11, 8) and
target.Offset(7, 5)?


Or are you referring to some variant of the worksheet function: e.g.,

target and target.Offset(-11, 8).Resize(7, 5)

or something else entirely?

 

[Curt]

Try to make more clear if I can The Target cell isin Column (L)or 12 -11
being (A)
-8 being (D) -7 being(E) -5 being(G) I got idea that any cell to left of
target was a minus (-) (L)or 12 would make (K) (0,-1)(J)(0,-2) target is (0)
in count ?
If I follow your responce then I must list each cell in offset group correct?
All of you that respond have a lot to get an understanding of what another
is doing.
Thanks

 

[Bob Phillips]

Union(Target.Offset(0, -11), Target.Offset(0, 8), _
Target.Offset(0, 7), Target.Offset(0, 5), _
Target).Copy Destination:=rngPaste

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Curt]

Thanks Bob
much appreciated

Union(Target.Offset(0, -11), Target.Offset(0, 8), _
Target.Offset(0, 7), Target.Offset(0, 5), _
Target).Copy Destination:=rngPaste

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Curt]

Forgot to mention in post. It looks like that once a offset of(,0, -11) is
set the rest are then counted from this point without the minus correct.