Free Microsoft Excel 2013 Quick Reference

Copy paste Macro in a Protected Sheet

Hi Guys!!!

I have a spreedsheet that has a checkbox with a macro and I want to
protect the spreadsheet to protect the formulas from novices users.

I have figure out all the steps of unlock the cell that I want the
user to enter the data, unlocked the cells that link to the check box.
The PROBLEM is that I have a range that a macro has to copy and paste
but I want the range to remain lock for the users but gets unlock when
the macro needs to copy and paste.
this is the code that I have right now:
WHAT DO I NEED TO write to allow the macro to unlock, copy, paste and
lock the file again.

Sub CheckBox1107_Click()
'
' CheckBox1107_Click Macro
'
If ActiveSheet.Range("D19").Value = True Then

Application.Goto Reference:="R16C4"
Range("D1618").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("H9").Select
ActiveCell.FormulaR1C1 = "0"
Range("H11").Select
ActiveCell.FormulaR1C1 = "0"
Range("H12").Select
ActiveCell.FormulaR1C1 = "0"
Range("D12").Select
Application.Goto Reference:="R16C4"
Else
Application.Goto Reference:="R45C2"
Range("B43:B45").Select
Range("B45").Activate
Selection.Copy
Application.Goto Reference:="R16C4"
Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.Goto Reference:="R16C4"
End If
End Sub

WHAT DO I NEED TO write to allow the macro to unlock, copy, paste and
lock the file again.
Thank YOU,

PAMELA xoxo


Hello,

I have a password protected worksheet, in which I built MACROS to sort some columns from a-z and from z-a.
When trying to built in a MACRO for
- the CLEAR function (filters, if applied) -> X
- deleting a full record (line) -> Y

with including the unprotect and protect function in VBA:

ActiveSheet.Unprotect Password:="pass"

... MARCRO X and Y ...

ActiveSheet.Protect Password:="pass"

I LOSE MY AUTOFILTER OPTION!

What to do in order to USE MACROS in a PROTECTED SHEET without loosing AUTOFILTER option?

Puskas1978

Hi,

I have a Protected worksheet having 4 columns whe
1. Column A and B is Un-Protected and
2. Column C and D is Protected with Formulae.

However, in column A, I have "MM/DD/YY" Format and in Column B I have a
Drop-Down list.

I don't want to mess up my column A Format, since people can copy/paste
cells from column B or a different Format from another sheet into cells in
the Column A.

Please help me out to de-activate the Copy/Paste or Cut/Paste function so
that only Manual entry is possible in my Protected Sheet.

Your help will be greatly appreciated.

Thanks in advance,
Prashanth KR.

If i protect a worksheet the macro is not executing. it is giving error
message as
Run-Time Error 1004
You cannot use this command in a protected sheet.

kindly suggest the procedure to execute the macro in a protected sheet. i
want to protect some cells in the sheet from being uptaion.

1. How to hide rows in a protected worksheet without unprotecting the
worksheet.
2. Is it possible to delete rows in a protected worksheet without
unprotecting the worksheet.
3. My requirements is that in a worksheet, I have input certain formulas
and the same is protected with a password, I will be sending these sheets to
my colleagues to fill in data and the formulas will automatically calculate
the results. I dont want them to modify the protected ranges.

This protection will be for certain columns for the entire worksheet. In
case if they print then there are totals at the end of the sheet which also
has to be printed but I dont want empty rows without data not to be printed.

My querry is that is it possible to hide or delete empty rows in a protected
sheet without unprotecting the same.

4. These sheets will be used regularly as templates is it possible for them
to store the same in one file with different worksheet. In other words they
will copy and paste the template in the same file in different sheet but I
need the protection to continue is it possible.

5. Is it possible for someone to unprotect the sheet without the owner
disclosing the password. In other words is it possible to break the password.

Expecting your immediate response on the same.

regards

Prakash

I am trying to create a macro to run with a button click that will refresh a pivot table that is located in a protected sheet. I had created the macro and it worked before I protected the sheet. Here is the code I have for it right now:

Sub OptionButton4_Click()

End Sub
Sub boxpivottablerefresh()
'
' boxpivottablerefresh Macro
' Refreshes the Boxes Pivot Table
'
' Keyboard Shortcut: Ctrl+Shift+P
'
    Range("K3").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    MsgBox "Box quantities have successfully been calculated.", vbExclamation + vbInformation, "Calculation
Complete"
End Sub
Can someone please tell me the correct code to enable this function to work even when the sheet is protected? When I press the button right now, a pop-up appears that says:

"Run-time error '1004':

Cannot edit PivotTable on protected sheet."

With options to End, Debug, and Help.

Thanks in advance for the help!
Gavin

Hi I am trying to do a copy paste macro for a spread sheet a data
import that skips over a few fields. The spreadsheet is about 650 rows
so going through it manually is very tedious. I want the macro to be
able to copy paste when column "A" is blank the date from the row above
in columns A,B & D, E, F. It would look like this:

A B C D E F
as ad af ag ah ak
IT LO

After macro is run - seeing that column A is blank it would copy paste
from above to looke like this - Notice it copys over the previous
contents of column D and leaves column C alone.

A B C D E F
as ad af ag ah ak
as ad IT ag ah ak

Any ideas on how I could get this to work?

Thanks alot!

-Brian

Hello
I'm quit new to macro & vba for Excel and i got problem with my textbox in a protected sheet. When the protection is on, i can't enter data on my texbox's, more than one line. If i press the enter key to move one line under, the cursor just go to the next availabale, unprotected cell. My textbox protection as been uncheck in the propriety of it.

Excuse my English, i'm more fluent in French

Hello,

I am trying to find a way to past an object I have captured as a .png file
on a
protected sheet. I have protected the sheet so as to be able to navigate by
tab
or arrow only to cells I have unlocked. On the sheet, I have a group of cells
with the caption "Past object here" This is were I past the image I have
captured.
Trouble is, I can only paste on an unprotected sheet! Is there some way to
get arround this while leaving the sheet protected? Perhaps some way to
display
the contents of another sheet where I past images to be displayed on the
protected
sheet?

I appreciate any help with this. :-)

Hello,

I'm a compleet newbie with VBA. I've made a macro for a button but it doesn't work in a protected sheet.
Is it possible that the protection is removed in the macro, then the macro must be carried out and then the macro must put the protection back on the sheet.

This is the recorded marco

Sub Herberekening()
'
' Herberekening Macro
' Macro recorded 18-3-2005 by Rimco
'
' Keyboard Shortcut: Ctrl+q
ActiveWindow.SmallScroll ToRight:=14
Range("V3:AY3").Select
Selection.AutoFill Destination:=Range("V3:AY1002"), Type:=xlFillDefault
Range("V3:AY1002").Select
ActiveWindow.SmallScroll Down:=-12
ActiveWindow.ScrollRow = 1
ActiveWindow.SmallScroll ToRight:=-34
Range("A3").Select
End Sub

I hope you can help me

Thanx m8's

Hello,

I am trying to find a way to past an object I have captured as a .png file
on a
protected sheet. I have protected the sheet so as to be able to navigate by
tab
or arrow only to cells I have unlocked. On the sheet, I have a group of cells
with the caption "Past object here" This is were I past the image I have
captured.
Trouble is, I can only paste on an unprotected sheet! Is there some way to
get arround this while leaving the sheet protected? Perhaps some way to
display
the contents of another sheet where I past images to be displayed on the
protected
sheet?

I appreciate any help with this. :-)

hello out there anyone

does anyone know in excel how to insert a row in a protected sheet without unprotecting the sheet. i do not want any one to delete my formula.

please help

thanks to everyone.

rennoyinc

Hi all,

I would like to hover over a chart in a protected sheet in Excel 2007. This is a feature in Excel 2003 but in Excel 2007, i don't know how to have this feature.

How to test this:

- create a simple chart and protect the sheet where the chart is. Then hover the mouse over the chart and see if it gives the number in the chart.

thanks in advance.

I recently protected sheets within a workbook but now, none of the macro which refer to those worksheets, work. I am looking for a way to make this happen.

Should I unprotect each sheet before it is called and then protect it after the macro is done? If I do this, can someone break while the macro is running and have acces to the sheet?

Is there a command or method that will let me run a macro against a protected sheet?

Hi

I would be obliged if anybody can help me to do the following task:

"how to unprotect radio buttons in a protected sheet"

Thanks

Shaivi Bansal

Hello,

Application: Excel XP SP2
OS: Win XP 5.1.2600 SP1

Is it possible to group and ungroup in a protected sheet.
I tried several approaches provided by the sheet protection dialog,
but I didn't succeed.

Hiding and unhiding rows is possible, even in a protected sheet,
and therefore I thought it should be possible to group and ungroup
in a protected sheet as well.

Regards
Werner

Is there anyway to disable the copy/paste function in a cell in an excel spreadsheet but still be able to edit/type in the same cell?

For example, I want to set up a data entry spreadsheet where a user has to enter the same information twice (example - cell A1 and B1) to stop keying errors, there is a warning message in cell C1 that warns if cells A1 and B1 do not match but i do not want the user to simpily copy and paste the contents of A1 into B1, therefore negating the purpose ok the check column.

Any ideas would be a great help.

Thanks

Sam

Hi everybody,
In Excel 2003 a user can add comment in a protected sheet, if editing
objects is allowed. I have to make it possible also in Excel 2000 with VBA
code. I´m thinking of using a userform with textbox but maybe there is some
better way.
Thanks in advance for any suggestion.
Best Regards
Mats
--
ESI Update

Am not able to create a comment on an unlocked cell in a protected sheet.
Creating comments is not one of the features I can enable when protecting the
sheet. Please advise if you've faced/solved this problem before.

I have a worksheet which is protected. When I am searching anything by pressing Control+F command it will show a message “Microsoft Office Excel Cannot Find the Data You’re Serching For” and does not find anything. How can I search anything in a protected sheet please let me know.

Is there a way to prevent excel users from moving data in a protected sheet? In other words, allow only data input on a protected sheet?

Hello Everyone,

Apparently, I don't fully understand Excel's copy / paste behavior in a table.

Issue #1:

When I enter or change the content of a table cell, sometime Excel offers to copy the new content to the whole column, and sometimes it doesn't offer. It drives me nuts that I can't anticipate or control when that invite is extended. I feel like I'm waiting behind a velvet rope for my spreadsheet to find me worthy!

So, can someone please explain when I can depend on getting the "copy down column" offer. I wish it would make that offer every time I make a manual change to any table cell in any manner!

Issue #2:

Given that I often have to manually copy a new formula down 16,000 rows of data, what is the fastest way to do this? Copy - highlight - paste is driving me nuts! It takes over 20 seconds! Aren't there methods involving hot-keys or something? I remember reading something about this, but after 5 minutes of searching I still can't find the answer.

Cheers,

Jay

I am using a validation list-combo box code solution to manage the font size and the number of items that are in the list that is displayed. This works fine while the sheet is not protected. I double click on the cell with the validation list and the combo box code kicks in to display the modified list. When I protect the worksheet, double clicking on the same cell with the validation list does not activate the combo box. The combo box code is attached to the protected sheet. The cell in question is not protected. Can anyone tell me how I can still use this in a protected sheet.

Thanks

Is there anyway to disable the copy/paste function in a cell in an excel
spreadsheet but still be able to edit/type in the same cell?

For example, I want to set up a data entry spreadsheet where a user has
to enter the same information twice (example - cell A1 and B1) to stop
keying errors, there is a warning message in cell C1 that warns if
cells A1 and B1 do not match but i do not want the user to simpily copy
and paste the contents of A1 into B1, therefore negating the purpose ok
the check column.

Any ideas would be a great help.

Thanks

Sam

--
sam

Any ideas on how I can refresh a pivot table in a protected sheet? Do I have to unprotected the sheet first? If so is there any way around this, as I do not want others adjusting my formulas feeding the pivot tables.