Copying a range to clipboard

[Richard]

Sub Send2Application()

Could someone lend a hand here please.

I'm copying the range P9110 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

 

[Tom Ogilvy]

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

 

[Richard]

Thanks Tom,
Regards,
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]

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 P9100 is anti-aliased and Range T9:T100 is
highlighted when it crashes.

Any clues?

Thanks,
Richard

 

[Tom Ogilvy]

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.

 

[Richard]

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

 

[Tom Ogilvy]

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]

Hi Tom,
Yes, I meant marching ants.
Will use your suggestion. Thanks for all that.
Regards,
Richard

-----Original Message-----
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

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







with
the Crashes
at
Reference:="R9C16:R110C16" 'P9
Reference:="R9C20:R110C20" 'T9

.