Paste Special Method of Range Class Failed

[krish42632]

kindly solve this issue



my code is


Private Sub Button9_Click()


Sheets("sys").Range("A9142:A20000").Copy


Dim lastrow As Long
lastrow = Range("A9249:A20000").End(xlUp).Row


Sheets("Sheet1").Activate
Cells(lastrow + 1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


End Sub

 

[krish42632]

https://drive.google.com/open?id=0BzYVmIR6p1Wza21IRDVteWxWbFU

 

[Peter_SSs]

.. make sure that the error still occurs in that copy of the file.

I get no error when clicking the button on Sheet1.

 

[krish42632]

https://drive.google.com/open?id=0BzYVmIR6p1WzbEJqNUdjWjMwdlk

https://drive.google.com/open?id=0BzYVmIR6p1WzM0dTenlXQkRSQVE

Error Screen Shots Attached

 

[Fluff]

Couple of things.
1) this line is not needed, as you have to be on sheet1, in order to click the button

Sheets("Sheet1").Activate

2) With the data setup in your sample lastrow will return 1. Suggest you change to

lastrow = Range("A" & Rows.Count).End(xlUp).Row

I suspect that you are getting the error, because lastrow is 1 & row 1 is hidden, due to the filter

 

[Peter_SSs]

Error Screen Shots Attached

Yes, but do you get that error with the sample file that you provided? I don't.

The macro just copies A9249:A20000 from 'sys' (all formulas that return zero but formatted as "-") and pastes those zero value into Sheet1, starting at cell A1. In Sheet 1 column A you see nothing because you have Excel set to not show zero values in that sheet.

 

[Fluff]

@Peter_SSs
Not sure if it's a version thing (I'm using 2013), but I got the error message described, using the sample file.

 

[Peter_SSs]

@Peter_SSs
Not sure if it's a version thing (I'm using 2013), but I got the error message described, using the sample file.

Hi Fluff
Your message prompted me to download the file again and now I do get the error. Comparing both the filter (not filtered in the original sample) and some of the values in column A of 'sys', it appears that the sample file has been replaced since I first downloaded it.

I think you are right about the filter issue.

I wonder where the OP is actually trying to paste the data?

 

[Fluff]

I wonder where the OP is actually trying to paste the data?

I'm guessing that it should be below the existing data, otherwise why use "lastrow"?

 

[Peter_SSs]

I'm guessing that it should be below the existing data, otherwise why use "lastrow"?

You may be right, but then Range("A9249:A20000").End(xlUp).Row is an unusual code so I am not sure you can confidently deduce much from the code.

 

[Fluff]

You may be right, but then Range("A9249:A20000").End(xlUp).Row is an unusual code so I am not sure you can confidently deduce much from the code.

That's a very good point.