Force Macro To Remove Cut, Copy And Paste Options
Feb 18, 2010
I have been using excel for years but have always managed to resolve issues using formula's etc but now i have had to step into the realms of macro's. I am slowly learning but urgently need help with the below problem which is bound to be a simple solution.
I have compiled a workbook for users to administrate on a daily basis. (each worksheet is a different day of the month but the same template) There are a few hidden worksheets as the info is extracted from these to run reports.
The issue/s i have is that administrators use cut, copy and paste to repeat data opposed to retyping, this messes formats, formulas and validation up. I have used a macro from the net to remove this option and it works fine. Problem is that if they do not enable macro's this will cease to be an option.
I understand that you cannot remove the option of enabling macro's but you can force. Again used a macro from the web that if you don't enable it only shows a welcome page saying that macro's need to be enabled to use. (all others are hidden) if macro's are enabled the welcome page disappears and the worksheets 1-31 reappear. Again this worked fine.
I tried putting both macro's into the same workbook but kept coming up with errors, each one i resolved led to another.
So short of it i need a instructions/macro to force macros and remove cut copy and paste.
View 14 Replies
ADVERTISEMENT
Aug 7, 2007
I need a macro that will take the values that are in the far right cells and move them to an area on the left. the columns they will be pulling from are the IU & IV column starting with row 2 down to row 460. from there I need the values to be pasted into the D & E Columns starting at row 6. The two columns IU & IV are a date and a task for that date. When they are pasted into the columns D&E they will need to be sorted by dates (or just all of the blanks removed), with the soonest occurrence at the top. The reason for pasting values is because I have formulas pulling the tasks and dates off of another sheet. The last thing is that the macro needs to be triggered by the information in cell c2, when that cell is changed the formulas go to work and everything in cells IU & IV update. That is when I would like the macro to kick in and work the miracle.
I have been trying to build a colony of formulas that could do it, but I have given up, then I tried to make an array index it for me, but that wasn't working for me either.
View 9 Replies
View Related
Aug 6, 2007
I have a sheet which is protected but people are still able to change cell formats etc if they use the standard paste method. I want people to be able to paste data in but it forces paste special values to stop people pasting formats etc over already formatted and locked cells.
I know I could write a macro and run it using the shortcut keys ctrl-v but then the users lose the ability to undo if they make a mistake.
Is there a way to force paste special or allow users to run a macro but be able to do undo if they make a mistake?
View 9 Replies
View Related
Sep 4, 2009
After using Copy/Pastespecial in my macro, when it completes I have the flashing border around the range that was copied, (like you do when you do it manually).
What line of code do I need in my macro to make this go away?
View 3 Replies
View Related
Apr 23, 2009
I imported data in to column A and it looks like it inserted a question mark (which I think represents a space or tab )before about half the names.
So when I import the data in my contact manager it inserts a square and throws off the import.
I know it is a simple question but have not been successful.
I am using Excel 2007.
All I want to do is copy and paste the data and remove all those question marks. I would assume it is simple as pasting with "remove formatting" but I am not seeing that option.Plus they are names so I want to keep capitalization etc.
I also tried pasting in a text file to remove formatting and paste in the text file.. (the names ended up indented) and then "paste special" back in to excel with no formatting .which I was able to do in 2000 but with no luck with 2007.
View 9 Replies
View Related
Apr 21, 2009
I have a save button running a macro that validates before saving. Is there a way to remove all other save options (alt f a, ctrl s, the toolbar save button, etc.) but just from this one workbook?
A while ago I tried playing with a code that did this but it did it through out excel.
View 8 Replies
View Related
May 27, 2014
I am looking to see if is possible to copy and paste a worksheet and then remove data( only values not Text) and also not removing formatting and formulas
View 1 Replies
View Related
Jan 29, 2014
I would like to implement specific cell ranges from two specific worksheets each within 33 workbooks (which all have several tabs) into a summary page in a separate workbook.
The cell ranges are going across my spreadsheet in rows and I would like for them to transpose into a columns depending on the data which I have separated by catergory on the summary page. They are all on the same location in each workbook which is separated by country. The cell ranges are E26:P37 and I would like to transpose them and have them put below eachother without overwriting for my format on the summary page, how I can put this together in a macro?
View 1 Replies
View Related
Oct 1, 2012
I have one workbook that needs two macros.
On the "Complete Backlog" tab of my workbook, I want users to enter in the requested information based on the column header. Then I would like a Macro attached to a button that says "Refresh" that the user would click after they have entered in all of the information. This macro should look in Column M (WIP Status) and if any of the cells say "Close", it should Cut the entire row from the spreadsheet(Ex. A2:M2) and Paste it into the speadsheet titled "Closed Jobs".
This is so that as jobs are closed/finished, they are removed and stored on a separate sheet. The items would have to be pasted so that it pastes into the next available row - not just on top of each other.
I also need another macro that i can put into a button that doesn't "delete" a row from the sheet, but just copies over to another sheet - so that there are two instances in the workbook.
If would look something like: If a cell in "Column G / Director" of the "Complete Backlog" speadsheet is equal to "Snodgress" then copy columns A-L of the same row to the spreadsheet titled "Snodgress" - of course skipping down the rows to the next blank row.
.....is equal to "Herr" copy row to "Herr" spreadsheet.
....is equal to "McCormick" copy row to "McCormick" spreadsheet.
and so on.
View 2 Replies
View Related
Sep 12, 2013
I have found instructions on the Net for stopping that rather intrusive 'Paste Options' pop-up button appearing in Excel 2003, 2007 and 2010 (I think; or it might have been 2011). But I have Excel 2008, and none of the instructions I have found applies. I cannot find a Microsoft Office button (other than the icon of the folder in Applications); nor is there 'Options' in the File menu. I use a Mac mini (Intel) with OS 10.8.4 (Mountain Lion).
View 2 Replies
View Related
Apr 9, 2012
I am using excel 2010 and cant see the paste special options in any of the worksheets. If I restart my laptop then it shows up. I noticed that after opening Internet explorer, the options disappear once again and restarting the system brings them back.
View 1 Replies
View Related
Oct 26, 2006
I have a workbook where I want to retain FORMATS in all of my cells in all of my sheets, but allow the user to enter data. So I got this code to undo PASTE and instead do a PASTE SPECIAL VALUES:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim UndoString As String
On Error GoTo err_handler
UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)
If Left(UndoString, 5) = "Paste" Then 'Only allow Paste Special|Values
Application.ScreenUpdating = False
Application.Undo
Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.ScreenUpdating = True
End If......................
View 9 Replies
View Related
Apr 19, 2012
When I copy a cell with CTRL-C and paste elsewhere, a small clipboard pop-up appears to give paste options. The pop-up is the size of an average 'starter' cell. I find it a nuisance as it always covers a cell I might want to paste into, but I cannot see that cell anymore because of the pop-up. How can I get rid of the pop-up?
View 2 Replies
View Related
Jan 22, 2007
I want to copy and paste from one sheet to another based on column a using a macro copy button.
E.g. if column a value = apple then copy that row into the apple sheet.
View 9 Replies
View Related
Jul 30, 2014
I'm currently using Excel 2010. Before, when ctrl+v'ing a cell that wasn't a number (but was, for example, a function), I could immediately after press ctrl, bring up a paste menu, and press v to paste as a value.
So before, if I had a range of cells that were functions (say something simple like "=D3/E3" on F3 and dragged down), I could do the following to paste the range into another worksheet:
1. Ctrl+shift+down to select the entire range
2. Ctrl+C
3. Ctrl+V into a different worksheet (at which point everything shows up as either "#DIV/0!" or as the wrong number, depending on cells to the left)
4. Ctrl (brings up the paste menu)
5. V (selects "values")
6. The entire range is now pasted as values, and not as functions
Recently, however, clicking on "ctrl" after pasting brings up no paste menu. I haven't changed any settings. I will lovingly serenade the first (and second, and third) man (or woman) that figure out what settings I must change or what I must do differently.
View 1 Replies
View Related
Jul 10, 2006
I want this macro to find in this case "406" in column A which is at the very end of the last block of 160 rows of information.
I then want it to move up 159 rows and copy 160 rows of information underneath the last block of information.
i.e.go to A5280 , then go to a5121, copy rows 5121:5280 to 5281.
It falls over on the very last line of code I can see A5281 selected but it won't paste....
View 9 Replies
View Related
Jul 13, 2007
I tried writing a macro to copy data from one tab and paste it on another. It gave me an error message every time.
So I tried recording one doing just that, and when I went to play it, it still gave me an error.
So here is what I came up with:
View 10 Replies
View Related
Feb 18, 2009
I have a total of 30 sheets in my workbook. I would like to copy rows 21:1000 from sheets 5 - 27 and paste them into sheet 4 starting on row 21. The data ranges vary within sheets 5 - 27. For example, sheet 6 has data in rows 21:50, whereas sheet 10 has data in rows 21:500. I chose 21:1000 as a safe measure to ensure that all data starting on rows 21 onward gets copied.
I am having trouble thinking of how to create a macro which will copy the data from sheets 5 - 27 and paste into sheet 4 leaving no blank spaces in between pasted rows. Ultimately my goal is to use the filter boxes I have set up in the column headers of sheet 4 to quickly sort all the data pasted from sheets 5 - 27.
View 4 Replies
View Related
Mar 6, 2009
im trying to record a macro that allows me to copy b22-b25 so i can copy it sp its on my clipboard to use in other applications
but i have this and it doesnt allow me to paste
View 14 Replies
View Related
May 1, 2009
I need a macro that will copy row 7, then let user select desired row, then the macro will paste the copied row onto it.
View 5 Replies
View Related
Dec 3, 2007
i'm currently building a project with VBS2008, i'm currently stuck on 2 things.
I have the beginning of my code here(it's a button):
Private Sub Button14_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button14.Click
Shell("C:Program FilesMicrosoft OfficeOFFICE11EXCEL.EXE")
AppActivate("Adobe Reader")
'copy stuff wants to go here
'Then i want to paste into Excel
AppActivate("Microsoft Excel")
'Then run a macro
(PERSONAL.XLS!Eden)
End Sub
Adobe Reader will always be running.
I'm trying to copy all text data from Adobe and paste it into Excel. I'm having trouble trying to mimic pressing [Ctrl-A] [Ctrl-C] and then mimic the paste [CTRL-V] into excel, then running the Macro in Excel.
View 9 Replies
View Related
Apr 13, 2008
I have a requirment to be able to copy a data range from one sheet to another. However each time the macro is run I need to be able to change the ROW the data is pasted to via user input.
EG First time macro is run, range to be copied from sheet 1 is A1:D1 and then pasted to sheet 2 range A5:d5.
the second time the macro is run I need to tell it to paste the data from Sheet 1 A1:D1 to Sheet 2 range A6:D6.
I guess the ultimate way to achieve my need would be for excel to find the last row of data on sheet 2 and automatically paste to the next free row, but thats way beyond me.
View 9 Replies
View Related
May 27, 2009
i am trying to record a macro that copies and pastes data from a webpage into a sheet in excel and then copies and pastes different data from a different webpage into another sheet in excel.
when i replay the macro it just pastes the same data twice instead of 2 different sets.
i am obviously making a basic error - is the way i copy and paste?
View 9 Replies
View Related
Nov 2, 2009
I have an index page with all my employee numbers and a summary page.
All my employees work on a time payment basis.
So for every Employee there is a worksheet with a calculator that determines their working hours.
I need a macro that copies the information on every worksheet to the summary worksheet.
On the worksheets the information is in calls: A53:F53
The Macro needs to paste that information under each other. So for instance employee 255 has a worksheet with the label of 255. His information needs to be pasted in the summary in cells A2:F2.
The next employee is 503, he also has a worksheet with the label of 503. his information is in cells A53:F53 (the same for all the employees). This needs to be pasted to the summary page in Cells A3:F3 (underneath employee 255)
I have 802 worksheets and 1 Summary. All the information for all the employees is in A53:F53.
I tried to do a record macro, it worked but excel gives me an error that sad : Procedure to long.
this is the code that i used:
Sheets("002").Select
Range("A53:F53").Select
Selection.Copy
Sheets("Summery").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Every employee has a coding like this..... so you can understand that excel thinks the procedure is to long.... ha ha ha..
I need the macro to paste only values and not the formula.
View 9 Replies
View Related
Apr 22, 2006
I have an excel form that I fill out when I service a vehicle. This form has a couple of cells that I would like to transfer to different workbooks by way of a macro.
The form cells that I want to transfer are labeled as follows:
InvoiceNumber or cell Q3
Date or Cell R5
TotalLabor or cell R31
TotalParts or cell G31
Tax or cell R40
I have 3 external files named:
Tax - this file has 4 columns: Date, InvoiceNumber, Tax
Parts - this file has 4 columns: Date, InvoiceNumber, TotalParts
Labor - this file has 4 columns: Date, InvoiceNumber, TotalLabor
I would like to have each external file copy the info into the next row and keep a running total at the bottom.
View 4 Replies
View Related
Aug 1, 2006
I would now like to adapt it to do something else. The code is as follows:
Sub addtaskstnmtce()
ActiveSheet. unprotect
Application.DisplayAlerts = False
myrow = Cells. Find(" Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
mycell = Cells(myrow, 2)
mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
With Range(Cells(myrow, 2), Cells(myrow + 5, 2))
.EntireRow.Copy
.EntireRow.Insert Shift:=xlDown
End With
Application.CutCopyMode = False
Cells(myrow + 6, 2) = "Task#" & mynum
Application.DisplayAlerts = True
ActiveSheet.protect
End Sub
View 8 Replies
View Related
Dec 5, 2006
i have two work sheets open. with the press of a button on the screen, i need a selection of the sheet to be coppied, different data per button clicked. In the other open sheet depending on which button the user clicks on, i need that data to be pasted. I have tried it by using the record button, but when i have finished recording, the paste option does not come up in the next sheet. I know it can be done manualy, but i have lots of data all over the place.
View 4 Replies
View Related
Feb 11, 2007
My application requires the user to use a check box to copy live date in a row, into the row that the perticular check box is in.
View 3 Replies
View Related
Dec 19, 2013
I have a financial model (attached) which calculates the final cash flows (in a calculation sheet named 9.Operations, rows 428 to 437) for a particular plot. However,the model has a list of 17 plots in the input sheet (3.Input-Plot Details). The macro must change the plot number in the 9.Operations sheet(cell G11), re-calculate the final cash flows and paste all the final cash flows for 1 to 17 plots in a single separate sheet consecutively.
View 6 Replies
View Related
May 15, 2014
see the attached file below. I have monthly precipitation data for yrs 1950-1999. I need to have the data formatted such that the precipitation data is copy/pasted into one single column, going all the way from jan 1950 to feb 1950 (next row) to mar 1950 (next row).....dec 1999 (last row). On occasion, I will also have data for shorter/longer time periods. the macro code I'd need to take this data and line it all up into one single column?
View 7 Replies
View Related
