Copying a range to clipboard

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

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




.
Click to expand...
 
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
 
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
 
Thanks Tom,
I'll have a play around with it and get back to you. I
can't see what is wrong but it certainly generates the
error message on mine. I'll copy my formulas and code
relating only to this section to an empty sheet and see
what happens.

Regards,
Richard
 
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
 
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.
 
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
 
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,
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





Richard said:
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
Click to expand...


.
Click to expand...
 
Back
Top