Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Possible? Copy/paste to&from moving sources/destinations | Excel Discussion (Misc queries) | |||
Lookup and copy multiple values | Excel Worksheet Functions | |||
Copy Down With Multiple Values | Excel Discussion (Misc queries) | |||
are multiple Hyperlink destinations, in one cell possible | Excel Discussion (Misc queries) | |||
Copy rows to different worksheet destinations | Excel Programming |