Copy values to multiple destinations

[David]

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.

 

[Norman Jones]

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

 

[keepITcool]

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 >

 

[Norman Jones]

Hi KeepITcool,

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

 

[Bob Kilmer]

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

 

[keepITcool]

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 >

 

[David]

Norman Jones wrote

Try:

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]

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]

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]

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