PasteAsValues into selection

[torinjr]

Hi There,

I have the following code pasted below attached to a button:
This is really annoying me because the code works if the worksheet is unprotected BEFORE running the macro.
However if the worksheet is protected the code produces the following error '1004' Paste special method of range class failed.
***Note that values have been copied on an outside sheet and this macro is only being run with copied cell data.

Please help.

Sub InputData()

'Unprotect sheet to allow formula to be pasted in locked cells
Worksheets("Sheet1").Unprotect Password:="MontyBurns69!"

'Unhide sheet and hide homepage sheet
Sheets("Sheet1").Visible = True
Sheets("Sheet1").Select
Sheets("DatabaseHomepage").Visible = False

'Select the cell after the last entered row.
Range("A" & Rows.Count).End(xlUp).Offset(1).Select

'Paste data that was copied from outside spreadsheet as values into the selected cell.


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Reprotect Worksheet
Worksheets("Sheet1").Protect Password:="MontyBurns69!"


End Sub

 

[JoeMo]

This may be an alternative to unprotect at the start of the macro. Replace this line:

Worksheets("Sheet1").Unprotect Password:="MontyBurns69!"

with this:

Worksheets("Sheet1").Protect Password:="MontyBurns69!", UserInterfaceOnly:= True

and remove the line:

Worksheets("Sheet1").Protect Password:="MontyBurns69!"

just before the End Sub

 

[torinjr]

Thanks but still same error.

 

[JoeMo]

Does the error not occur if you unprotect the worksheet? Have you verified that the data is on the Excel clipboard when the paste special line causes an error?

 

[torinjr]

If I unprotect the sheet the error does not occur and it works perfectly.
Maybe protecting/unprotecting the sheet has something to do with uncopying the selected data. How do I work around this???

I just did a quick sense check after the error I am unable to paste the data as it seems to be no longer copied.

 

[JoeMo]

If I unprotect the sheet the error does not occur and it works perfectly.


I just did a quick sense check after the error I am unable to paste the data as it seems to be no longer copied.

What's wrong with protect/unprotect if it works perfectly?

 

[torinjr]

I don't know I was wondering if running the protect macro will cause the copied data on the clipboard to uncopy?

Anyway I have an additional question:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">lastCol = ActiveSheet.Range("a3").End(xlToRight).Column
lastRow = ActiveSheet.Cells(5001, lastCol).End(xlUp).Row
ActiveSheet.Range("a3", ActiveSheet.Cells(lastRow, lastCol)).Copy</code>I want to copy a range of cells starting in cell A3. Select all of the filled cells below (row A only) and all the columns (In this case columns A:AB).

My issue is that my data goes from columns A to AB which is fine. But my data in columns T and W the data extend down to row 5001 .

Can you please tell me how to modify or give new code so that the copied range only cells to the end of column A and not all the way to row 5001.

 

[StephenCrump]

Maybe protecting/unprotecting the sheet has something to do with uncopying the selected data.

Yes, the problem is that the clipboard is clearing, so there is nothing to paste.

What's wrong with protect/unprotect if it works perfectly?

I think torinjr is saying the code works if the worksheet is already unprotected, but a VBA .Unprotect statement clears the clipboard.

There are ways around this, e.g. JoeMo's suggestion:

Worksheets("Sheet1").Protect Password:="MontyBurns69!", UserInterfaceOnly:= True

should work if it's in another module, e.g. perhaps in a Workbook_Open.

But in general terms, you want to copy and paste without a lot of intermediate steps. How are you copying your values from "an outside sheet". Is this something that could be done within the current code?

 

[StephenCrump]

Anyway I have an additional question:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">lastCol =ActiveSheet.Range("a3").End(xlToRight).Column
lastRow =ActiveSheet.Cells(5001, lastCol).End(xlUp).Row
</code>

Not totally clear, but guessing you want:

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

(It would be better to refer to the worksheet by name rather than relying on ActiveSheet)