Excel, Copy and paste special values without switching sheets

Copy and paste special values without switching sheets

Author	Message
Simo #1 / 3	Copy and paste special values without switching sheets Hi, I have a very simple program that copies some data from one column to another and pastes the values only (paste special). When the macro is run i dont want it to go from one sheet to another sheet.(eg sheets ("positions").select). I want it to run in the background if I am in another sheet and not switch sheets. I have pasted it below. The third line doesnt work? Any suggestions. Thanks. Public Sub Copy2() Sheets("Positions").Range("I8:I200").Copy Sheets("Positions").Range("al8").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False End Sub
Fri, 27 Feb 2004 20:16:35 GMT

Mike Muster #2 / 3	Copy and paste special values without switching sheets Quote: >Hi, I have a very simple program that copies some data >from one column to another and pastes the values only >(paste special). When the macro is run i dont want it to >go from one sheet to another sheet.(eg sheets >("positions").select). I want it to run in the background >if I am in another sheet and not switch sheets. I have >pasted it below. The third line doesnt work? Any >suggestions. Thanks. >Public Sub Copy2() > Sheets("Positions").Range("I8:I200").Copy >Sheets("Positions").Range("al8").Select > Selection.PasteSpecial Paste:=xlValues, >Operation:=xlNone, SkipBlanks:= _ > False, Transpose:=False > End Sub Try: Public Sub Copy2() Sheets("Positions").Range("I8:I200").Copy Sheets("Positions").Range("al8").PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False End Sub This works like a charm in XL2000 However this does not (seem to) work in XL97; that requires the sheet to be active to have anything done with it.
Fri, 27 Feb 2004 20:42:50 GMT

Tom Ogilv #3 / 3	Copy and paste special values without switching sheets You can try something like this: Sub CopyRange() Set rng = Selection Application.ScreenUpdating = False Sheets("Positions").Range("I8:I200").Formula = "=Trunc(Rand()*100+1)" Sheets("Positions").Range("I8:I200").Copy With Sheets("Positions").Range("al8") .PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False End With Application.CutCopyMode = xlCopy rng.Parent.Activate rng.Select End Sub Regards, Tom Ogilvy Quote: > Hi, I have a very simple program that copies some data > from one column to another and pastes the values only > (paste special). When the macro is run i dont want it to > go from one sheet to another sheet.(eg sheets > ("positions").select). I want it to run in the background > if I am in another sheet and not switch sheets. I have > pasted it below. The third line doesnt work? Any > suggestions. Thanks. > Public Sub Copy2() > Sheets("Positions").Range("I8:I200").Copy > Sheets("Positions").Range("al8").Select > Selection.PasteSpecial Paste:=xlValues, > Operation:=xlNone, SkipBlanks:= _ > False, Transpose:=False > End Sub
Fri, 27 Feb 2004 21:29:03 GMT

Page 1 of 1

[ 3 post ]