I have a worksheet that sometimes needs to be edited. In order for me to copy and paste I have to "comment block" the Workbook change and the Worksheet change event macros. Make my copy and paste then I have to "Uncomment block". I do this manually. Is there a way to create a macro to do this? Or is there an easy way to do what I am trying to accomplish.
View 9 RepliesI have attached a portion of an excel file I am using at the moment. What I require is a piece of VBA code which will allow me to copy selected data based on a combo box selection. What needs to happen is this :-
If the selection in the "Index" worksheet combo box related to "Month" in cell G19 is for instance "December", I would like the macro to compare this cell value to the cell values in cells C96, C124, C152, C180, C209, C236, C263 and C290 in the "Tech Services" worksheet and where the values match.......copy the commentary (Range C126:Z147 in December's case) to the range C34:Z55.
[url]
Private Sub Workbook_Open()
****'** MsgBox Environ("ComputerName")
****If Environ("ComputerName") UCase("Authorized PC Name") Then
********MsgBox "Sorry No Go!" & vbCrLf & "Wrong computer!"
********End
****Else
********'** Do your thing here
********MsgBox "Proceed"
****End If
End Sub
Reading the thread in the link above, It seems as if one could limit access to a workbook. I tried this code on a dummy workbook, I have a few question...
1. Why am I getting "Sorry, No Go. Wrong Computer" when I just put the code in the dummy worksheet for the first time?
2. Can this code be modified to capture my clients computer information to restrict the workbook useage only on one computer and prevent unauthorized distribution to other computers?
3. Can this code run automatically everytime the workbook is open?
I understand that security in Excel is not very robust, but the users I intend to distribute a workbook to are not very sophisticated
Are there any system properties that are accessible to Excel/VBA and are unique to a computer that could be encoded in a hidden/protected cell, so the workbook would be frozen if copied to another computer?
Eg the user emails me that property which I enter in the check cell, and upon opening the workbook the check cell is compared to the system property, locking it up if the check fails?
I have hidden columns on a sheet.
I protect the sheet with a password before I send out the file to a 3rd party. However, it seems that all a user has to do is select the sheet cells, copy and paste onto another sheet or file. The protection is nonexistant and the user is able to unhide the columns.
Am I missing something?
Is there a way to "really" protect a sheet so that a user is unable to unhide confidiential columns? Preferably a method without VBA code.
I have the following code from a form. It works just fine copying named ranges from one sheet to another. The probloem now is that I don't want to copy the characteristic of the formating or whether they are protected or not. The originating spreadsheets have cells that are protected. I am trying to copy those values over to the new sheet and paste their values or formulas but not their formatting (background color) or the fact that some of the cells are 'locked'. I want the new sheet to have the values and formulas but not the be lock for the user. Is this possible with the current way I do the code or do I have to rewrite it? How would you approach this?
Private Sub CommandButton1_Click()
Import_Data_Form.Hide
Run "NPA"
Set b = Selection
ad = b.Address
' Local Variables
Dim wkbDataFile As Workbook
' Let user select source datafile
Call UserSelectFile_WOpen(wkbDataFile)
If wkbDataFile Is Nothing Then
MsgBox "User did not select a workbook to open"
Exit Sub
End If
Application. ScreenUpdating = False
Set wb = ThisWorkbook
Application.DisplayAlerts = False
I recieve an error :
Run-time error '50289':
Can't perform operation since the project is protected.
When i try and run my code.
The code does add parts to modules and workbook events, but I need the code to be protected.
I have a VBA module that performs several things when a command button is clicked. One of the last things it does is paste values, so when the module is comeplete the pasted area is still copied to the clipboard; in other words, it is outlined in pulsating dashes. What can i add to the end of the code to un-copy this selection?
View 2 Replies View RelatedI am working on a project where all columns but Column A are locked. The user selects a row of data by selecting the one unlocked cell in that row. This gives him access to modifying the contents and position of that row via a UserForm. My question is: Is there a way to copy the row of data to another sheet automatically when the user changes his selection? In this environment, a change of selection means that the user is done with one row of data and initiating work on another row. What I am trying to accomplish is a procedure invoked by the Worksheet_SelectionChange event that references the cell that has just been de-selected.
View 3 Replies View RelatedHow can I sort a 7 column selection and then paste columns 1-4, and 7 to the new location? Columns 5 and 6 should be ignored for the most part. Also, how can I obtain the first values of the column 5 and 6 selection to? I want to use them to name a file. This is how I am selecting the range.
Dim rng As Range
'Copy Selection
Set rng = Selection
rng.Copy Destination:=Range("A8")
This is how I name my files. I am getting the values from the form.
Dim strFileName As String
strFileName = .DateBox.Value & " " & .FromStoreBox.Value & " " & _
.ToStoreBox.Value & " " & .RequestedByBox.Value & " " & ".xls"
If I select a group of non-contiguous cells with control shift is there any way that I can shift the same selection over to the next column. For example, I have A4 and A8 selected. When I press shift and right arrow it doesn't work.
View 4 Replies View RelatedI want to copy a selection of cells and paste them into a row on another
sheet.
An example would be to copy:
Sheets("Sheet1").Range("T5,C7:T9,T13,C15:T17,T19,C21:T23")
and put it into A4:F4 on Sheet2
I know I can do it one cell at a time but I was hoping that I could speed
things up and do it all in one go as the macro will be quite long.
I have this ....
View 14 Replies View RelatedI have a drop-down list with 3 values:
A
B
C
The list is generated from a table, which is hidden, like so:
Column 1 - Column 2
A - Text1
B - Text2
C - Text3
Now I want to have a very simple function that will allow me to COPY the text from Column 2 based on the selection from the drop-down list. For example, when the user selects "B" from the drop-down list, I want the text "Text2" to be copied to a specific cell.
I have is two spreadsheets with different data on them, but the only connector is the user ID, so I want the cell I select with the user ID to be copied it then switches to the other spreadsheet and finds the copied selection, it then takes the columns on the selection of A to J and pastes them back in the other columns in the other spreadsheet.
My only real problem is that I can't get it to use the selection.copy in the find to select the cells I need, any help would be great, thanks.
Code ( i put spaces bewteen the other parts and find function so you could easily see it):
Sub Macro1()
Selection.Copy
Windows("EMERA-employees-asof-06-Mar-2007.xls").Activate
Cells.Find(What:="(I want this to be selection.copy)", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Columns("A:J").Select
Application.CutCopyMode = False
Selection.Copy
Windows("SMSReportResults.xls").Activate
ActiveCell.Offset(0, 6).Range("A1").Select
ActiveSheet.Paste
End Sub
I have autofiltered a selection of data then issued the copy command in the code below so as to copy the visible area. In attempting to paste the data in the first blank row the program fails on the last code statement, "ActiveSheet.Paste".
The error is 1004.
An alternative suggested in the error message is to select an area the same shape and size as that being copied to paste into. Given that the copied data will change on each run how might I do this, and is this a valid alternative?
Selection.AutoFilter
Selection.AutoFilter Field:=7, Criteria1:="=TC", Operator:=xlAnd
Selection.AutoFilter Field:=2, Criteria1:=">=7330", Operator:=xlAnd
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Range("A" & LR + 1).Select
ActiveSheet.Paste
NB. most of the code has been generated by the Macro recorder.
I have a spreadsheet named PRODUCTS and in row A there are a bunch of product codes. I would like for the user to be able to use the CTRL and mouse to select all the products that they want and then once they push that button I would like it to paste the user selected cells into a sheet called Quote starting at A7.
View 4 Replies View RelatedCopy paste macro that I'm using to transfer information from Sheet1 to sheet 3. The macro must:
On the click of a command button copy information from A5 to M5 and A6 to M6 and paste it to the next 2 free rows on sheet3 between columns A and M.
After the copy/paste the macro must clear the information from the input sheet - A5:M5 and A6:M6 - but retain the formatting.
Simple loop macro.
1) Select/Copy rows 1 thru 35 of column C from "Start" tab.
2) Special Paste/transpose them in row 2 of "Finish" tab (1st 35 columns in row 2 should fill up).
3) Go back to "Start" tab, move exactly three rows down from bottom of previous selection in column C and copy the next 35 rows (rows 38 thru 72 in column C).
4) Again return to "Finish" tab, and special paste/transpose in next row down (row 3 in this case).
5) Continue to repeat this action until all rows of data in column C have been copied and transposed/pasted from "Start" tab to "Finish" tab. Variable number of rows on "Start" tab.
I want to select a range of cells (not together I.e. b2, c3,c4,g7 etc) and copy them to another sheet but I need to check which cell has been selected as I am using a check box to set a cell as true or false if ticked or not, so if ticked a certain cell will be added to the selection set to be copied.
View 7 Replies View RelatedI'm looking at [URL] which gives a lot of keyboard shortcuts, but not specifically what I'm looking for.
It's a basic function, I'm surprised it's not listed. I just want to copy the value of a selected cell via keyboard shortcut (for copy and paste purposes). I don't want the formula, just the value. If it were just text I would typically do CTRL-C and CTRL-V to paste like everyone does 1000 times a day, but in this case it's copying between two otherwise totally unrelated spreadsheets, so having the formula is irrelevant and produces undesired results. I just need the value of the formula.
Is there a keyboard shortcut for "copy value"?
I'm new to excel and VBA and I'm having problems with a copy paste macro that I’m using to transfer information from Sheet1 to sheet 3. The macro must:
On the click of a command button copy information from A5 to M5 and A6 to M6 and paste it to the next 2 free rows on sheet3 between columns A and M.
After the copy/paste the macro must clear the information from the input sheet - A5:M5 and A6:M6 - but retain the formatting.Thank You.
I want to create multiple pivot tables each performing its own tasks. When i want to filter a particular category in all first pivot tables i have to do this one by one.
This is time consuming and i think it can be done faster. Is it possible when i filter a category in pivot table 1 that this filter is automatically filtered in the other pivot tables?
I'm working in a rather large (20meg) Excel file with multiple worksheets, references, formatting, etc.
Suddenly, when I attempt to Copy > Paste Special > Formats using more than a few cells at a time, I receive this error message...
"Selection is too large."
That's it - no reference, no nothing. Doesn't matter if I select a few cells, or entire rows, I get the same message everytime. However, I can copy-n-paste the entire 8000+ rows of data into a NEW file with no problems whatsoever. It handles everything - data, formatting, formulas, etc. But within my working file (same worksheet even) I get the error everytime.
I'm not using a macro. This file has worked fine for months. I'm doing absolutely nothing differently than ever before. This error literally just started today.
I know there is some issue with copy/paste of 8100+ non-contiguos cells, but I'm using contiguos cells. And again, I can copy nearly 10K entire rows of data and paste into a new workbook - just not within the same workbook and worksheet.
I've searched online - here, Mr.Excel, Microsoft, everywhere I can think of. Of all the problems I've found answers too, I have yet to find ANYTHING dealing with this problem - except in the context of a macro, which I am not using.
The CRM application that we use provides output in an excel sheet "Table View 1". I want to be able to copy the data that is dumped to a sheet to another sheet. The requirements:The data output range is different. Sometimes it is 4 rows and sometimes it is 25 rows. I want a mechanism where based on the output, the formula/macro automatically copies the information to the new sheet.The first 2 rows need to be excluded when the copy is being made.
View 6 Replies View RelatedNeed achieving the following:
I have 2 sheets in one workbook, the first sheet named D1 where I have dropdown menus and the second sheet called reference where I have fixed data
what im looking for is when the user select an option from the dropdown menu on cell b3 on "D1" sheet based on the selection an entire table (4 rows by 3 columns) called "Default" from reference sheet to be automatically pasted into the D1 sheet.
I have two sheets that I am using. sheet 2 has a list of experiments in column C with information regarding the experiment in columns F - J. On sheet 1 I have a dropdown box with a list of all the experiments. When I select an experiment from the dropdown box I would like to populate columns K - O on sheet 1 with the information from columns F - J for the selected experiment on sheet 2.
View 9 Replies View RelatedI have a copy and paste macro below, that copies the selected rows and pastes them into a different sheet called Blank BOM. Each time they are pasted, it just writes over the previous items at the top of the list. I would like it to paste in the next open row, so I can go back and forth between the sheets and add things. Here is the code:
VB:
Sub CopyRow()
Selection.Copy Sheets("Blank BOM").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End Sub
I am working on a macro, which asks the required field from multi select list box.
User can select required field, for example some user may be interested in
Region ID, Circle, BSC ID, Cell ID, Sector ID, City/Town.
Some user may be interested in Circle, BSC ID, Cell ID, Sector ID, City/Town.
Some user may be interested in BSC ID, Cell ID, Sector ID.
In sort, Selection on a multi select list box copy and paste that field from any folder.
That folder may have any location, and contains file which have.
Date
Vendor
Region ID
Circle
BSC ID
[Code] .....
If a user selects certain options from a drop down list created by cell verification (from list), is it possible to display an input box and have the resulting input populate on another worksheet in the workbook? I have an attendance template I'm working with and if a user selects OT (Over Time) then a input box is displayed prompting for how many hours. The overtime is then tracked on another worksheet.
View 1 Replies View Related