Looking to paste without changing the format of the cells pasted into

[Blakearino]

Any assistance on this macro would be appreciated. I have searched the forum and not found anything. I have an Excel 365 Macro that I want to Paste Special into a range in a protected worksheet. I manually preselect the values in another spreadsheet to be copied before running the Macro. I only want the values pasted so I use this. However when it completes, the highlighting and protection (Locked) are changed for the range of cells. How can I just paste the values? Below is what I have tried.

Sub Macro9()
'
' Macro9 Macro
'
' Keyboard Shortcut: Ctrl+m
'


Application.ScreenUpdating = False


Sheet1.Unprotect Password:=""
Worksheets("Sheet 1").Range("E15:E110").PasteSpecial xlPasteValues


Application.ScreenUpdating = True
Sheet1.Protect Password:=""
End Sub

[cwojtak]

Does below work?


Worksheets("Sheet 1").Range("E15:E110").Value = Worksheets("SheetCopiedFrom").Range("Range Copied From").Value

[Blakearino]

Does below work?


Worksheets("Sheet 1").Range("E15:E110").Value = Worksheets("SheetCopiedFrom").Range("Range Copied From").Value

Hi cwojtak thanks for the quick response. This needs to be manually copied and then put into the spreadsheet by the macro.

[snb]

Sheet1.Range("E15:E110")=selection.value

[Blakearino]

Thanks snb. I tried that with no observable results. It compiled and ran with no issues, but I could not see anything changing.
I tried Sheet1.Range("E15:E110").value=selection.value with the same result.

[p45cal]

I tried that with no observable results. It compiled and ran with no issues, but I could not see anything changing.Possibly had a single blank cell selected before it was run?

[Blakearino]

Thanks p45cal. I tried it again and it does remove any thing in the column that was there. If I click on the top cell and paste matching formatting or paste special text the column populates. Not sure where to go with this. I can buff this out by reapplying the highlight and unlocking the cells in code, but I was hoping to figure this out.

[Blakearino]

Could this be related to the protected status of the worksheet or the protected status of the macro project? The worksheet gets unprotected before the rest of the macro runs.

[p45cal]

A guess:
Sheet1.Range("E15").Resize(Application.Min(Selection.Rows.Count, 96)).Value = Selection.Value

When it fails, what's the format of the cell E15?

[Blakearino]

A guess:
Sheet1.Range("E15").Resize(Application.Min(Selection.Rows.Count, 96)).Value = Selection.Value

When it fails, what's the format of the cell E15?

Cell E15 value is erased but retains yellow highlight and is not locked.