Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range.Copy [Destination] should not use clipboard | Excel Discussion (Misc queries) | |||
Clipboard empty but still get waring that clipboard is full | Excel Discussion (Misc queries) | |||
Copying to Excel Clipboard | Excel Discussion (Misc queries) | |||
Copying to Clipboard query. | New Users to Excel | |||
Copying the text from a text box to the clipboard | Excel Programming |