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

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.

Code:

---

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

---

Does below work?

Code:

---

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

---

Quote:

---

Originally Posted by cwojtak

Does below work?

Code:

---

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.

Code:

---

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

---

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.

Quote:

---

Originally Posted by Blakearino

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?

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.

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.

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?

Quote:

---

Originally Posted by 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?

---

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