ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Copying a range to clipboard (https://www.excelbanter.com/excel-programming/278691-copying-range-clipboard.html)

Richard[_18_]
Copying a range to clipboard
 
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

Tom Ogilvy
Copying a range to clipboard
 
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

Richard[_15_]
Copying a range to clipboard
 
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" 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


.

Richard[_18_]
Copying a range to clipboard
 
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


.

Tom Ogilvy
Copying a range to clipboard
 
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


.

Richard[_18_]
Copying a range to clipboard
 
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

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


.



.

Richard[_18_]
Copying a range to clipboard
 
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


.



.

Tom Ogilvy
Copying a range to clipboard
 
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


.



.

Richard[_18_]
Copying a range to clipboard
 
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


.



.



.

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


.



.



.

Richard[_18_]
Copying a range to clipboard
 
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" 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


.



.



.



.


All times are GMT +1. The time now is 06:51 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com