simple copy & paste
-
- Panoramic Lounger
- Posts: 8128
- Joined: 25 Jan 2010, 09:09
- Location: retirement
simple copy & paste
It's Friday afternoon so the trivial is beyond me, even after Googling
Having said Cells.Copy how do I then say Cells.Paste?
Ken
Having said Cells.Copy how do I then say Cells.Paste?
Ken
-
- Administrator
- Posts: 78238
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: simple copy & paste
Cells.Copy Destination:=Cells
or
Cells.Value = Cells.Value
or
Cells.Copy
Cells.PasteSpecial
or
Cells.Value = Cells.Value
or
Cells.Copy
Cells.PasteSpecial
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78238
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: simple copy & paste
By the way, Cells is 1,048,576 * 16,384 = 17,179,869,184 cells.
If possible, use a limited range. If you want to copy all used cells, you could use ActiveSheet.UsedRange.
If possible, use a limited range. If you want to copy all used cells, you could use ActiveSheet.UsedRange.
Best wishes,
Hans
Hans
-
- Panoramic Lounger
- Posts: 8128
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: simple copy & paste
That's probably where I'm going wrong. All I want to do is copy the current cell to another cell.HansV wrote:By the way, Cells is 1,048,576 * 16,384 = 17,179,869,184 cells...
Ken
-
- Administrator
- Posts: 78238
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: simple copy & paste
Examples:
ActiveCell.Copy Destination:=Range("D4")
or
ActiveCell.Copy Destination:=ActiveCell.Offset(0, 11)
ActiveCell.Copy Destination:=Range("D4")
or
ActiveCell.Copy Destination:=ActiveCell.Offset(0, 11)
Best wishes,
Hans
Hans
-
- Panoramic Lounger
- Posts: 8128
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: simple copy & paste
I wonder how you knew the required offset would be 11?HansV wrote:.Offset(0, 11)
Ken
-
- Panoramic Lounger
- Posts: 8128
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: simple copy & paste
and a few mins later, at about 16:15 on a Friday, I think I've got more or less what I want so I'm going to quit while I'm ahead.
Thank you for all your help this week. Enjoy the weekend!
Ken
Thank you for all your help this week. Enjoy the weekend!
Ken
-
- Administrator
- Posts: 78238
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: simple copy & paste
Have a good weekend! Hopefully less dreary and grey than it is here at the moment...
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: simple copy & paste
Just for fun....
Add the '' * Worksheets Paste Method * to the list of ways to do it...
( or is that the same as the Bypass Clipboard ( "shortcut" ) way Hmm I do not think so
EDIT:Have a nice WeekEnd
Alan
Add the '' * Worksheets Paste Method * to the list of ways to do it...
( or is that the same as the Bypass Clipboard ( "shortcut" ) way Hmm I do not think so
Code: Select all
Sub WorksheetMethodPaste() ' ' http://www.eileenslounge.com/viewtopic.php?f=27&t=22512
' Let ActiveCell.Offset(0, 11).Value = ActiveCell.Value ' Value way
'ActiveCell.Copy Destination:=ActiveCell.Offset(0, 11) ' Bypass Clipboard ( "shortcut" ) way
ActiveCell.Copy ' Range Copy Method
'ActiveCell.Offset(0, 11).PasteSpecial xlPasteAllUsingSourceTheme ' Range PasteSpecial Method
'
' *
ActiveSheet.Paste Destination:=ActiveCell.Offset(0, 11) ' ' * Worksheets Paste Method * ( goes to active Cell if optional argument is ommited
' *
'
' Rambling Ways for after the WeekEnd, To warm up on Monday Morning !! ;)
' Rambling Value ways ;)
Let ActiveCell.Offset(0, 11).Value = Application.Index(Cells, ActiveCell.Row, ActiveCell.Column) ' ... ! ;) ? Cells Index way?
Let ActiveCell.Offset(0, 11).Value = Cells(ActiveCell.Row, ActiveCell.Column) ' ? Cells co ordinate Way
' Rambling Range Ways ;)
Dim rngCrossOver As Range
Set rngCrossOver = Intersect(ActiveCell.EntireRow, ActiveCell.EntireColumn) '
Let ActiveCell.Offset(0, 11).Value = rngCrossOver.Value
' or
Let ActiveCell.Offset(0, 11).Value = Intersect(ActiveCell.EntireRow, ActiveCell.EntireColumn).Value ' Intersect returns Range Object ( A single Cell here ) where the Row and Column Range Objects crossover. .Value Property applied to return the Cell Value
'
Dim strRangeRef As String ' http://excelmatters.com/referring-to-ranges-in-vba/#comment-185788
Let strRangeRef = "=" & FukOutChrWithDoWhile(ActiveCell.Column) & ActiveCell.Row & "": Debug.Print strRangeRef ' Ctrl G reveals =A17 in Immediate Window
Dim AppRange As Range ' Application Range Object
Set AppRange = Range("" & strRangeRef & "") ' Unqualified Range referrence goes to Application Range, determined by string referrence
Let ActiveCell.Offset(0, 11).Value = AppRange.Value
'
Let strRangeRef = Replace(strRangeRef, "=", "", 1, -1)
Let strRangeRef = "='" & ThisWorkbook.Path & "\" & "[" & ThisWorkbook.Name & "]" & ActiveSheet.Name & "'!" & strRangeRef & "": Debug.Print strRangeRef ' In Immediate Window gives string referrence like ='H:\Excel0202015Jan2016\ExcelForum\RangeArrayBasicsTips\[copyPaster_test.xlsm]Sheet1'!A18
Set AppRange = Range("" & strRangeRef & "")
Let ActiveCell.Offset(0, 11).Value = AppRange.Value
End Sub
' http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
Function FukOutChrWithDoWhile(ByVal lclm As Long) As String 'Using chr function and Do while loop For example http://www.excelforum.com/excel-programming-vba-macros/796472-how-to-go-from-column-number-to-column-letter.html
Dim rest As Long 'Variable for what is "left over" after subtracting as many full 26's as possible
Do
' Let rest = ((lclm - 1) Mod 26) 'Gives 0 to 25 for Column Number "Left over" 1 to 26. Better than ( lclm Mod 26 ) which gives 1 to 25 for clm 1 to 25 then 0 for 26
' Let FukOutChrWithDoWhile = Chr(65 + rest) & FukOutChrWithDoWhile 'Convert rest to Chr Number, initially with full number so the "units" (0-25), then number of 26's left over (if the number was so big to give any amount of 26's in it, then number of 26's in the 26's left over (if the number was so big to give any amount of 26 x 26's in it, Enit ?
' 'OR
Let FukOutChrWithDoWhile = Chr(65 + (((lclm - 1) Mod 26))) & FukOutChrWithDoWhile
Let lclm = (lclm - (1)) \ 26 'This gives the number of 26's ( if any ), but just the excact part, in the next number down , - so applying the rest formula to this new number will again leave a difference "left over" rest.
'lclm = (lclm - (rest + 1)) \ 26 ' As the number is effectively truncated here, any number from 1 to (rest +1) will do in the formula
Loop While lclm > 0 'Only loop further if number was big enough to still have 0-25's in it
End Function
' http://www.eileenslounge.com/viewtopic.php?f=27&t=22512
' http://www.excelforum.com/showthread.php?t=1144116&p=4414189&highlight=#post4414189
' http://www.excelforum.com/showthread.php?t=1114014&page=3&p=4256195#post4256195
' http://www.mrexcel.com/forum/excel-questions/828241-visual-basic-applications-autofilter-specialcells-xlcelltypevisible-copy-only-values-not-formulas.html#post4043472
' http://www.mrexcel.com/forum/excel-questions/785462-visual-basic-applications-copy-value-paste-worksheet-same-name-value-3.html#post4071766
' http://www.excelforum.com/excel-programming-vba-macros/1159780-syntax-problem-with-intersect-statement.html#post4504411
' http://www.excelforum.com/tips-and-tutorials/1108643-vba-column-letter-from-column-number-explained.html
' https://app.box.com/s/lts3h2ejf33yj2i2xmaq5zrq784qwwl9
' http://www.excelforum.com/showthread.php?t=1154829&page=2&p=4483347&posted=1#post4483344
Alan
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Copy Paste Multi Area Range Objects
Copy Paste Multi Area Range Objects
Hi ,
I thought this could be alright here as a “Copy Paste” type follow up question. It is based on basic Copy Paste stuff ( I think)....But just for a Copy and Paste of Non-Contiguous Cells
I am getting a bit confused why the following is working, and I am not sure I quite know what I am talking about with the different “ways” I describe.
So until I understand it a bit more I am a bit wary of using it. But it does seem it might be useful to me.
Can anyone help me get some clarity on this? - I will tell you what I am doing first with a reduced size example
Say I have this_..
Using Excel 2007
|< < > >|_RangeObjects_/___//
_.. and I have a demo code below, Sub CopyPasteAreas()
In that code, in Rem 1 code section I get from the Range Above a Range Object consisting of two Areas of Contiguous Cells ( 4 cells in total )
So like I end up with
rngObj.Areas(1) is Range("A10:B10”)
rngObj.Areas(2) is Range("A13:B13”)
I thought it is not possible to copy a Range Object of more than one Area. But my demo code , Sub CopyPasteAreas() , is working provided that the columns in the Ranges are in line
http://www.excelforum.com/excel-program ... ost4529679" onclick="window.open(this.href);return false;
So I do the Copy ( Range Copy Method on rngObj like rngObj.Copy ) at various parts of my code.
I experimented with 3 paste “ways” that I know about ( I am not interested in values “ways” here )
Rem 2 "Copy Destination" "way"
rngObj.Copy Destination:=WsRO.Range("A20")
Rem 3 "Range Copy Method Worksheets Paste Method" "way"
rngObj.Copy
WsRO.Paste Destination:=WsRO.Range("A20")
Rem 4 "Range Copy Method Range PasteSpecial Method" "way"
rngObj.Copy
WsRO.Range("A20").PasteSpecial Paste:=xlPasteValues
To my surprise they “work” to give me this form , with the “rows” in between which were empty removed
So a couple of things are puzzling are me.
Question_1) Why does this work, and can one enlighten me on what is going on to make this appear to work?
Question_2) I have heard that the_..
Rem 2 "Copy Destination" "way"
_..does not use the Clipboard.. I am wondering if this is really true? - Towards the end of the code I demo that this “way” has the by product of clearing the clipboard ( as many things do ) Rem 6
So I am wondering if it does what I demo in the complete Rem 3) section which is the basic "Range Copy Method Worksheets Paste Method" "way" , with the optional Destination:= argument as well as a line to clear the clipboard
( I thought I would be clever at this point and get the text from the clipboard in between the “ways” to check this. – But of course I cannot do that for "Copy Destination" "way” as the one line executes of course, in one line, and as I found it clears the clipboard anyway. So does anyone know that for sure. I am wondering if people have been fooled into thinking it bypasses the clipboard
Question_3) A final question.. Also in the code window below is another Code, Sub GetItFromClipboardText() . This gives two message boxes at the end. They simply show the text string which I get from the clipboard as a result of a code line like
rngObj.Copy
I assume the info is in that text string to give me this? _..
_...rather than this
( The latter is how it comes out in the Message Boxes with the empty rows still included. )
If that is the case, then can anyone tell me if they know of a way to use that string to get those cell values into an Array directly rather than pasting out and then bringing it back in a _..
arr() = Range(“A20”).CurrentRegion
_.. type thing.
Alternatively if anyone knows how to “see” that string as it is , pseudo like_..
“Areas1,Cells(1)” & Tab & “Areas1,Cells(2)” & vbCrLf & Tab & Tab ... etc... etc..
_.. then I could probably have a go at doing that myself
( I guess I may be talking rubbish on this last question – possibly VBA “takes” a version of the Clipboard that has other information. If that is the case, then it is curious that the Rem 2 "Copy Destination" "way" works... that is if it does by pass the clipboard ?? ? )
Thanks
Alan
_....................
Demo Codes
Sub CopyPasteAreas()
And
Sub GetItFromClipboardText()
Hi ,
I thought this could be alright here as a “Copy Paste” type follow up question. It is based on basic Copy Paste stuff ( I think)....But just for a Copy and Paste of Non-Contiguous Cells
I am getting a bit confused why the following is working, and I am not sure I quite know what I am talking about with the different “ways” I describe.
So until I understand it a bit more I am a bit wary of using it. But it does seem it might be useful to me.
Can anyone help me get some clarity on this? - I will tell you what I am doing first with a reduced size example
Say I have this_..
Using Excel 2007
Row\Col | A | B |
10 | Areas1,Cells(1) | Areas1,Cells(2) |
11 | ||
12 | ||
13 | Areas2,Cells(1) | Areas2,Cells(2) |
_.. and I have a demo code below, Sub CopyPasteAreas()
In that code, in Rem 1 code section I get from the Range Above a Range Object consisting of two Areas of Contiguous Cells ( 4 cells in total )
So like I end up with
rngObj.Areas(1) is Range("A10:B10”)
rngObj.Areas(2) is Range("A13:B13”)
I thought it is not possible to copy a Range Object of more than one Area. But my demo code , Sub CopyPasteAreas() , is working provided that the columns in the Ranges are in line
http://www.excelforum.com/excel-program ... ost4529679" onclick="window.open(this.href);return false;
So I do the Copy ( Range Copy Method on rngObj like rngObj.Copy ) at various parts of my code.
I experimented with 3 paste “ways” that I know about ( I am not interested in values “ways” here )
Rem 2 "Copy Destination" "way"
rngObj.Copy Destination:=WsRO.Range("A20")
Rem 3 "Range Copy Method Worksheets Paste Method" "way"
rngObj.Copy
WsRO.Paste Destination:=WsRO.Range("A20")
Rem 4 "Range Copy Method Range PasteSpecial Method" "way"
rngObj.Copy
WsRO.Range("A20").PasteSpecial Paste:=xlPasteValues
To my surprise they “work” to give me this form , with the “rows” in between which were empty removed
Row\Col | A | B |
20 | Areas1,Cells(1) | Areas1,Cells(2) |
21 | Areas2,Cells(1) | Areas2,Cells(2) |
Question_1) Why does this work, and can one enlighten me on what is going on to make this appear to work?
Question_2) I have heard that the_..
Rem 2 "Copy Destination" "way"
_..does not use the Clipboard.. I am wondering if this is really true? - Towards the end of the code I demo that this “way” has the by product of clearing the clipboard ( as many things do ) Rem 6
So I am wondering if it does what I demo in the complete Rem 3) section which is the basic "Range Copy Method Worksheets Paste Method" "way" , with the optional Destination:= argument as well as a line to clear the clipboard
( I thought I would be clever at this point and get the text from the clipboard in between the “ways” to check this. – But of course I cannot do that for "Copy Destination" "way” as the one line executes of course, in one line, and as I found it clears the clipboard anyway. So does anyone know that for sure. I am wondering if people have been fooled into thinking it bypasses the clipboard
Question_3) A final question.. Also in the code window below is another Code, Sub GetItFromClipboardText() . This gives two message boxes at the end. They simply show the text string which I get from the clipboard as a result of a code line like
rngObj.Copy
I assume the info is in that text string to give me this? _..
Areas1,Cells(1) | Areas1,Cells(2) |
Areas2,Cells(1) | Areas2,Cells(2) |
Areas1,Cells(1) | Areas1,Cells(2) |
. | |
. | |
Areas2,Cells(1) | Areas2,Cells(2) |
If that is the case, then can anyone tell me if they know of a way to use that string to get those cell values into an Array directly rather than pasting out and then bringing it back in a _..
arr() = Range(“A20”).CurrentRegion
_.. type thing.
Alternatively if anyone knows how to “see” that string as it is , pseudo like_..
“Areas1,Cells(1)” & Tab & “Areas1,Cells(2)” & vbCrLf & Tab & Tab ... etc... etc..
_.. then I could probably have a go at doing that myself
( I guess I may be talking rubbish on this last question – possibly VBA “takes” a version of the Clipboard that has other information. If that is the case, then it is curious that the Rem 2 "Copy Destination" "way" works... that is if it does by pass the clipboard ?? ? )
Thanks
Alan
_....................
Demo Codes
Sub CopyPasteAreas()
And
Sub GetItFromClipboardText()
Code: Select all
'
Sub CopyPasteAreas()
10 Rem 1 Worksheets info
20 Dim WsRO As Worksheet: Set WsRO = ThisWorkbook.Worksheets("RangeObjects")
30 WsRO.Range("A20:C25").ClearContents ' Just to clear any last experiment
40 Dim rngObj As Range
50 Set rngObj = WsRO.Range("A10:B10,A13:B13")
' Or, assuming no blanks in wanted rows...
60 Set rngObj = WsRO.Range("A10:C15").SpecialCells(xlCellTypeConstants, xlTextValues) '
70
80 Rem 2 "Copy Destination" "way"
90 rngObj.Copy Destination:=WsRO.Range("A20") ' Works
100 ' Application.CutCopyMode = False ' Not needed ? Do we By pass Clipboard
110 Rem 3 "Range Copy Method Worksheets Paste Method" "way"
120 WsRO.Range("A20:B25").ClearContents ' Just to clear last experiment
130 rngObj.Copy ' Range Copy Method
140 WsRO.Paste Destination:=WsRO.Range("A20") 'Worksheets Paste Method ' works
150 Application.CutCopyMode = False ' Clear Clipboard ( Stop screen flicker )
160 Rem 4 "Range Copy Method Range PasteSpecial Method" "way"
170 WsRO.Range("A20:B25").ClearContents ' Just to clear last experiment
180 rngObj.Copy ' Range Copy Method
190 WsRO.Range("A20").PasteSpecial Paste:=xlPasteValues 'Range PasteSpecials Method
200 Application.CutCopyMode = False ' Clear Clipboard ( Stop screen flicker )
210 Rem 5 "Range Copy Method Worksheets Paste Method Range PasteSpecial Method" "way"
220 WsRO.Range("A20:B25").ClearContents ' Just to clear last experiment
230 rngObj.Copy ' Range Copy Method
240 WsRO.Paste Destination:=WsRO.Range("A20") ' Works
250 ' WsRO.Range("A20:A1000").ClearContents ' Just to clear last experiment.. Do not do it - it clears the Clipboard
260 WsRO.Range("A20").PasteSpecial Paste:=xlPasteValues ' Works
270 Application.CutCopyMode = False ' Clear Clipboard ( Stop screen flicker )
280 Rem 6 "Range Copy Method Copy Destination Range Paste Method" "way"
290 WsRO.Range("A20:B25").ClearContents ' Just to clear last experiment
300 rngObj.Copy ' Range Copy Method
310 rngObj.Copy Destination:=WsRO.Range("A20") ' Works but clears Clipboard
320 ' WsRO.Range("A20:B25").ClearContents ' Just to clear last experiment.. Do not do it - it clears the Clipboard
'330 WsRO.Range("A20").PasteSpecial Paste:=xlPasteValues ' Does not now work as line 310 as a by product cleared the Clipboard
End Sub
'
Sub GetItFromClipboardText()
Rem 1) Worksheets info
Dim WsRO As Worksheet: Set WsRO = ThisWorkbook.Worksheets("RangeObjects")
WsRO.Range("A20:B25").ClearContents ' Just to clear any last experiment
Dim rngObj As Range
Set rngObj = WsRO.Range("A10:B10,A13:B13")
Rem 2) Range object of two Arreas Copy to Clipboard
rngObj.Copy ' Range Copy Method
Rem 3 Get infomation from clipboard as text string - Current clipboard text is put in a long string
Dim objCliCopied As DataObject '**Early Binding. This is for an Object from the class MS Forms. This will be a Data Object of what we "send" to the Clipboard. So I name it CLIpboardSend. But it is a DataObject. It has the Methods I need to send text to the Clipboard
Set objCliCopied = New DataObject '**Must enable Forms Library: In VB Editor do this: Tools -- References - scroll down to Microsoft Forms 2.0 Object Library -- put checkmark in. Note if you cannot find it try OR IF NOT THERE..you can add that manually: VBA Editor -- Tools -- References -- Browse -- and find FM20.DLL file under C:\WINDOWS\system32 and select it --> Open --> OK. http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
' ( or instead of those two lines Dim objCliCodeCopied As New DataObject ). or next two lines are.....Late Binding equivalent'
'Dim objCliCopied As Object ' Late Binding equivalent' If you declare a variable as Object, you are late binding it. http://excelmatters.com/2013/09/23/vba-references-and-early-binding-vs-late-binding/
'Set objCliCopied = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") ' http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
objCliCopied.GetFromClipboard 'All that is in the Clipboard goes in this Data Object initial instance of the Class
Dim strClp As String ' String varable to take Required copy column.' ' Prepares "Pointer" to a "Blue Print" (or Form, Questionnaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular “Value”, or (“Values” for Objects). There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. String is a a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructiions will tell how to do this. Theoretically a specilal value vbNullString is set to aid in quick checks-But-http://www.mrexcel.com/forum/excel-q...html#post44
Let strClp = objCliCopied.GetText() 'retrieve the text in the instance of the Class.
MsgBox prompt:="" & strClp & ""
MsgBox prompt:=Replace("" & strClp & "", vbCrLf & vbCrLf, "", 1, -1, vbTextCompare)
Rem 4) What uses the clipboard then ?
Application.CutCopyMode = False ' Clear clipboard
'4.2 Hmm ....
End Sub
'
' http://www.eileenslounge.com/viewtopic.php?f=27&t=25002&p=193871#p193871
' http://www.excelforum.com/excel-formulas-and-functions/1162752-paste-special-not-working-correctly.html#post4519556
' http://www.excelforum.com/excel-programming-vba-macros/1161452-paste-as-absolute-value.html
' http://www.eileenslounge.com/viewtopic.php?f=30&t=22787&p=177745#p177079
' http://www.excelforum.com/excel-programming-vba-macros/1164486-select-and-copy-non-contiguous-cells.html
Last edited by Doc.AElstein on 29 Nov 2016, 23:47, edited 1 time in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- Administrator
- Posts: 78238
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: simple copy & paste
Alan,
I don't know about others, but I for one am not willing to spend the time and effort to read a post consisting of more than 1,500 words and more than 12,000 characters. If you state your question concisely you probably have a better chance of getting a helpful reply.
I don't know about others, but I for one am not willing to spend the time and effort to read a post consisting of more than 1,500 words and more than 12,000 characters. If you state your question concisely you probably have a better chance of getting a helpful reply.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: simple copy & paste
Hi Hans
Apologies, I was just trying to give as much detail to help explain the questions :(
The questions I tried to summarise as well: I try again
_1) Why it appears to work that you can copy a Multi Area Range Object. I was just trying to understand a bit better what is going on
_2) Does this type of code line
rngObj.Copy Destination:=WsRO.Range("A20")
really does by pass the clipboard? I had not seen any precise documentation. I found it difficult to prove experimentally
_3) I was asking if the text string returned from the Clipboard after a copy of a Multi Area Range Object, had information in it from which I might be able to put the Cell value data within that text string into an Array directly, rather than pasting out and then retrieving an Array from the pasted out Range.
Alan
Apologies, I was just trying to give as much detail to help explain the questions :(
The questions I tried to summarise as well: I try again
_1) Why it appears to work that you can copy a Multi Area Range Object. I was just trying to understand a bit better what is going on
_2) Does this type of code line
rngObj.Copy Destination:=WsRO.Range("A20")
really does by pass the clipboard? I had not seen any precise documentation. I found it difficult to prove experimentally
_3) I was asking if the text string returned from the Clipboard after a copy of a Multi Area Range Object, had information in it from which I might be able to put the Cell value data within that text string into an Array directly, rather than pasting out and then retrieving an Array from the pasted out Range.
Alan
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- Administrator
- Posts: 78238
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: simple copy & paste
2) Such a line clears the clipboard, like most VBA actions that change the workbook.
I'll take a look at the other questions later (after dinner)
I'll take a look at the other questions later (after dinner)
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: simple copy & paste
Hi Hans
Thanks
Lass es dir schmecken
Alan
Thanks
Ahh, as I thought... so possibly it may or may not actually use the Clipboard, as I suspect ?HansV wrote:2) Such a line clears the clipboard, like most VBA actions that change the workbook...
No rush Hans ( I never need or expect a quick answer on my Posts ) - I am off for a long jog in the Bavarian Hills myself now !HansV wrote:..
I'll take a look at the other questions later
Guten Appetite ! , here something for aftersHansV wrote:.... (after dinner)
Lass es dir schmecken
Alan
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- Administrator
- Posts: 78238
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: simple copy & paste
1) Excel allows copying of multi-area ranges. However, while pasting the ranges will be moved together, so that the result will be a single-area range.
This works in the Excel interface as well as in VBA.
3) To convert the text string back to a range would require splitting the string into lines, and splitting each line into separate values. That seems more work than it's worth to me.
This works in the Excel interface as well as in VBA.
3) To convert the text string back to a range would require splitting the string into lines, and splitting each line into separate values. That seems more work than it's worth to me.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: simple copy & paste
Hi Hans
_1)Thanks ( It seems also that the ranges must be in line, these 2 Areas for example cannot be copied
Using Excel 2007
_....
_3 ) Thanks.
Do you know if there is a way to “see” the format of the string in its constituent form, like
“Areas1,Cells(1)” & Tab & “Areas1,Cells(2)” & vbCrLf & Tab & Tab ... etc... etc..
_...I thought there might be some Property or Method other than .GetText() from the DataObject, or some method property to get something other than the string text where you cannot “see” things like vbCrLf , Tab etc..
Alan
_1)Thanks ( It seems also that the ranges must be in line, these 2 Areas for example cannot be copied
Using Excel 2007
Row\Col | A | B | C |
10 | Areas1,Cells(1) | Areas1,Cells(2) | |
11 | . | ||
12 | . | ||
13 | Areas2,Cells(1) | Areas2,Cells(2) |
_3 ) Thanks.
Do you know if there is a way to “see” the format of the string in its constituent form, like
“Areas1,Cells(1)” & Tab & “Areas1,Cells(2)” & vbCrLf & Tab & Tab ... etc... etc..
_...I thought there might be some Property or Method other than .GetText() from the DataObject, or some method property to get something other than the string text where you cannot “see” things like vbCrLf , Tab etc..
Alan
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: simple copy & paste
Hey Alan,
Not to break from the topic, but one other thing that can help to make your posts easier to view, is to encourage you to use a screen capture app. instead of all that effort to create tables to mimic a spreadsheet, why not just screen capture the image using any one of a number of free capture apps...or just use the built in Windows snipping tool?
IMHO, this...
[/size]
...is much quicker and easier to add to a post, and much easier to read than this:
|< < > >|_RangeObjects_/___//
Please note: Just my personal opinion...
Not to break from the topic, but one other thing that can help to make your posts easier to view, is to encourage you to use a screen capture app. instead of all that effort to create tables to mimic a spreadsheet, why not just screen capture the image using any one of a number of free capture apps...or just use the built in Windows snipping tool?
IMHO, this...
[/size]
...is much quicker and easier to add to a post, and much easier to read than this:
Row\Col | A | B |
10 | Areas1,Cells(1) | Areas1,Cells(2) |
11 | ||
12 | ||
13 | Areas2,Cells(1) | Areas2,Cells(2) |
Please note: Just my personal opinion...
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78238
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: simple copy & paste
Hi Rudi, Hi Hans
It’s a cruel World, ..Lol.. - in most Forums you get clobbered big time if you post Images of spreadsheet ranges.... . The argument is that you cannot copy images easily to a spreadsheet Range. But that image from Rudi certainly looks a lot clearer than mine. And uploading Files is an option here so I will try to get in the habit of images and File uploading here. I use Paint and Snipping Tool. I caught these Threads http://www.eileenslounge.com/viewtopic.php?f=12&t=18566" onclick="window.open(this.href);return false; http://www.eileenslounge.com/viewtopic. ... 77#p192677" onclick="window.open(this.href);return false; and I think there are a few more Threads discussing Image posting here, so I will have lurk.
Thanks for the advice. ( But it isn’t easier and quicker on my old computer to do an image. – For my table I highlight the Range , run a code version of one given to me by Hans ( http://www.eileenslounge.com/viewtopic. ... 03#p192671" onclick="window.open(this.href);return false; ) then paste in the Editor. To get snipping tool up and save the image and upload in a Thread Post can take half a minute on my computer, - but it is no big deal – I will get in the habit of doing it )
_...........____
http://www.mrexcel.com/forum/excel-ques ... ost4043472" onclick="window.open(this.href);return false;
_... I am sure I saw a code once that gave out the different versions.. you ended up getting something looking like the second two .Value( ) ‘s
https://msdn.microsoft.com/en-us/librar ... 95193.aspx" onclick="window.open(this.href);return false;
https://msdn.microsoft.com/en-us/librar ... 37565.aspx" onclick="window.open(this.href);return false;
_ .. I have also been told that I am mad already .. so I will take another look sometime and post back my findings :)
Have a good Weekend, Both
Alan
It’s a cruel World, ..Lol.. - in most Forums you get clobbered big time if you post Images of spreadsheet ranges.... . The argument is that you cannot copy images easily to a spreadsheet Range. But that image from Rudi certainly looks a lot clearer than mine. And uploading Files is an option here so I will try to get in the habit of images and File uploading here. I use Paint and Snipping Tool. I caught these Threads http://www.eileenslounge.com/viewtopic.php?f=12&t=18566" onclick="window.open(this.href);return false; http://www.eileenslounge.com/viewtopic. ... 77#p192677" onclick="window.open(this.href);return false; and I think there are a few more Threads discussing Image posting here, so I will have lurk.
Thanks for the advice. ( But it isn’t easier and quicker on my old computer to do an image. – For my table I highlight the Range , run a code version of one given to me by Hans ( http://www.eileenslounge.com/viewtopic. ... 03#p192671" onclick="window.open(this.href);return false; ) then paste in the Editor. To get snipping tool up and save the image and upload in a Thread Post can take half a minute on my computer, - but it is no big deal – I will get in the habit of doing it )
_...........____
Thanks I expect going through the character string and checking the type would be one way, then looping in based on the findings to an appropriately sized Array. But all that might not in end effect be better than pasting out and bringing into an Array in one go. It is strange that the text string appears to include the lines not copied – here an image from my second code Sub GetItFromClipboardText() That suggests VBA is doing something else or getting what it Pastes possibly from a different Clipboard version.. But as you say, it is probably more trouble than it is worth and trying to figure out the Clipboard leads to madness I have often been told_....HansV wrote:1) Correct.
3) Not that I know of.
http://www.mrexcel.com/forum/excel-ques ... ost4043472" onclick="window.open(this.href);return false;
_... I am sure I saw a code once that gave out the different versions.. you ended up getting something looking like the second two .Value( ) ‘s
https://msdn.microsoft.com/en-us/librar ... 95193.aspx" onclick="window.open(this.href);return false;
https://msdn.microsoft.com/en-us/librar ... 37565.aspx" onclick="window.open(this.href);return false;
_ .. I have also been told that I am mad already .. so I will take another look sometime and post back my findings :)
Have a good Weekend, Both
Alan
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- Panoramic Lounger
- Posts: 8128
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: simple copy & paste
Hmm, having revisited this thread because I got a notification of a new post, I'm glad that I quit while I was ahead
Ken
Ken