simple copy & paste

User avatar
stuck
Panoramic Lounger
Posts: 8128
Joined: 25 Jan 2010, 09:09
Location: retirement

simple copy & paste

Post by stuck »

It's Friday afternoon so the trivial is beyond me, even after Googling :brickwall:

Having said Cells.Copy how do I then say Cells.Paste?

Ken

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: simple copy & paste

Post by HansV »

Cells.Copy Destination:=Cells

or

Cells.Value = Cells.Value

or

Cells.Copy
Cells.PasteSpecial
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: simple copy & paste

Post by HansV »

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.
Best wishes,
Hans

User avatar
stuck
Panoramic Lounger
Posts: 8128
Joined: 25 Jan 2010, 09:09
Location: retirement

Re: simple copy & paste

Post by stuck »

HansV wrote:By the way, Cells is 1,048,576 * 16,384 = 17,179,869,184 cells...
:groan: That's probably where I'm going wrong. All I want to do is copy the current cell to another cell.

Ken

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: simple copy & paste

Post by HansV »

Examples:

ActiveCell.Copy Destination:=Range("D4")

or

ActiveCell.Copy Destination:=ActiveCell.Offset(0, 11)
Best wishes,
Hans

User avatar
stuck
Panoramic Lounger
Posts: 8128
Joined: 25 Jan 2010, 09:09
Location: retirement

Re: simple copy & paste

Post by stuck »

HansV wrote:.Offset(0, 11)
:innocent: I wonder how you knew the required offset would be 11?

:thankyou:

Ken

User avatar
stuck
Panoramic Lounger
Posts: 8128
Joined: 25 Jan 2010, 09:09
Location: retirement

Re: simple copy & paste

Post by stuck »

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

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: simple copy & paste

Post by HansV »

Have a good weekend! Hopefully less dreary and grey than it is here at the moment...
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: simple copy & paste

Post by Doc.AElstein »

Just for fun....
Add the '' * Worksheets Paste Method * to the list of ways to do it... :smile:
( or is that the same as the Bypass Clipboard ( "shortcut" ) way :scratch: 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


EDIT:Have a nice WeekEnd
Alan
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Copy Paste Multi Area Range Objects

Post by Doc.AElstein »

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
Row\ColAB
10Areas1,Cells(1)Areas1,Cells(2)
11
12
13Areas2,Cells(1)Areas2,Cells(2)
|< < > >|_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
Row\ColAB
20Areas1,Cells(1)Areas1,Cells(2)
21Areas2,Cells(1)Areas2,Cells(2)
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? _..
Areas1,Cells(1)Areas1,Cells(2)
Areas2,Cells(1)Areas2,Cells(2)
_...rather than this
Areas1,Cells(1)Areas1,Cells(2)
.
.
Areas2,Cells(1)Areas2,Cells(2)
( 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()

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

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: simple copy & paste

Post by HansV »

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.
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: simple copy & paste

Post by Doc.AElstein »

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
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: simple copy & paste

Post by HansV »

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)
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: simple copy & paste

Post by Doc.AElstein »

Hi Hans

Thanks :smile:
HansV wrote:2) Such a line clears the clipboard, like most VBA actions that change the workbook...
Ahh, as I thought... so possibly it may or may not actually use the Clipboard, as I suspect ?
HansV wrote:..
I'll take a look at the other questions later
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:.... (after dinner)
Guten Appetite ! , here something for afters
:chocciebar:
Lass es dir schmecken
Alan
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: simple copy & paste

Post by HansV »

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.
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: simple copy & paste

Post by Doc.AElstein »

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
Row\ColABC
10Areas1,Cells(1)Areas1,Cells(2)
11.
12.
13Areas2,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

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: simple copy & paste

Post by Rudi »

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

SC002.jpg
[/size]
...is much quicker and easier to add to a post, and much easier to read than this:
Row\ColAB
10Areas1,Cells(1)Areas1,Cells(2)
11
12
13Areas2,Cells(1)Areas2,Cells(2)
|< < > >|_RangeObjects_/___//

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.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: simple copy & paste

Post by HansV »

1) Correct.
3) Not that I know of.
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: simple copy & paste

Post by Doc.AElstein »

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 )
_...........____
HansV wrote:1) Correct.
3) Not that I know of.
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()
Areas1Areas2.JPG
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_....
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

User avatar
stuck
Panoramic Lounger
Posts: 8128
Joined: 25 Jan 2010, 09:09
Location: retirement

Re: simple copy & paste

Post by stuck »

Hmm, having revisited this thread because I got a notification of a new post, I'm glad that I quit while I was ahead :laugh:

Ken