How To Have Data Automaticly Transfered From Sheet 1 To Sheet 2 When Its Labeled "IV"
Jun 17, 2009
My business requires me to dial names and phone numbers all day from a list of leads in Microsoft Excel. I use verious disposition codes for each phone number I dial such as...
WN = wrong number
NQ - Not Qualified
IV = Invite
SA - Sale
DNC = Do Not Call
UA = Unavailable
AM = Answering Machine
What I would like to do is apply a function to the disposition column so that when I label a lead as "IV" the information will automatically be copied to sheet 2.
Please click on this link to get a microsoft excel list of leads that I've already dialed on and has dispositioned applied to it so that you know exactly what I'm talking about.
I currently have 4 sheets in a workbook. They are... "Main", "Safety", "1 Week Response" and "Routine" I would like to enter data into the "Main" and have it automatically send that data to the corresponding sheet name. Example: On the "Main" sheet I enter in row 1 column "A" the number 520; column "B" I enter Safety. Then I want to push a macro button that sends this data to sheet "Safety" in the next available row.
I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."
(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:
(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.
i need a macro that will compare the p'folios in sheet "Data to Sheet Workings, and then list the p;folios which is in sheet Data but not in workings in Sheet "New", so in the example below this would be ETSTSA ...
Is there a way to count all sheets between sheet "Begin" and sheet "End" if the sheet contains the word "complete"? The word "complete" can be found anywhere in the spreadsheet. If I need to be more specific let me know.
I am tracking my sales interactions and am trying to link cells so when I update on one sheet it updates my last "customer touch" on a master sheet. Current formula-
On "sheet1" I selected a cell and used this formula... =sheet2!$b$7
The problem is that I want to keep a history of what was in that sheet 2 B7 cell so i insert a row (making B7 move down and become B8) and enter new info into the new b7. if I go back to sheet1 the formula in the selected cell becomes =sheet2!$b$8 I want it to stay to =sheet2!$b$7 no matter what I do... insert rows, delete rows etc...
in short what i am trying to do is have a cell in sheet1 show my last interaction with the customer and keep a history of in sheet2-infiniti.... with the caveat that my last interaction on sheet 2 etc... is listed first (thus the insert row)
im trying to count all the cells with data in sheet 1 column g but it must omit any cells that have "vs" in it. all cells have scores in like 1-1 2-2 2-1 etc but a few have vs in them and i dont want them counted
i have multiple worksheets which has data (similar to sheet "Homewares") which need to be summarised in the summary sheet, i need macro that will grap all the info from all worksheets and pull data into sheet summary. The headings in data sheets will always be in Col A, i need to populate the summary sheet with this data please see below example
I have a excel table in Cell B2:C2 to B10:C10 in sheet1. I need to copy this table "x" number of times as specifiedin A2, with an row offset (gap) of 10 rows starting D2 in sheet2.
But somehow, only the first row of the data is getting copied?
I have two sheets, one is named "details" and other is "query". The data in sheet "details" is listed in columns A, B,C, then D is blank and again data is in columns E,F.G. (in text forms)
In the Query Sheet, wish to enter in A1 the value contained in A, B or E, F of "details" sheet, the result of columns C or G may appear in Column B1 of Query sheet and also if value is equal to C or G, the result of A,B, or E,F may appear:
I would like to write a function that enhances conditional formatting capabilities in this way: suppose that cell(4,5) contains a number that can be 0 to 5 in the adiacent cell(4,6) i want to put a function that:
1) write "NO DATA", "HIGH", "GOOD", "MODERATE", "POOR", "BAD" depending on that value 2)Choose color font depending on value 3)Choose color background depending on value
I wrote this piece of
Public Function StatusResponse(AdiacentCell As Range) As String Dim thisStatus As String Dim ThisFontColor As Integer Dim ThisbkColor As Integer Select Case AdiacentCell.Cells(1, 1) Case Is = 0 ThisbkColor = 2 ThisFontColor = 1 StatusResponse = "NO DATA"............
In my workbook I have multiple sheets but I'm attaching a very simple workbook to demonstrate what I'm trying to accomplish. In my "Lookup" tab/sheet. I want to have known Latitude and Longitude data that will exist in columns A&B. Columns C & D will have address numbers and Street Name. I would like my lookup formula to find the longitude and latitude data from my "lookup" sheet, when the matching address information is typed in, in my 2009 sheet. I have to keep the street numerics and street name separate on this worksheet as well. I believe I'll need two separate lookup formulas as I need these formulas to start in cell G4 & H4 in my "GeoCoding1" sheet. Is it possible to have four columns of data to be viewed in a lookup formula? I tried this formula in cell G4 (GeoCoding1 sheet)
Basically my code is bombing at the red highlighted code. Error: "Run-time error '1004': Copy method of Worksheet class failed"
Sheet "Blank Form" is a hidden sheet. Can I alter the red section of code to work while being a hidden sheet? I tried a few variations but nothing seems to work..
i am using the macro below to get excel to hide all sheets but the one called "open"...
PHP Sub savemini()
ActiveWorkbook.Unprotect "letmein"
For Each sh In ThisWorkbook.Worksheets If Not sh.Name = "open" Then sh.Visible = xlVeryHidden Next sh
ActiveWorkbook.Save End Sub
So the workbook is protected with the password "letmein", and the VB code above in theory should unlock the workbook, allowing all visible sheets to be hidden with the exception of the sheet called "open". Then is saves the file.
Now heres the odd bit... it works for certain people, but not for others. I have made sure those it wont work for are not doing anything weird and they are not.
The workbook i am using has a code that only opens certain sheets for certain users. I as a master user have access to all sheets. I can go into the users sheets and click the button that activates the macro above myself and it works fine, but for some users it wont work.
i have a worksheet which is a form which feeds into another worksheet which acts as a database. i have a button which clears the form based on code which is essentially "clear contents".
the problem is i have a few cells where i would like to retain the formulas. i tried to do this with custom in validation but this did not work. i also tried to enter the code directly into the worksheet but this didnt work either (my skills are limited..). i am avoiding using protect sheet bc that in turn will affect many of my other buttons. is there something i am overlooking?
I have a workbook with about 200 sheets. Each sheet contains lots of links to the other sheets, so the user can clink the link and then go to the new sheet.
But, how do I make a "return-function"? That can return the user to the sheet that was active before the link was followed? If possible I would like this do be doable for 5-10 sheets back.
This "return-function" is gonna be used by clicking a button on a floating toolbar I have connected to the sheet.
As per the following code I'm trying to add a new sheet which should inclide the CHANGE Event Proc
Unfortunatelly, I get an error message (free translation from Hebrew): ==================== RunTime Error '1004': "Programming entrance to the Visual Basic Project is not secured". =========================================== Here is the code I used:
Sub AddSheetWithCode() Dim StartLine As Long Sheets.Add With ActiveWorkbook.VBProject.VBComponents(ActiveSheet. Name).CodeModule StartLine = .CreateEventProc("change", "worksheet") + 1 .InsertLines StartLine, _ "if not intersect(target,range(""a1:a10"")) is nothin then" & Chr(13) & _ "if target=10 then" & Chr(13) & _ "target.clearcontents" & Chr(13) & _ "msgbox (""Bla...."")" & Chr(13) & _ "end if" & Chr(13) & _ "end if" & Chr(13) End With Application.VBE.MainWindow.Visible = False End Sub
I want to be able to copy a name from one sheet (Available Players), paste it to a cell in another sheet (Round 1 through Round 20). The cell that will be copied is fixed but the place where it will be pasted will be different and may be on a different sheet.
also i would like to change the color of the copied cell to "greyed" out or cut if it can not be greyed out. I have created a button and put in a macro that i created but have been having problems with it, generic 1004 errors that i can not figure out. i am attaching the document.
I have a "Menu" workbook that has 1 sheet named "MyMenu" which only has macros and Hyperlinks on it.
I would like to add a second sheet to this workbook named "SetUp".
Next, as an example, I would have the user list 20 Excel Workbook.xls files on the 2nd sheet (SetUp sheet) in Cells A1 thru A20 (as my example). In column B, directly adjacent to each file, they would enter a generic name of their choice, representing each file. These are files that they would be using frequently. The file names would be entered in cells B1 thru B20.
Now, I need 20 macros on "MyMenu" that can open these appropriate files listed on the SetUp sheet. As an example, I could have 20 macros that are located in Column D on "MyMenu". They could be located in cells D5 thru D24.
2 Things I want these macros to do . . . First . . . The Generic name would be displayed in the appropriate cell in column D so that the user would know which workbook they are going to open.
And Second . . . Clicking on the Generic name would open the associated file.
NOTE: If the user were to enter a new file name in one of the cells in column A and a new Generic name, the name for the macro on "MyMenu" would automatically be changed and the new file name would be opened when the macro was clicked.
RE-STATED: What I am looking for is a way of running macros from "MyMenu" sheet by clicking on any paticular macro I want to run just like normal . . . but, I want the macro on "MyMenu" to open the file that is listed in column A of the SetUp sheet.
REASON: "MyMenu" has to be a locked sheet. Therefore, the user can not enter or change macros on "MyMenu". I actually have 5 or 6 macros that I would like them to be able to change, but again, "MyMenu" has to be "Password" protected. Additionally, many of the users simply don't know anything about macros or hyperlinks
I have started to actually write a few macros for myself and am doing ok ish. this one has stumped me. I would like all open sheets to be considered. if any cell on row 12 on any sheet is changed to "yes", i would like to have from the sheet it was changed on, the following cells copied (some update at intervals, so i would like them copied so they show what is in the cell at that time and then not change with the update)
the sheet name pasted into A144 of sheet 5
the cell from row 6 that is on the same column as the "yes" pasted into B144 of sheet 5
the cell from row 9 that is on the same column as the "yes" pasted into C144 of sheet 5
the cell from row 10 that is on the same column as the "yes" pasted into D144 of sheet 5
if another cell on any sheet is then changed to "yes", i want it to do the same as above, but paste it in row 145, then 146, etc so i end up with a log of those cells when a cell on row 12 = yes
I have a spreadsheet where only certain cells are to be edited by he user. After editing a cell I don't want the cursor to move. option/edit/move selection after enter is unchecked. All works as expected. I protect the sheet using options "select unprotected cells" is true, "Selected protected cells" is false. This seems to override the "move selection" turned off property. The option box does not work when unchecked.
I have searched the general forum and programming forum and see no cure for this. I tried a one line macro "Application.MoveAfterReturn = False" thinking if I set it programmatically, it will work. It does not. Is there a few lines of code that I could add to a ThisWorkbook macro that would reocord the current cursor location and set location to it after hitting enter? Or possibly an even simpler solution that I'm overlooking?
I have a seriew of drop-down combon boxes that ultimately "drill-down" to a resulting sheet & cell reference in cell F7. Based on the criteria selected in these drop-downs the resulting sheet & cell reference (cell F7) will change. I am looking for help with a macro that, when run, will take me to the sheet & cell referenced in cell F7. When I do a goto function it always remember the last place I went to and never goes to the latest data in cell F7.
