Paste or Paste Special is not Pasting - need help

[tekniks]

I have data in Sheet1 and Sheet2 of the workbook.
I have the following code in which it copies the range values from Sheet2 based on a condition and has to paste the same range values at the end of Sheet1.

Somehow the code is not pasting the data on Sheet1 , any suggestions to debug will be of great help.

For c = 1 To maxCols

For R = 1 To maxRows
searchString = ""
On Error Resume Next

searchString = ws2.Cells(R, c).Value

Stat_val = ws2.Cells(R, 2).Value
If Stat_val = "SUBMITTED" Then

Set mycell = ws1.Cells.Find(What:=searchString, After:=ws1.Cells(2, 1), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If mycell Is Nothing Then

With ws2.Range(Cells(R, c), Cells(R, 10))

.Copy ws1.Range(Cells(lr1 + 1, 1), Cells(lr1 + 1, 10))
ws1.Cells(lr1 + 1, 1).Resize(ws1.Rows.Count, _
ws1.Columns.Count).Value = .Value
ws1.Range(Cells(lr1 + 1, 1), Cells(lr1 + 1, 10)).PasteSpecial
End With

End If
Else
End If
Set mycell = Nothing
Next R
Next c


TIA

TKS

 

[SkipVought]

Hi,

Your problem is that you have a resize after the copy. paste should immediatley follow your copy.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[tekniks]

Commenting out these lines:
ws1.Cells(lr1 + 1, 1).Resize(ws1.Rows.Count, _
ws1.Columns.Count).Value = .Value

generates an error:

"Data on the Clipboard is not the same size and shape as the selected area."

Any suggestions to tweak it further...


Thanks

TKS

 

[SkipVought]

The paste destination should be a single cell reference

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[tekniks]

Changing the code to :

ws2.Range(Cells(R, c), Cells(R, 10)).copy

ws1.Paste

Doesn't paste the range either.

There has to be something else.

TKS

 

[tekniks]

OOPS !
the last line of the code is:

ws1.Cells(lr1 + 1, 1).Paste

Still it doesn't paste.

TKS

 

[SkipVought]

Try this...

    ws2.Range(Cells(R, c), Cells(R, 10)).copy _
    Destination:=ws1.Cells(lr1 + 1, 1)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[tekniks]

Thanks Skip ! It's Done.

TKS

 

[tekniks]

I don't know why but it is not pasting in sheet1(ws1) again although it copies the range from Sheet2(ws2)
This is the code:

ws2.Range(Cells(R, c), Cells(R, 10)).Copy _
Destination:=ws1.Range.Cells(lr1 + icount, 1)

Any ideas what's happening and why?????....

Thanks

TEKS

 

[SkipVought]

No error message -- it just does not paste?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[tekniks]

Yup, no error messages and no paste. Kinda strange...

Teks

 

[SkipVought]

and you're ABSOLUTELY SURE that you're executing this statement and NOT branching around it?

Put a break in your code on this statement and be sure that it breaks!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VBAjedi]

Not sure if this is relevant when the copy/paste are both in the same VBA procedure, but do you have any code in the worksheet_SelectionChange event or other worksheet events? This will often clear the clipboard. . .

I think you can bypass that particular problem when you are copy/pasting in VBA by setting Application.EnableEvents = False (and resetting it to True at the end of your procedure).

Again, not sure if this is a potential issue in your particular case, but I thought it would be worth checking.

VBAjedi

 

[SkipVought]

VBA,

Its all one statement. See the continuation character.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VBAjedi]

Skipper,

Yup, I saw that it was a single statement before I replied. And no, I didn't think it was possible for a worksheet event to be triggered in the middle of a statement like that. But I've had this problem pop up at weird times before, with the symptoms being like what was described: can't copy/paste to same sheet, but can copy from one sheet to another. The cause (in my case) has ALWAYS been code in the worksheet events.

Hey, I didn't say it was likely, just that it was a possibility to investigate. I figured if YOU hadn't solved it after five posts it must be something a bit unusual!

LOL

Once I hear tekniks say there's no code in the worksheet events I'll leave ya' be. . .



VBAjedi

 

[tekniks]

Hey Guys,

Please stop throwing things on each other....

I could resolve the issue with the following code:

ws2.Range(Cells(R, c), Cells(R, 10)).Copy
ws1.Activate
ActiveSheet.Paste Destination:=ws1.Range(Cells(lr1 + icount, 1), Cells(lr1 + icount, 10))

Hope it helps..

Thanks for all your help.

TEKS

 

[VBAjedi]

But DAD! He hit me FIRST!

LOL

tekniks,

"I could resolve the issue. . . hope that helps." I thought we were trying to help YOU. Are you saying your problem is resolved? Are you going to satisfy my burning curiosity and tell me if you have code in your worksheet events?

VBAjedi