Select Row Copy anomaly

[stanl]

The attached workbook has this sample module:
[VBA]
Sub copyrows()
Dim i As Integer
Dim cRange As String
Dim criteria As String
cString = ""
For i = 13 To 40
criteria = Trim(Cells(i, 8).Value)
If criteria = "C" Or criteria = "D" Then
cString = cString & i & ":" & i & ","
End If
Next
cString = Mid(cString, 1, Len(cString) - 1)
ActiveSheet.Range(cString).Select
Selection.Copy Destination:=ActiveSheet.Range("A50")
Selection.Copy
ActiveSheet.Range("A50").Activate
End Sub
[/VBA]

Prior to running the macro, open up Notepad. When the macro is complete you will see the correct criteria range placed starting in row 50, but activate Notepad and select Paste - seems to include the entire range from the first and last rows selected, not just those with grades C or D.

Is this expected behavior? Stan

[ska67can]

Try this:

[vba]Sub copyrows()
Dim i As Integer
Dim cRange As String
Dim criteria As String
cString = ""
For i = 13 To 40
criteria = Trim(Cells(i, 8).Value)
If criteria = "C" Or criteria = "D" Then
cString = cString & i & ":" & i & ","
End If
Next
cString = Mid(cString, 1, Len(cString) - 1)
Range(cString).Copy Destination:=Cells(50, 1)
Range(Cells(50, 1), Cells(100, 8).End(xlUp)).Copy
End Sub[/vba]

ska

[stanl]

Try this:

[vba]Sub copyrows()
Dim i As Integer
Dim cRange As String
Dim criteria As String
cString = ""
For i = 13 To 40
criteria = Trim(Cells(i, 8).Value)
If criteria = "C" Or criteria = "D" Then
cString = cString & i & ":" & i & ","
End If
Next
cString = Mid(cString, 1, Len(cString) - 1)
Range(cString).Copy Destination:=Cells(50, 1)
Range(Cells(50, 1), Cells(100, 8).End(xlUp)).Copy
End Sub[/vba]

ska

Thanks, but that misses the point. It is almost as though Selection.Copy without a destination - copies 2 formats into the clipboard - (1) for Excel which copies (or you could use a .paste and get the same results) the correct criteria and (2) one for other applications like Notepad.... and of course, this plain doesn't make sense...sorta 'Gates Gone Wild'. Even stranger, if you persist

ActiveSheet.Range(cString).Value(xlRangeValueMSPersistXML)

To file, it only includes the first row in the range. So, to re-phrase the original question - why do two different sets of data result from the .copy method depending on the destination application?

[Norie]

Stan

I not sure what you are trying to do here or you're just doing testing.

But if you take look at the worksheet what's still selected?

[stanl]

Stan
I not sure what you are trying to do here or you're just doing testing.

I'm hoping somebody will download the workbook, run the macro and compare both what was copied to the Range("A50") and what is pasted into Notepad. To answer your other question, the selected non-contiguous range (students with C or D grades) stays selected. I'm just curious as to the copy/pasting of non-contiguous ranges from excel to other applications. Seems the process is kinda buggy.

[Norie]

Stan

I did downloaded the file and did what you described. ie opening Notepad


If you copy a selection then surely that selection would be what's put on the clipboard and what would be pasted?

Maybe I'm missing something.

[stanl]

Stan
Maybe I'm missing something.

When you looked at the data pasted into Notepad. Did you notice lines where the Grade was NOT C or D? Maybe I'm the one missing something.

[stanl]

aha! Found this snippet on a discussion group

It?s two steps because Excel treats internal copy and paste totally differently from external copy and paste.

and I believe there is a similar behavior with browsers and non-contigous data.

[Carl A]

I downloaded your attachment and ran it as requested with the code in post #2 and what was pasted in Range A50 was the same that was pasted in notepad. Just C and D Grades displayed.

[stanl]

I downloaded your attachment and ran it as requested with the code in post #2 and what was pasted in Range A50 was the same that was pasted in notepad. Just C and D Grades displayed.

Thank you. I set up the example to determine a method to verify that coorect information was being copied out from a non-contiguous range. All of my tests had different results... sure be nice to understand what might account for the difference in results.

[stanl]

This is a screen shot of what I get.