Copying a range to clipboard
The line I added does not change the selection.
You would need to add
rng.Select
after the assignment, however, your
Range("B4").Select does change the selection, so I don't see how any cells
in Column T stay selected (if that is what you mean by antialiased - but
maybe you mean it has the marching ants [marquee] around it)
Sub Send2Destination()
ActiveSheet.Unprotect
Application.ScreenUpdating = False
Application.Goto Reference:="R9C16:R110C16" 'P9
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Application.Goto Reference:="R9C20:R110C20" 'T9
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:= _
True, Transpose:=False
Set rng = Range(Range("T9"), Range("T111").End(xlUp))
rng.Copy
End Sub
for this to work
Set rng = Range(Range("T9"), Range("T111").End(xlUp))
then the cells you don't want to select must be empty.
you could try this more cumbersome approach
Sub Send2Destination()
ActiveSheet.Unprotect
Application.ScreenUpdating = False
Application.Goto Reference:="R9C16:R110C16" 'P9
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Application.Goto Reference:="R9C20:R110C20" 'T9
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, _
SkipBlanks:= _
True, Transpose:=False
set rng = Range("T9")
for each cell in Range("T10:T111"))
if len(trim(cell.Value)) 0 then
set rng = Union(rng,cell)
else
exit for
end if
Next
rng.select
rng.Copy
End Sub
--
Regards,
Tom Ogilvy
"Richard" wrote in message
...
Hi Tom,
Fixed the error in the code as you pointed out and no
crashes.
Still no luck though. I've posted my code again which you
might have a look at for me. This is driving me nuts.
The code still leaves ALL the range T9:T110 anti-aliased
which means when I post it elsewhere ALL that range is
posted. This generally means that up to 70-80 cells are
transposed to my destination even though they are blank. I
just want to select only those cells from T9 down that
have data in them. If 30, I only want T9:T30 not the whole
range.
Do you think in view of the difficulty that this may not
be achievable.
This is my actual code:-
Sub Send2Destination()
ActiveSheet.Unprotect
Application.ScreenUpdating = False
Application.Goto Reference:="R9C16:R110C16" 'P9
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Application.Goto Reference:="R9C20:R110C20" 'T9
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, _
SkipBlanks:= _
True, Transpose:=False
Set rng = Range(Range("T9"), Range("T111").End(xlUp))
rng.Copy
Range("B4").Select
End Sub
-----Original Message-----
Set rng = Range(Range("F2"), Range("F10").End(x1up))
your problem is you are using a constant x1up and it
should be xlup using
the letter "L" rather than the number 1 as you are doing.
--
Regards,
Tom Ogilvy
"Richard" wrote in message
...
Hi Tom,
I've now had a chance to try a condensed version on an
empty sheet and it still crashes at the same line with
the
same message.
This is what I've done.
A B D F H
Data Formulas Extracted asText Sorted Text Pasted Text
F2:F5 H2:H5
Blue Blue Blue Blue Blue
Red Blue Blue
Blue Blue Blue Blue Blue
Green Blue Blue
Blue Blue Blue
Blue Blue Blue
Red
Red
Green
Formula =if(A2="Blue","Blue","")
What I want is just F2:F5 (NOT F2:F10) so I can paste to
H2:H5 (excludes any blanks).
Can't see why it won't run properly.
If you get a chance could you have another look at it.
Thanks,
Richard
Sub TestToCopy()
ActiveSheet.Unprotect
Application.ScreenUpdating = False
Application.Goto Reference:="R2C3:R10C3"
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Application.Goto Reference:="R2C6:R10C6"
Selection.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
Set rng = Range(Range("F2"), Range("F10").End(x1up))
'CRASHES
HERE. 1004 Application or Object defined error.
rng.Copy
Range("A1").Select
End Sub
-----Original Message-----
I can't reproduce it - even on an empty spreadsheet:
set rng = Range(Range("T9"),Range("T111").End(xlup))
? rng.Address
$T$5:$T$9
--
Regards,
Tom Ogilvy
Richard wrote in message
...
Hello Tom,
Just tried the code but a bit of a problem. Crashes
at
line commencing "set rng". Error Msg 1004,
Application-
defined or Object-defined error.
Range P9:P100 is anti-aliased and Range T9:T100 is
highlighted when it crashes.
Any clues?
Thanks,
Richard
-----Original Message-----
Application.ScreenUpdating = False
Application.Goto
Reference:="R9C16:R110C16" 'P9
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Application.Goto
Reference:="R9C20:R110C20" 'T9
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
set rng = Range(Range("T9"),Range("T111").End
(xlup))
rng.copy
Range("B4").Select
Application.ScreenUpdating = True
End Sub
--
Regards,
Tom Ogilvy
"Richard" wrote in message
...
Sub Send2Application()
Could someone lend a hand here please.
I'm copying the range P9:P110 to T9
The range will include some cells with nothing in
them
that is:- no formulas or text.
Then I want to extract from T9:T110 JUST the cells
that
have data. (It will be text data)
These cells are then copied to the clipboard for
pasting
into another application.
I keep getting many blank cells at the bottom
which I
don't want.
How do I just get the cells with text in them.
Thanks,
Richard
This is my code
ActiveSheet.Unprotect
Application.ScreenUpdating = False
Application.Goto
Reference:="R9C16:R110C16" 'P9
Selection.SpecialCells
(xlCellTypeVisible).Select
Selection.Copy
Application.Goto
Reference:="R9C20:R110C20" 'T9
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
Selection.Copy
Range("B4").Select
Application.ScreenUpdating = True
End Sub
.
.
.
|