Copy values to multiple destinations
 

This does what I want between 2 workbooks in XL2K:

Range("RCATTND.XLS!C57:G57").Copy
Range("B4").PasteSpecial xlPasteValues
Range("G4").PasteSpecial xlPasteValues
Range("L4").PasteSpecial xlPasteValues
Range("Q4").PasteSpecial xlPasteValues
Range("V4").PasteSpecial xlPasteValues
Application.CutCopyMode = False

I would like to combine all pasting to a single step or loop, preferably
without final paste showing anything highlighted when finished.

--
David

Copy values to multiple destinations
 

Hi David,

Try:

Sub Tester03()
Dim rng As Range

Set rng = Selection
Range("RCATTND.XLS!C57:G57").Copy
Range("B4,G4,l4,q4,v4").PasteSpecial xlPasteValues
Application.CutCopyMode = False
rng.Activate

End Sub

---
Regards,
Norman



"David" wrote in message
...
This does what I want between 2 workbooks in XL2K:

Range("RCATTND.XLS!C57:G57").Copy
Range("B4").PasteSpecial xlPasteValues
Range("G4").PasteSpecial xlPasteValues
Range("L4").PasteSpecial xlPasteValues
Range("Q4").PasteSpecial xlPasteValues
Range("V4").PasteSpecial xlPasteValues
Application.CutCopyMode = False

I would like to combine all pasting to a single step or loop, preferably
without final paste showing anything highlighted when finished.

--
David

Copy values to multiple destinations
 

Try:
Range("RCATTND.XLS!C57:G57").Copy Range("b4,g4,l4,q4,v4")

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


David wrote:

This does what I want between 2 workbooks in XL2K:

Range("RCATTND.XLS!C57:G57").Copy
Range("B4").PasteSpecial xlPasteValues
Range("G4").PasteSpecial xlPasteValues
Range("L4").PasteSpecial xlPasteValues
Range("Q4").PasteSpecial xlPasteValues
Range("V4").PasteSpecial xlPasteValues
Application.CutCopyMode = False

I would like to combine all pasting to a single step or loop, preferably
without final paste showing anything highlighted when finished.

Copy values to multiple destinations
 

Hi KeepITcool,

That is certaily succinct but will it not copy more than the values?

---
Regards,
Norman



"keepITcool" wrote in message
...
Try:
Range("RCATTND.XLS!C57:G57").Copy Range("b4,g4,l4,q4,v4")

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


David wrote:

This does what I want between 2 workbooks in XL2K:

Range("RCATTND.XLS!C57:G57").Copy
Range("B4").PasteSpecial xlPasteValues
Range("G4").PasteSpecial xlPasteValues
Range("L4").PasteSpecial xlPasteValues
Range("Q4").PasteSpecial xlPasteValues
Range("V4").PasteSpecial xlPasteValues
Application.CutCopyMode = False

I would like to combine all pasting to a single step or loop, preferably
without final paste showing anything highlighted when finished.

Copy values to multiple destinations
 

If that is all the more pasting you need to do, I wouldn't bother with a
loop. As for the final selection (highlight), you could 1) get the initial
selection at the beginning and reselect that in the end in case it has
changed, or 2) just select some cell of your choosing at the end.

1)
Dim oldSelection As Range
Set oldSelection = Selection
Application.ScreenUpdating = False
'your code here
Application.Goto oldSelection
Set oldSelection = Nothing
Application.ScreenUpdating = True

2)
Application.ScreenUpdating = False
'your code here
Application.Goto Range("RCATTND.XLS!A1")
Application.ScreenUpdating = True

"David" wrote in message
...
This does what I want between 2 workbooks in XL2K:

Range("RCATTND.XLS!C57:G57").Copy
Range("B4").PasteSpecial xlPasteValues
Range("G4").PasteSpecial xlPasteValues
Range("L4").PasteSpecial xlPasteValues
Range("Q4").PasteSpecial xlPasteValues
Range("V4").PasteSpecial xlPasteValues
Application.CutCopyMode = False

I would like to combine all pasting to a single step or loop, preferably
without final paste showing anything highlighted when finished.

--
David

Copy values to multiple destinations
 

Norman.. I'd say it would :).. oops!

let's try again...

Range("RCATTND.XLS!C57:G57").Copy
Range("b4,g4,l4,q4,v4").PasteSpecial xlValues
Application.CutCopyMode = False

or to avoid using the clipboard...

Sub Oops()
Dim rArea
With Range("RCATTND.XLS!C57:G57")
For Each rArea In Range("b4,g4,l4,q4,v4").Areas
rArea.Resize(.Rows.Count, .Columns.Count).Value2 = .Value2
Next
End With
End Sub


Cheerz!

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Norman Jones" wrote:

Hi KeepITcool,

That is certaily succinct but will it not copy more than the values?

---
Regards,
Norman



"keepITcool" wrote in message
...
Try:
Range("RCATTND.XLS!C57:G57").Copy Range("b4,g4,l4,q4,v4")

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


David wrote:

This does what I want between 2 workbooks in XL2K:

Range("RCATTND.XLS!C57:G57").Copy
Range("B4").PasteSpecial xlPasteValues
Range("G4").PasteSpecial xlPasteValues
Range("L4").PasteSpecial xlPasteValues
Range("Q4").PasteSpecial xlPasteValues
Range("V4").PasteSpecial xlPasteValues
Application.CutCopyMode = False

I would like to combine all pasting to a single step or loop,
preferably without final paste showing anything highlighted when
finished.

Copy values to multiple destinations
 

Norman Jones wrote



Try:

<snip

Range("B4,G4,l4,q4,v4").PasteSpecial xlPasteValues

Had tried that prior to posting, but got
'Runtime error 1004 This operation requires the merged cells to be
identically sized'

--
David

Copy values to multiple destinations
 

Norman Jones wrote

That is certaily succinct but will it not copy more than the values?

It does indeed. And the copy range is comprised of formulas.

--
David

Copy values to multiple destinations
 

Bob Kilmer wrote

If that is all the more pasting you need to do, I wouldn't bother with
a loop. As for the final selection (highlight), you could 1) get the
initial selection at the beginning and reselect that in the end in
case it has changed, or 2) just select some cell of your choosing at
the end.

Thanks for the input. Highlight is not that critical since my routine
activates another cell after the paste anyway. That part of my query was
academic.

---
David

Copy values to multiple destinations
 

keepITcool wrote

With Range("RCATTND.XLS!C57:G57")
For Each rArea In Range("b4,g4,l4,q4,v4").Areas
rArea.Resize(.Rows.Count, .Columns.Count).Value2 = .Value2
Next
End With

Unless someone comes up with a one-liner, I went with this one, the first
bombing as mentioned in my followup to Norman

Thanks

--
David