View Full Version : Looking to paste without changing the format of the cells pasted into
Blakearino
02-17-2022, 02:32 PM
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
02-17-2022, 04:26 PM
Does below work?
Worksheets("Sheet 1").Range("E15:E110").Value = Worksheets("SheetCopiedFrom").Range("Range Copied From").Value
Blakearino
02-17-2022, 06:05 PM
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.
Sheet1.Range("E15:E110")=selection.value
Blakearino
02-19-2022, 09:35 AM
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
02-19-2022, 11:30 AM
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
02-19-2022, 06:06 PM
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
02-19-2022, 06:37 PM
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
02-19-2022, 06:50 PM
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
02-19-2022, 07:28 PM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.