I am trying to change data vailidation settings on Sheet1 based on a state name provided on a separate worksheet (Sheet2). The below code is part of a macro that is assigned to a button on Sheet2. The issue is when the state name changes on Sheet2 and the button is pushed to initiate the macro to run, the state validation does not change on Sheet1.
View 2 RepliesI can't understand why my rate1() function (my macro) doesn't work properly. While I'm dragging M5 cell in Sheet2 down to M14 cell it gives the wrong result.
Even when I change value in Sheet1 and refresh data of pivot table in Sheet2, my function rate1() isn't updated. But my problem is solving (getting the correct result) when I'm going Edit Mode (pressing function key F2) in active Cell M5 or M6 or M7..so on and pressing Enter each time.
how do i change the default settings in excel, for example when i open excel it shows numbers on both rows and columns and i want it to show letters on the colunms. i know how to change the r1c1 reference style but how do i get it to stay the way i want?
View 4 Replies View RelatedHow can I change the default settings I get with a new Excel document?
What I want is that when I create a new Excel document, all cells should have Comic Sans MS font, Bold & size 10, with a particular horizontal and vertical alignment etc.
I am not able to do these changes using settings in Option menu.
Is there anyway to change the security settings in Excel 2007 using VB macros? I have tried to record the macro as I change the settings but nothings is recorded.
Basically I have two workbooks, and I want to run one of the macros within workbook 1 from workbook 2. This is straight forward, however why I try to run it, excel says that the macros are disabled in the other workbook, but I know they are not, they are all enabled (bottom option)
I was hoping to find some way of fooling Excel into thinking the macros were enabled by changing the security settings (actually keeping them the same, but making excel think that i've changed them) before I try to the macro from the other sheet. I don't know if i've explained myself very well here, I hope I have, if not let me know and i'll try to go into more detail.
Is there any way to change the settings in excel 2007, to give me the original color palate from 2003? I want to use the old colors because I don't like the new ones.
View 2 Replies View RelatedI have a big table with lots of columns for which I want a pivot to sum the data in the columns. Most likely because there are lot of blanks in the data Excel automatic makes a count of the data when I drag it into the pivot table. Now I have to change for each column one by one the Value field settings from count to sum. Is there a way to change the Value field settings for multiple columns at once from count to sum? If not can I somehow change the settings of the pivot table so default the Value field settings will be sum when I drag it into the pivot table?
View 4 Replies View RelatedI have a range of cells ( C2, C5, C8:N1007, P8:P1007 ....) on a worksheet that I want to be able to clear the contents of through a macro - worksheet is entitled Database
I have 3 additional worksheets all of which have summary information on and some of the formulas are huge.
I've inserted a module and produced this code;
Sub ClearContents()
Application.Calculation = xlCalculationManual
Range("C2, C5, C8:N1007, P8:P1007, ....").ClearContents
Application.Calculation = xlCalculationAutomatic
End Sub
Because of all the 3 summary page formulas the code is taking a few seconds to run.
I'm new to VBA and just wondered whether I could switch the calculation setting for the entire workbook onto manual at the beginning of the code and then switch it back to automatic at the end of the code? Think this might speed things up.
The following line of VBA code executes properly if machine settings are US English, but throws a "Run-time error '1004': Application-defined or object-defined error" ? ...
View 9 Replies View RelatedI have a line in my VBA code that i need to change the location of Sheet1 to another workbooks. Instead of Match at Sheet1 at the same workbook I need to match with sheet1 at another workbook called ("Master.xls). The Original Line:
View 3 Replies View RelatedI am using the below code and it is not performing the operations of comparing and deleting the duplicate values from sheet1 and pasting unique values in sheet 1, p.s. Do not need values from sheet 2, just want to compare the sheet 1 with 2 and delete dups in sheet1.
[Code] .....
I am having trouble disabling the "Preview" button when calling Application.Dialogs(xldialogprint) in XL2003. I have an xlSheetVeryHidden worksheet containing a form which is made visible programatically when the user wishes to print a copy (code below). I want the user to be able to have access to the functionality of the xlDialogPrint dialog (i.e. select desired print destination, number of copies, pages per sheet etc.) but I also want to prevent them modifying the page setup of the protected worksheet by initiating a preview from the print dialog.
Private Sub btnPrintReturnForm_Click()
wbkRUSC.Unprotect sysPass 'unprotect workbook to enable changes to sheet visibility
' (sysPass is project constant string containing password)
shtStaffForm.Visible = xlSheetVisible 'make staff form visible
shtCalc.Visible = xlSheetVeryHidden 'hide main calculator in case user gets smart and hits..............................
Im trying to create a validation for cells but the settings tab under data>validation menu isnt there.
View 2 Replies View RelatedExcel 2007 - need to adjust protected view settings but the protected view tab is missing from my Trust Centre settings - have the following tabs - trusted publisher / location, add-ins, activeX, macro, message bar, external content and privacy options. Document is a revenue authority download and without being able to adjust the protected view settings, can't input data.
View 3 Replies View RelatedI have 2 Worksheets in an Excel 2010 Workbook -
Sheet1
Column1: contains the word "dog"
Column2: contains the word "bark"
Sheet2
Column1: contains the sentence "I like dogs a lot."
Column2: is blank
What I need to do is search Sheet2/Column1 for the presence of "dog" and if it's present, populate the word "bark" in Sheet2/Column2 from Sheet1/Column2.
How can I do this?
I have attached a sheet that has part of a data list, sheet called (Full Data) what i am trying to do is seperate the data into event locations into individual sheets.
The data ref will be column F which is the different event locations.
I thought the best way to try and do this was to create an if/ match formula using the sheet tab names as the if match, with the event locations in column F.
I have added two sheet tabs so you can see that i require the data for (Ain) to be put into the Ain sheet.
The data list in Full Data sheet will be continuously updated so i will need the range to be around 10,000 entries.
I have data in sheet 1 like below
Custid loc city
123 us newyork
124 uk wales
876 in mumbai
Sheet 2
custid newcustomerid
123 756
124 394
Outputsheet:
Custid loc city
756 us newyork
394 uk wales
876 in mumbai
So it will check sheet1 custid with sheet2 custid. If both matches it will replace custid with newcustid.if cust id not exists it will load sheet1 recprds as usual.
In the following code, in select statement Type & Type1 & Type2 defines queue number. And now in sheet2 , there is a list of Queue names and corresponding queue numbers. So When the data gets copied from Access to Excel using the code below then i want the code that will check the queue number and replace it with queue name in Sheet1.
[Code] .....
I have two column (A and B) text data in worksheet1. If I type same text of column A (of worksheet1) in worksheet 2 column A, how can I retrieve data from worksheet 1 to 2 from same row.
View 1 Replies View RelatedWhen i record a macro with an autofilter, the default setting is always "equals". Is there any VBA code or anything to be able to change so that my macro will search in a "contains" setting.
View 9 Replies View RelatedI need to spool data from Sheet1 (Data) such that when the pins are pasted into the combo box and button is clicked it brings up the corresponding reasons and comments for each pin
I have attached a doc. pinpopulate.xlsx
I am enlcosing a file that I partially acquired (the macro portion) and I am trying to add to that.
The workbook has a dde link in sheet 1, the macro will write the data to sheet2 at set times. I am creating a report of the data in sheet 2 in sheet3. at this point it is very rudimentary. I need some assistance to clean up the macro and the post of the dat in sheet2. I have created a detailed (probably over detailed) explanantion in sheet 1 in a text box.
I have a large list of data in sheet1. The relevant columns contain a unique identifier (column 'N') and a value of either "True" or "False" in column O. Because I don't want users to have to trawl through hundreds of lines looking for the 'False' entries, I have a macro that copies only those records to Sheet3. In this sheet the user can set the 'False' value to 'true' but I don't know how to return the new value to Sheet2 (possibly for multiple records).
View 9 Replies View RelatedI have:
- sheet1.xls, this is the source sheet.
column A = model numbers
- sheet2.xls, have the data that I need to copy to sheet1.xls
column C = product description text and in column D = product price
column G = product description text and in column H = product price
What I need to do is to write a Macro that:
open each row in column A in sheet1.xls and search in sheet2.xls in column C if it finds this text (model number) then it should copy row D (product price) and paste it to column E in the right row in sheet1.xls.
Also search in sheet2.xls in column G if it finds this text (model number) then it should copy row H (product price) and paste it to column E in the right row in sheet1.xls.
I hope please that you understand me.
And will be happy if someone could help me please to do that
I want to skip 1 row blank in two entries while transferring data from sheet2 to sheet1. There are about 100 entries on sheet2 which I want to transfer on sheet1. If I skip 1 row and paste the formula it skips 1 entry. How to avoide it?
View 1 Replies View RelatedDate & Time JOB NO PENDING
12/23/13 2:14 PM SICD-B00xxx *
My question is, there are three cells named- Date & Time, Job No and Pending. When I tick(*) in Pending named cell, I need to copied automatically the data of Date & Time and Job No to Sheet 2 in the same cells from Sheet
1. which formula I can use and how?
Sheet 1 : Date & Time (O5), Job No (Q5) and Pending (S5)
Sheet 2 : The same as Sheet 1
When I give * in Sheet 1 S5, the other data should copied to Sheet 2 in O5 and Q5.
I want to use formula in sheet 1 S5 because I have already used a formula in sheet 1 O5, as
IF(Q5<>"",NOW(),O5),"")
Can anyone tell me if is possible to copy data from sheet1 to sheet2 using VLOOKUP?
I had an interview where they asked me to copy data from sheet1 to sheet2.
I used the function (=Sheet1!A1:...) and for me it was right, but they told me that it was wrong since I did not use the (VLOOKUP) function.
Please confirm if VLOOKUP can be used in this regard.
I have some data in sheet1 (as per attached sheet), every month i have to enter this data more than 1000 rows..i want a formula so that automatically this data should go to sheet2 and get a seperate report shape (as mentioned in sheet2) and also add a row below of each report in case ColumnA (Shipp) data is increased for some shipp name and also report heading shoudl be change according to columnA (shipp) and renumbering it.
View 3 Replies View RelatedI have data in sheet1, which needs copied to sheet2
But in sheet2 already data is available. In Sheet2, i want code to go from header to down , to locate the first blank row in between data and to paste data by inserting same number of rows as number of rows copied.
I basically have a sheet where clicking a button on a UserForm creates an email and populates it with data from a specified sheet. So for example, the attachment v3.1, I've included looks at Sheet1 and populates an email with the data. However, I want it to drag data from another sheet. So, as per the attachment v3.2, I want the UserForm on Sheet1 and the data on Sheet2.
It works fine if the UserForm and data it's looking for are on the same page, but it won't find information from another sheet when the code is changed? I thought changing the code to Sheet2 instead of Sheet1 would work fine, but that's not the case!