Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

How to enable Copy/Paste on protected work sheet?

I have locked all the cells that have coding and protected the worksheet,
How can i allow a user to copy & paste cells these cells together with its
functionality.

Thanks in advance.


Post your answer or comment

comments powered by Disqus
I have protected 3 work sheets and allow users to edit certain ranges, no
problem here, Question is how can you prevent the warning messages (that read
the sheet is protected etc....) appearing. Every time a user clicks on a
protected cell the messages appear. may not sound a problem though it is a
nuisance and a constant distraction for those users who are not familiar with
the sheet. Any help would be appreciated.

how to disable copy paste buttons from from edit menu only for a given
workbook?

Dear all,

Pls help me how to disable Copy or Move function of sheets,worksheets when right click on them

Thanks in advance
vumian

Hi All,

My application protects all the sheets and the workbook by the
password.

I have couple of cells which are not locked so that the user can enter
the data.

But in these unlocked cell I do not have the right click functionality
or copy paste functionality enabled.

How to enable the copy paste functionality on these unlocked cells
where user can enter the data.

I also want this copy and paste to be enabled so that i can copy the
data from different work book onto these unlocked cells of this
application

Please guide.

Thanks and Regards,

Yuvraj

Hi All,

Can you please help on my userform because I cannot use the copy/paste in the userform that I created.
Can you please help on how to use copy paste in a userform so that I can copy the output that the userform produce.

I attached the excel for your reference.

Thanks,

klowjet

Hi,

I want to create a protected sheet, but which allows to use copy and paste functions. Not sure if it is possible at all.

I was testing the following methods (suggested in another thread) to disable copy/paste on a particular worksheet -- now copy and paste is disabled entirely from Excel and, apparently, from each and every excel workbook I've got. HELP!!!!!

http://www.xcelfiles.com/VBA_Quick13.html

http://www.mrexcel.com/archive2/75500/87639.htm

Taxstar

I am using Office 2003 & would like to copy & paste on a Excel sheet a
webpage with coloums containing text but also with hidden formulas.

I would like the hidden formulas to be deleted from the excel sheet. due to
this hidden formulas the worksheet takes time to open as the size increases
to Mb.

Would apprecaite your help.

I am using Excel 2007 and I have a problem in drop down list. My drop down list contains 15 kinds of ticket types. By default, one can't write anything additional in the given drop down list, but he can copy any cell or text and paste in the drop down list. What I actually want is that one can only be able to copy paste the given 15 ticket types and he can't add anything extra. A user has to fill many rows and it would not be easy for him to select manually from drop down for each row, so I want an user to be able to copy any ticket type from those 15 types and paste in the multiple rows simultaneously. But he should not be able to paste or add anything extra for this drop down column.

In short, I want to allow copy paste but there should be a check in the drop down which does not allow to paste data other than the contents of list.

I hope I am able to make my problem understand to everyone. Please help with the solution/macros.

I am using Office 2003 & would like to copy & paste on a Excel sheet a
webpage with coloums containing text but also with hidden formulas.

I would like the hidden formulas to be deleted from the excel sheet. due to
this hidden formulas the worksheet takes time to open as the size increases
to Mb.

Would apprecaite your help.

Hi, I have worksheet containing 20 link sheets.

I want to see the result on the similar sheet by changing values which effect the other worksheets.

Can I make a link text box or any other tool with my resultent sheet to see the result on the working sheet at a glance. If could help me a lot if I need to variy the figures to get the required result.

Many thanks in advance.

Rgds

I'm looking for a way of bringing in data from another workbook into the currently one without too much screen flickering using Excel 2007.

Currently, my code works fine with bringing the data in. But the series of actions (open the source file, select/copy data range, unprotect and clear target file's range, paste data to target, and close source file) cause differet sheets to activate back & forth. The flickering is caused by the requirement of Copy/Paste statements being stuck together. If I can do a Copy-Unprotect-ClearContents-PasteSpecial, I will only need to visit the source file once. However, since PasteSpecial only works when immediately after Copy, I have to Unprotect-ClearContents on the target sheet, go to source to copy, then come back to target again to paste.

The key question is - is it possible to do Unprotect, ClearContents, Select, Copy, PasteSpecial, and Protect all in the background without activating the sheets involved? Or, is ther any other way to get the data in without using these functions? I know my users want a tool that will work "smoothly & quietly" without seeing too many screen flashes.

Thank you in advance for your ideas!

Ideal scenario:

1. Click "import" button in ThisWorkBook's Main sheet.
2. Browse and select sourceFile, detect range for copying.
3. Unprotect "CFDS Report" sheet in ThisWorkBook, clear 4 columns, copy/paste sourceFile data, protect sheet, then close sourceFile.
All 3 steps should be done behind the scene with Main sheet being displayed the whole time.

Here is my code:

Private Sub ImportReport()

Dim i, endSourceRow As Integer
Dim temp, sourceRange, sourceFile As String

Application.DisplayAlerts = False
sourceFile = Application.GetOpenFilename("Excel files (*.xls; *.xlsx), *.xls; *.xlsx")

If InStr(sourceFile, "False") = 0 Then  'if user browses and selects a file to import

    '-----------------------------------------------------------------
    ' 0 - Clear contents of the target tabs
    '-----------------------------------------------------------------

        ThisWorkbook.Worksheets("CFDS Report").Unprotect
        ThisWorkbook.Worksheets("CFDS Report").Range("A:D").ClearContents
               
    '-----------------------------------
    ' 1 - Open user selected source file
    '-----------------------------------

    Workbooks.Open Filename:=sourceFile, AddtoMRU:=True, Editable:=True
    'Reset source file to contain just the name without path
    sourceFile = Application.ActiveWorkbook.Name
        
    '---------------------------------------------------
    ' 2 - Find out import data range from source file
    '---------------------------------------------------

    'Last line of data contains "Total:"
    i = 5
    Do While i > 0
        temp = Workbooks(sourceFile).Sheets(1).Cells(i, 1).Value
        If Trim(temp) = "Total:" Then
            Exit Do
        End If
    i = i + 1
    Loop

    'Now endSourceRow contains the bottom range for copying
    endSourceRow = i
    sourceRange = "A1:D" & CStr(endSourceRow)

    '-----------------------------------------------------------------
    ' 3 - Copy the source data range from source file
    '-----------------------------------------------------------------

     Workbooks(sourceFile).Sheets(1).Range(sourceRange).Select
    Selection.Copy
    
    '---------------------------------------------
    ' 4 - Paste the selection to the target file tab
    '---------------------------------------------
    
    ThisWorkbook.Worksheets("CFDS Report").Range(sourceRange).PasteSpecial Paste:=xlPasteAll
    ThisWorkbook.Worksheets("CFDS Report").Protect
    
    '----------------------------------------------------
    ' 5 - Close the source file and display Main tab
    '----------------------------------------------------
     Workbooks(sourceFile).Close SaveChanges:=False
    ThisWorkbook.Worksheets("Main").Activate

     Application.DisplayAlerts = True

Else

MsgBox "You didn't pick a file."

End Sub


How to enable spell checking when you protect a Word 2007 Template?

I am creating a Microsoft Word 2007 template which needs to be Protected so that user's can open the template and enter their data into the fields then save the data. When I protect the document I notice that now the Spelling & Grammar options are unavailable to be run. I have tried creating a Macro to run on Exit which doesn't run when you exit the template. Please help!

Hi All,

I am new to VBA and this forum, but have already learned a lot since joining!

I think there are a few ways to do what i'm trying to do and my first way may not be working. I want to copy/paste between different sheets based on a value in the row. For example, if I have these rows:

Company New/Exist Communication Status
Business1 New No contact
Business2 New No contact
Business3 New No contact

And then I update the communication status (which will happen for multiple rows), then I want to move it to a different sheet. The columns do not exactly align, so I'd paste each column at a time. There are also two other sheets, for "Contacted" and "Negotiating", so I want to move them into the appropriate sheet based on the Communciation status.

The code I have so far is as follows:


	VB:
	
 
Sub UpdateProspects_2() 
     
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Application.StatusBar = "Working..." 
     
    Dim SetWbkName As String 
     
    Dim LRowProspects As Long 
    Dim LRowContacted As Long 
    Dim HRow As Long 
    On Error Resume Next 
     
    SetWbkName = ThisWorkbook.Name 
     
    Sheets("Prospects").Select 
    LRowProspects = Range("A1048576").End(xlUp).Row 
    HRow = Range("A8").Row 
     
    Sheets("Contacted").Select 
    LRowContacted = Range("A1048576").End(xlUp).Row 
     
    Sheets("Prospects").Select 
    Range("A8").Select 
    Range(Selection, Selection.End(xlToRight)).Select 
     
    If Not ActiveSheet.AutoFilterMode Then 
        Selection.AutoFilter 
    End If 
     
     '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     
    ActiveSheet.Range(Cells(8, 1), Cells(LRowProspects, 13)).AutoFilter Field:=4, Criteria1:= _ 
    "2 - Interested" 
     
    Range(Cells(HRow + 1, 1), Cells(LRowProspects, 3)).Copy Destination:=Sheets("Contacted").Cells(LRowContacted,
"A").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 4), Cells(LRowProspects, 8)).Copy Destination:=Sheets("Contacted").Cells(LRowContacted,
"E").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 9), Cells(LRowProspects, 10)).Copy Destination:=Sheets("Contacted").Cells(LRowContacted,
"K").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 11), Cells(LRowProspects, 13)).Copy Destination:=Sheets("Contacted").Cells(LRowContacted,
"P").Offset(1, 0) 
     
    Sheets("Contacted").Select 
    LRowContacted = Range("A1048576").End(xlUp).Row 
     
    Range(Cells(7, 1), Cells(7, 45)).Copy 
    Range(Cells(7, 1), Cells(LRowContacted, 45)).PasteSpecial Paste:=xlFormats 
     
     'Delete raw data to save space
    Sheets("Prospects").Select 
     
    With Range(Cells(HRow + 1, 1), Cells(LRowProspects, 13)) 
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete 
    End With 
     
     '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    ActiveSheet.ShowAllData 
     
    Application.StatusBar = False 
     
End Sub 
 
 
Sub UpdateProspects_3() 
     
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Application.StatusBar = "Working..." 
     
    Dim SetWbkName As String 
     
    Dim LRowProspects As Long 
    Dim LRowNegotiating As Long 
    Dim HRow As Long 
    On Error Resume Next 
     
    SetWbkName = ThisWorkbook.Name 
     
    Sheets("Prospects").Select 
    LRowProspects = Range("A1048576").End(xlUp).Row 
    HRow = Range("A8").Row 
     
    Sheets("Negotiating").Select 
    LRowNegotiating = Range("A1048576").End(xlUp).Row 
     
    Sheets("Prospects").Select 
    Range("A8").Select 
    Range(Selection, Selection.End(xlToRight)).Select 
     
    If Not ActiveSheet.AutoFilterMode Then 
        Selection.AutoFilter 
    End If 
     
     '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     
    ActiveSheet.Range(Cells(8, 1), Cells(LRowProspects, 13)).AutoFilter Field:=4, Criteria1:= _ 
    "3 - Negotiating Deal" 
     
    Range(Cells(HRow + 1, 1), Cells(LRowProspects, 3)).Copy Destination:=Sheets("Negotiating").Cells(LRowNegotiating,
"A").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 4), Cells(LRowProspects, 4)).Copy Destination:=Sheets("Negotiating").Cells(LRowNegotiating,
"E").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 5), Cells(LRowProspects, 8)).Copy Destination:=Sheets("Negotiating").Cells(LRowNegotiating,
"X").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 9), Cells(LRowProspects, 10)).Copy Destination:=Sheets("Negotiating").Cells(LRowNegotiating,
"R").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 11), Cells(LRowProspects, 13)).Copy Destination:=Sheets("Negotiating").Cells(LRowNegotiating,
"AB").Offset(1, 0) 
     
     
    Sheets("Negotiating").Select 
    LRowNegotiating = Range("A1048576").End(xlUp).Row 
     
    Range(Cells(52, 1), Cells(52, 45)).Copy 
    Range(Cells(52, 1), Cells(LRowNegotiating, 45)).PasteSpecial Paste:=xlFormats 
     
     'Delete raw data to save space
    Sheets("Prospects").Select 
     
    With Range(Cells(HRow + 1, 1), Cells(LRowProspects, 13)) 
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete 
    End With 
     
     
    ActiveSheet.ShowAllData 
     
    Application.StatusBar = False 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
My first attempt was to filter the data, copy the visible range, then paste in new tab. However, it works the first time, but usually not the second time. I dont know if its just the volatility associated with this method, in which case, maybe I'd have to sort/copy/paste?

Any help would be greatly appreciated!

Good morning everyone,

I hope someone knows a solution to my dilema...

I have a spreadsheet which is updated weekly, copying and pasting product data from another source.

I have a macro to do all this copying and pasting and figured out (for security purposes) how to disable printing.

I have noticed another security issue where the user can copy the data into another workbook and print from there, so I used the following code to disable the copy function altogether, which works a treat! (see below):

Private Sub Workbook_Activate()
Application.CutCopyMode = False
Application.OnKey "^c", ""
End Sub

Private Sub Workbook_Deactivate()
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.CutCopyMode = False
Application.OnKey "^c", ""
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
MsgBox ("Right click menu is deactivated.")
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.OnKey "^c", ""
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Application.CutCopyMode = False
End Sub

My dilema IS that my macro which copies the data from another source now falls over, because the above code prevents the copy function.

Can someone please advise the code to enable copying where required?

Thanks in advance!!

NoniJones

I am looking to “auto filter" or "auto copy & paste" from a "Master"
Spreadsheet on to another spreadsheet. Ex: I have a "Master" for all "Live"
and "Completed" jobs, but I need to have a separate sheet with the
"completes". How Can I do this without copying and pasting every time I enter
a new location? I need it so that I can just update the "Master" and it will
automatically copy and paste it (or filter it) through.
Any help would be appreciated.

Thanks,
Armando

Hello -

I set up a spreadsheet with drop-down menus for some of the cells. The
user can only select values from that list and an error message pops up
when something is typed it that is not in the list (via Data Validation
> Error Alert).

So this works all fine ... except if the user copies and pastes a cell
with a different drop-down menu into a cell overwriting the existing
validation.

I cannot totally lock the worksheet and if I lock the cells with the
drop-down menus nothing can be changed. So how can I prevent this from
happening? Is there a way to block all copy/paste operations that are
copying the validation? I know that with the Paste Special I can have
cells copied without the validation.

Thanks for any comments!
Joe

Hello All,

I have a workbook where i have around 9 worksheet. I have patient details on
worksheet name "ALL". I have a dropdown menue in main worksheet "ALL". There
are 8 items in the drop down menu. I have created 8 worksheet with same name
as items in dropdown menue.

My requiremnet is to copy and paste the specific Data coulmn to the another
worksheet for which dropdown item is slected.

Say i have List in dropdown menu as A, B, C,D,E,F. I have also created
worksheet as A, B,C,D,E,F. If a user select A from the dropdown menue the
entire Data couln should be copied into worksheet A and same for all other
menue item.

How can i do it ?

Thanking You

Regards

Niraj

Hi, I have very little to no knowledge of VBA scripting, therefore would like an Excel Guru to help.

I have a Macro-enabled workbook that has 3 worksheets:
1. Show Me Profile: Main sheet with Search Button
2. Project List: will be a hidden worksheet for the Drop Down Menu on main sheet (no relevance to VBA issue)
3. Total Forecast: Master Data where the the Main sheet will grab information from

Example of what I'd like the VBA script to do:
On the "Show Me Profile" Worksheet:
- User has a Search Box (Cell B5), they type in "Great Plains" into B5.
- Hits the "Search" button
- VBA script on "Search" button will look for "Great Plains" (or whatever is in B5) in F Column of "Total Forecast" Worksheet.
If there is a match:
- It will Copy that entire row, Paste the entire row into Row 10 of ""Show Me Profile" Worksheet.
-Then it repeats the "Find, Copy & Paste" function until no further matches are found in Column F of "Total Forecast" Worksheet.

At the end of it all, the user should have a Rows of Information, based on their Search Criteria in the "Show Me Profile" Worksheet.

Hope this makes sense. Any help would be greatly appreciated.

Regards,
Tara.

How do I enable the changing of font color tool and still protect a worksheet
in Excel 2000? Protection is necessary for formulas in other areas of the
worksheet, but we need to be able to chnge the font color without
unprotecting the worksheet

Hello all.

I have a little vba module that uses an external dll to automatically select a cell in one of the worksheets.
The user then enters a value into the cell and the external dll selects another cell. The user enters then a different value in the new selected cell.
This works pretty well. However, the values in the cells often only differ in the last one or two characters, which are usually numbers.
So, it would be great to let the user copy the first entered value and paste it into the next selected cell. But when I use Strg+V in the new cell nothing gets pasted. How would I enable copy paste?
Ideally, I would also like to set the cursor into the selected cell at the end of the pasted value. So that the user doesn't have to set the cursor manually.
Is this possible at all? I didn't find any setCursor function or something similar.

Any hints or tipps are appreciated.
Best regards,
heapifyman

Hi everyone,

I have a template that takes a Start Date and End Date that uses the =weeknum() function to generate a corresponding "Start Week" and "End Week".

Since this eventually gets saved as a CSV that is read by another system we have, I have to manually copy and paste all the corresponding information for the entire range of dates.

So for example, if the event starts Week 1, and ends Week 5, I need to duplicate that line so that I have 5 identical rows of data with the only exception being the Week number.

Can anyone think of a way to streamline this process, or maybe create a template that would help with this.

Sometimes I'll get lists of 5 events, which can be done in a few minutes, but when I get lists of 200 of these...it becomes a real time suck, and I can't help but feel like there is a better way around this.

Please see attached for an example of what the file I receive looks like, and how I need the output to look like in the end.

Thanks in advance for any help!

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

As part of an ISO process metric I want to track backlog of parts in a certain department. Luckily, there is a spreadsheet already in use that is updated each day. I modified that sheet so that the backlog of parts is calculated and summed into a single cell. What I need now is for that number, each day, to be logged or listed on a separate sheet. Copy/paste would work but shop guys will struggle. I hope I can get advise on how to automate. Scenario: Shop updates the working sheet first thing. (my backlog sum changes) They click a macro button and the changed sum is copied to sheet 2, column A, next available cell. After 30 days of updating the working sheet and running the macro I can look at sheet 2 and see 30 entries in column A, each corresponding to the backlog for that day. Help on getting this done is much appreciated.


No luck finding an answer? You could always try Google.