Macro To Perform Multiple Jobs?
Jul 30, 2013
I need a macro which will perform 5 simple tasks on my Customer sheet once a button is clicked:
1) Change Cell C15 (Drop down box) Value from "Confirmation" to "Delivery Note"
2) Put the current date in Cell H43 (I do not want this to update everytime i re-open the spreadsheet however) - is this possible?
3) Value of H23 to be the same as C16 on the Delivery Sheet ( guessing this will be =Delivery!$C$16)
4) Print Customer sheet
5) Print Delivery sheet
I'm guessing the hardest part is to stop the date changing?
View 3 Replies
ADVERTISEMENT
Oct 3, 2012
I have a macro which refreshes a query when the spreadsheet is opened. This works fine when online.
However, if the user is not online, the query is unable to refresh and the macro just hangs.
Is there a code which will enable me to say " if unable to refresh then move on to the next line"?
here's the code below.
Private Sub Workbook_Open()
Sheets("Houselist").Activate
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Front").Select
Range("A1").Select
End Sub
View 2 Replies
View Related
Jun 4, 2008
I am wondering if there is a way of performing Vlookup across several tables? I have tried to create a formula myself based on lookup and nested vlookups, but am getting all tanlged up, especially as I need an If isna formula in there so that if there isn't a result, it displays a 0 rather than a #N/A.
See the attached. I do compeltely understand that it would be so much easier if all the data on the commission worksheet was in a simple 3 column table, but it has to be split as it is creating 3 tables. exactly what formula needs to go in cell D7 on the rates worksheet that I can drag down, which will check for those initials across all 3 tables? (The initials will only appear once on the table on the commission worksheet.
View 2 Replies
View Related
Jun 21, 2014
I am trying to do the following: I want to replace the values in A1:A3 with "0" and clear the contents of B1:B3 for all worksheets that contain "ILA" in the worksheet name. Is there a simple way to do this or another thread that already answers this question? I tried to search on the internet but found nothing useful. Here is a sample file (NOTE: there are no macros in this workbook even though it is saved as an .xlsm).
Clear and replace macro on multiple tabs.xlsm
View 5 Replies
View Related
Dec 16, 2006
I am looking for the easiest way for my worksheet to perform the following:
My 1st sheet (Sheet1) has columns A-C
In Column "A" there is a Cust # call it "P200"
In column "B" I have an invoice amount of $500.00
In column "C" -In this case, I need this field to calculate 15% of the amount in column "B"....and return the result of $75.00 (however, this 15% figure will vary based on data looked up in (Sheet2)
ie... in (Sheet2)
Column "A" will list all cust #'s that my company has - A1=P100, A2=P105, A3=P125, A4=P127, A5=P200 etc....
Column "B" has each respective discount amount for that customer by %... B1=10%, B2=10%, B3=12%, B4=20%, B5=15%
So the macro I need for (Sheet1- column C) will need to search "Sheet2" column "A" to find the "P200" out of the long list and then take the corresponding % amount in column "B" (in this case it's 15%) and use this figure in (Sheet 1) to perform the calc.
View 9 Replies
View Related
Mar 30, 2007
I need a macro that will perform a set of oprations on all worksheets in a workbook. The names and number of worksheets will not always be the same. I have found several macros that look like they will do this, but when I try to add my code they don't seem to work.
View 9 Replies
View Related
Feb 3, 2010
How can I make this macro easier (smaller)? The same action is performed on an array of sheets (not all sheets).
View 4 Replies
View Related
Dec 10, 2008
I have this formula that checks a line of values to see whether it is within the constraints of a separate list of values.
=if(AND(Data1!A4="count1",Data1!D4>="bin1",Data1!D4
View 9 Replies
View Related
Apr 8, 2014
I have two sheets. One is named sheet1 where all my data is and sheet2 where all my values are. I want to do a find a replace on column N (sheet1) using the data in sheet2. Column A on Sheet2 has all the values that are found in column N and column B on Sheet2 has what the data should change to.
So for example:
Sheet1 says the following on column N:
cat
dog
lion
bear
Sheet2 says:
Column A
A1: Cat
A2: Dog
A3: Bear
Column B
B1: 2
B2: 8
B3:15
I want the values on column N to be replaced with 2, 8, 15 and so forth. I use excel 2010.
View 1 Replies
View Related
Mar 10, 2014
Converting old reports to usable excel format. I am having trouble using VBA to set up a looping macro that would search Column A for cells with the text string: "ACTIVITY TOTAL". If cell has that value I would like to perform text to column action on the adjacent cell (R0C1). The macro recording for the test to column settings i need is :
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True
I have attached a sample of what I am currently working with. The actual file has roughly 6000 lines of data, so I would like the macro to go through all lines.
Attached File : LoopingVBA.xlsx
View 3 Replies
View Related
Apr 2, 2009
I'm trying to create a formula which will allow me to do the following
1. Enter the number of jobs received
2. Divide it by the staff available without ending up with decimals.
As an example. Say I have 60 jobs to be allocated and there are 13 peeps to distribute them to I'm currently getting 4.62. What I need to do is make sure that the jobs are only issued as whole numbers. E.g 5 peeps gets 4 and 8 get 5. I also need to factor in jobs on hand etc but I have already cracked this.
View 2 Replies
View Related
Jun 30, 2009
look at the attached. I trying to identify any jobs that are in sheet1 but not in the current orders tab.
View 3 Replies
View Related
Sep 17, 2007
I'm trying to print individualized reports from my Excel gradebook for only those students whose grades are below 70%. I thought an If, Then structure could do that, but the code I've come up with keeps printing reports for ALL students regardless of their grade.
totalgradeindreport refers to a cell in the gradebook where the student's total grade is displayed as a percentage. The value is actually a decimal number (I think), so I have tried to build a test for the If-then statement
If totalgradeindreport * 100 < Criterion Then
to control printing. But I am obviously doing something wrong.
Here is the
Sub DandFreports()
Criterion = InputBox("Print reports for all students whose grade falls below what percentage?", "Info", "70")
Test = Criterion / 100
Count = InputBox("Please enter the highest possible student number.")
View 9 Replies
View Related
Jun 17, 2007
I have an Access database, with a table of jobs processed.
One column for Job ID (Unique), Date it was processed, By Who it was processed and Comments ascoiated.
I need to chart a graph of Jobs over a period of time. How many jobs done per date. In excel preferebly, I have programs like Database Plus for excel. And I am familiar with VBA. I can't figure out how to uniquely sort how many jobs for each date. As there are numerous job entries for on the same dates. I think it can be done with an SQL query or sorting it with VBA somehow. Any help will be greatly appreciated. Or can anyone suggest an application designed for charting with Access databases in Excel?
View 6 Replies
View Related
Feb 13, 2013
Im having a hard time creating the formula to calculate the min number of employees to complete a number of jobs that come in each day and need to be finished in 18 days or less.
Data that I have:
Forecast of the number of jobs for 251 days ( jobs range from 10 -80 per day) Each job needs to be finished in 18 days or less we need to staff a fixed number of employees for the whole year each employee can complete 4 jobs per day no weekends
i have excel 2010 and crystal ball
View 1 Replies
View Related
May 3, 2007
I have a quoting application for damaged vehicles, this application is able to export all quotes and information relating to quotes into an excel sheet. the export file arranges the jobs in rows with each value in a separate cell i.e.
Job #|| Name || number || Parts $ || Total $
1453 || Mike K || 1234567 || 123.00 || 222.00
1533 || John C || 4353456 || 234.00 || 2342.00
Etc
Each job is listed on a separate row as it exports the records from an access database. once the information is exported I need to open another workbook and type in a job number into a cell and have excel automatically reference the relevant job and information relating to that job (all info in the same row) and fill in all the details from the other workbook into the new workbook. Example: the exported file contains 200 jobs, of those 200 i require 20 to view for one week. I have the job numbers of the jobs which I need to get information for. i open up another workbook and type in the 20 job numbers in separate rows and excel fills in the rest by referencing the other worksheet.
View 4 Replies
View Related
Mar 26, 2013
All sheets are basically the same except some minor values, names and addresses. There is one page per sheet and all pages are in portrait format. The print preview shows all the pages in order but when I print it, it makes multiple print jobs of 1 to 2 pages each and prints them all out of order. I don't know why it is splitting up the workbook or why it changes the order. I print and reorder these weekly, which is a major pain. It comes out in the same order each time but it is the wrong order.
View 1 Replies
View Related
Aug 5, 2009
DSee the attached.
The current spreadsheets add up each persons totals by matching the name in each tab with the name of the person who won the job located in current orders tab.
BUT.....If two salesman pair up on up on a job then the formula doesn't recognise the joint name. eg Gary/paul in row 69 (current orders).
I need the totals to half the job and add it to the salesmans total accordingly. There is no 'Paul Tab' as he is our MD and doesnt have a target.
View 5 Replies
View Related
Dec 25, 2013
it is more than 5 days now that I try to perform calculation on pivot.
problem: I have one cell that I set to show values as "% of the column total" when try to perform the calculation through insert calculation field with other cell the calculation is not correct/ it seems like the "show values as "% of the column total"" disturbing the calculation resolve this thing
View 14 Replies
View Related
Jul 29, 2013
I have a large spreadsheet set up with multiple rows, I have the formula -
=a5&"repeated text"&c5
this brings back 2 results
test repeated text test
repeated text
In the instance this is the latter with no combined cell vale, how would I mass delete them?
View 2 Replies
View Related
Sep 16, 2013
I have a table of data with two rows of unqiue identifiying information. I am trying to develop a formula that will allow me to do a vlookup twice, once for each row of unique identifying information to retrive the value in same row for the third column. For example:
apple red 50
aaple blue 75
aaple yellow 80
apple green 90
grape red 12
grape blue 17
grape yellow 45
grape green 41
So, I am trying to do a lookup where I can say what is the value in the third column for apple and red. Or, what is the value in the third column for grape and green.
View 8 Replies
View Related
Feb 13, 2008
The following code works great in selecting individual cells and forming a text string with them. What I need to do is to change this slightly andi instead of just reading the cell, have the preceding value added to the current value "(Cells (e,i)" and that new runnng total value is added to the "temp" string.
The concept is 12 cells represent Jan - Dec (numbered 1-12)
I'm trying to perform a running total; where Feb(2) = (Jan(1) + Feb(2)) and Mar(3) = Feb(2) + Mar(3).....
I tried setting x to do the running total and then passing it to "temp" but I kept getting mismatch errors, or buffer overflow errors, or out of memory errors.
Is there someone who can provide some assistance with the proper way to do this? ....
View 9 Replies
View Related
Dec 21, 2013
How to set some conditions for identifying trends. there are two trends I am trying to track, one if there are any 3 consecutive months where the score was >0, and the other was if there two occurrences of 2 consecutive months (tracking area 1 has that example), tracking area 2 shows the three months in a row, and tracking area 3 did not meet either. It doesn't mater if meets both in same row I only need to identify if yes or no.
View 3 Replies
View Related
Mar 30, 2014
I am taking an online computer class and when i enter a formula (=c5/c11) and then press f4, to make the cell c11 absolute, this does not work. Instead it brings me the "Project" right side mini screen. Is there a way to program the f4 key to return absolute results?
View 5 Replies
View Related
Jun 22, 2009
I'm try to put together a set of macros that perform certain operations within a file. However I've got a bit stuck on the following, and I can't see where I've gone wrong:
View 3 Replies
View Related
Dec 14, 2012
I'm developing a financial spreadsheet.
I've got a cell "Taxable Income" and another cell "Tax on Taxable Income" in sheet2.
When I enter a value in "Taxable Income" I want "Tax on Taxable Income" to automatically calculate based on formulas in sheet1. I don't know how to get "Tax on Taxable Income" to determine which formula to apply from the different tax brackets.
For example:
If taxable income is <$18201, tax = $o
If taxable income is between $18201 and $37000, Tax on Taxable Income =0.19*Taxable Income
If taxable income is between $37001 and $80000, Tax on Taxable Income =3572+0.325*(Taxable Income - $37000)
The idea behind having formulas in a different sheet is so over time when tax rates/brackets change I only have to change the formulas on one sheet.
I know how to use basic AND, IF statements e.t.c but I can't seem to get the syntax right for this to work.
View 1 Replies
View Related
Feb 21, 2014
I have a workbook that contains two sheets that has 20k of rows of data and in a summry sheet I have a SUMPRODUCT and SUMIF formula together which performs the sum function on the large set of data based on a range of criteria on another sheet. Here's the formula:
=SUMPRODUCT(SUMIFS(BalSht!$F$2:$F$20175,BalSht!$B$2:$B$20175,CedantCode,BalSht!$K$2:$K$20175,'2. Booked Financials'!$A46))
The problem I have, is that by combining the large data set into my workbook it is becoming slow and unwieldy. I was thinking of having a macro button that when the user has input the criteria, they click the button and it then opens up another workbook and performs the sumproduct(sumif formula.
View 5 Replies
View Related
Oct 31, 2008
I am trying to make certain changes to the page setup on all sheets in a workbook. Here is my code but something is not working. I think I need to somehow select the next sheet because the macro is only performing the task on the active worksheet. My Visual Basic skills are somewhat limited so I'm sure this is not the most efficient way.
View 3 Replies
View Related
Nov 17, 2009
I would like to have a cell in which there is descriptive text and the results of a calculation. For example, in row 2, I want to state the current total number of items in inventory at the top of the sheet in the center of the used cells.
Inventory Total is: SUM(A4:A64,000) as of: Date()
I can't merge cells or manipulate the text in such a way to neatly bring the text and calculation together. And there are actually 2 calculations in the example above. One to SUM the items and one to add the date.
View 5 Replies
View Related
Apr 12, 2013
If a name is entered in cboRepName combo box, I want to check if the name matches the list of names in the named range "Names" found in sheet repInformation. If the name is not found, then perform tasks.
Here is what I have so far, of course is not working, but I think I am close.
Code:
Private Sub cboRepName_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim repName As String
Dim nRange As Range
[Code].....
View 2 Replies
View Related
