away to have a cells formula only up date via a Macro
Example
A1+B1 = C1
the formula is only done through a Macro so that if it is disabled C1 would be empty?
I guess the trick is how does the macro update when a value is entered in A1 or B1?
So I have been trying to plug away at this but am getting no where fast.
I have a set of job numbers on sheet 2 that is pulled from sheet 1, when the job numbers are pulled over it also pulls the due date. The problem I am having is that Sometimes the due date gets pushed out and writing the macro that will pull in the new date
I have a macro written that pulls data off another workbook and places it on a sheet on the my main workbook. The macro is set to auto filter based on whats in Column 1. The code for this is below:
ActiveSheet.Range("$A$1:$AE$46303").AutoFilter Field:=1, Criteria1:= _ "201504"
Now what I was wondering is there a way for it reference say cell I12 on sheet2 in the main workbook and pull that data instead when the macro is run via a button. The number listed is a date code so say we change it too "201503". I would like to pull all those dates instead of the ones for "201504".
I have not used an event macro before and am trying to one update a cell when changing a cell. I am basically copying a number to another cell that is an input for a calculation and then returning the calculated value back. How do I reference r69 in the code to start the event macro?
View 2 Replies View RelatedTrying to get this macro to auto-run after the referenced cell is changed:
Sub Headerdata()
ActiveSheet.PageSetup.RightHeader = Range ("d1").Value & Range ("e1").Value
End Sub
I got it to work in one book but it won't work in any other book...
In Excel 2007.
I have a macro that is set to clear a range of cells. If I make a change to the worksheet (such as adding a column) that moves those cells in the worksheet, the worksheet formulas update, but the macro cell references do not. How can I make my macro update in accordance with changes in the worksheet?
Let's say that in column A I have numbers,"Yes" and "No". I want in column B to have only the numbers from column A, in the same order without any empty ranges, and everytime I add in column A a new number, column B to update automatically with that number. Let's have an example:
A B
Yes 12
12 13
No 10
13
No
10
Yes
And if I want to add in column A:
A B
Yes 12
12 13
No 10
13 25
No 15
10
Yes
25
15
So the column be will update automatically. I already tried =IFERROR(INDEX($A$1:$A$10,SMALL(IF(ISNUMBER($A$1:$A$10),ROW($A$1:$A$10)),ROWS(B$1:B1))-ROW($A$1)+1),") but using this many times get's my file very heavy and the excel is working slow.
I have a basic formula =C17+'Asset Depreciation 2008 Onwards'!C24, and I want to copy it down just using the drag function. Problem is that the second reference range of cells are in rows and hence when I copy it down it doesn’t automatically update the cell references because it want to update them by column number instead of row number. IE I want it to display =C17+'Asset Depreciation 2008 Onwards'!
D24, instead of C25. Do you know if there is any way of telling Excel that I want it to increase the column number by 1 every time, instead of the row number for this part of the formula?
I have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error.
Sub UpdateItem
...
.Fields.Item(1).value = activecell 'activecell value = "Joseph"
If Not isempty(activecell.offset(0,1)) Then
.Fields.Item(2).value = activecell.offset(0,1).value
Else
.Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works
End If
...
End Sub
It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used
.Fields.Item(2).Value = Empty
' or
.Fields.Item(2).Value = 0
after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).
I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run.
My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?
I need to be able to open a workbook named "Cost Price List" and press an update button which will open Another workbook called "Numerical Pricelist" that will then look up the part number of the item and change the corresponding description only, using the "Numerical Pricelist as the correct master document. There are approx 5000 part Numbers but i have cut a small sample section.
View 9 Replies View RelatedI have multiple graphs that read from rows varying in length from 2 to 60 columns long, depending on user input. I need help in creating a macro that readjusts the graphs to read from smaller/larger data set.
I created one that said: If user input = 1, then set graph to (A1:A1), If user input = 2, then (A1:B1), but this is really inefficient. Is there a better way?
i have this macro that sorts out a few cells
View 4 Replies View RelatedI am trying to update a text box from a macro. The text box is outside of the chart area. I am using these two lines and it is not working.
Sheets("chart1").Select
ActiveSheet.Shapes("Text Box 2").Text = strSql ' a string variable
I have a macro that is supposed to update 3 pivot tables with the change to just the first one but it doesn't work. Can anyone help?
Here is my code but nothing happens when I change the first pt (C & S). It doesn't update the other 3 tables.
Private Sub Worksheet_Calculate()
Dim PF1 As PivotField
Dim PF2 As PivotField
Dim PF3 As PivotField
Dim PF4 As PivotField
Dim x As String
Application.EnableEvents = False
Application.ScreenUpdating = False
Set PF1 = ActiveSheet.PivotTables("PivotTable4").PageFields("State")
Set PF2 = ActiveSheet.PivotTables("PivotTable3").PageFields("State")
Set PF3 = ActiveSheet.PivotTables("PivotTable2").PageFields("State")
Set PF4 = ActiveSheet.PivotTables("PivotTable1").PageFields("State")
x = PF1.CurrentPage
PF2.CurrentPage = x
PF3.CurrentPage = x
PF4.CurrentPage = x
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Pivot Table 4 = C & S this is one I want to make the change to
Pivot Table 3 = Sales Pivot
Pivot Table 2 = 2005 Pivot
Pivot Table 1 = SMG Pivot
I have three columns as below,
Column A current Amount
Column B Increse %
Column C Revise Amount
If I put revise amount in column c, than Column B update automatically or if I put % in column B than column C update automatically, update must be live
I have a spreadsheet of 30 or so tabs, all of identical layout, with columns B to AS used. Each day I have to drag a row down to collect data from various other spreadsheets, on each of the 30 tabs (different data in each tab, but same format), i.e. today I dragged down B216:AS216 down to B217:AS217 for all 30 tabs. I could record a Macro on the first tab and then play it for the remaining 29, but I have to record the Macro each day so it drags down the next row down.
I am after a Macro that will drag down the columns to the current date (which is in column A and is already entered down to the end of the year) on all tabs, so if I left it for 5 days etc, and then ran the Macro, it will drag down 5 rows. But if I had to run it 5 times that wouldn't matter. A button on the first sheet would be brilliant too, meaning I could open it up, click the button, and then all 30 tabs would drag down to todays date.
I ran into a dead with this simple but overlycomplicated task in Excel. How can I write a macro to update one workbook from many workbooks? My case. I have workbook A with 2 sheet tabs, sheet1 contains 6 columns: one for date, one for 0-20%, 21-40% ... 81-100%. Each of the rows underneath the heading contains a single data value ( total) from each of the 5 sheet tabs from the workbook being created each week of the month. Sheet2 build the chart reflecting the data gathered on sheet1.
I need a macro that will update workbook A sheet 1 or add the new data generated that particular week down the columns for each of the columns stated? How can I achieve this?
Every week I get a file download from SAP with multiple tabs and on each tab are variable number of rows (can by 10 or 20 or 200, doesn't matter). I recorded a macro to add additional information that is required for the team prior to discussion each week (namely column D, E, K and O).
I am not experienced w VB but I know there has to be a way to account for the fact the rows vary each week. See my code below (pasted only a portion covering a couple tabs but you can see in the first part how it was 11 rows this week. Next week could be 25 rows. So how do I properly reflect that in the macro?).....
VB:
Sheets("BPTO").Select
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-1],css,2,FALSE)),(VLOOKUP(RC[-2],css,2,FALSE)),(VLOOKUP(RC[-1],css,2,FALSE)))"
Range("E2").Select
[Code] ......
I'm no expert with my vba, record a few macros and make alterations to existing code but this is driving me crazy.
I have a simple script which I would like to update all DB links and all Pivot Table information, which my code does but i'm currently having to run it twice before it shows the correct results.
I'm trying to make a scatterplot so what I did was recorded a macro and now I want to be able to update it using a for-loop. Here's my code.
View 4 Replies View RelatedI have created this macro below however if on the worksheet I insert a line or lines the macro range G25:G107 does not change - it remains static.
How can I get an insert of a line to increase the macro range by the number of inserted lines. For example if I insert 2 lines then the macro range s/b now G25:G109.
I have a time sheet which is used by around 15 people. Part of the timesheet is a userform which adds a new sheet and names it with the seleted month and year. I have made a few changes to my timesheet which I wish to update on other peoples sheets. I am going to send out a speadsheet with a macro that people can run and it will automatically make the changes to their timesheet. I have done all the work for updating the various formats and formuals but I have hit a bit of a brick wall when trying to change the code on a user form via a macro.
It would be great if I could either overwrite all of it or add a some lines of code from a specific line number.
Anyway, I have 70 Sheets ( Tabs ) which when i'm finished will have the same cells in the same location of each sheet, referring back to a different line in a Data sheet at the beginning.
IN order for me to get there i need to edit each of the relevent cells but changing the formula so that it reads the next line down. I was thinking i could maybe get a macro whicle will increment each of the formula cells contained with a Named Range, this would make it more accurate than using the Find/Replace method of updating the formula's on a per cell basis....
Basically I want to set up a workbook, which will have a sheet called "Database" and then sheet1.
I want to use sheet1 as a "add to database" sheet, where users will simply dump in some data into sheet1 and the marco will format it so it matches the headers of the data base and then it will add/update the database sheet with the info (and new info if its new) from sheet1.
Here it is in pictures.
This is basically what the database sheet will look like (but much much much more rows of data)
As you can see this is a job database with some columns for POD, Connote, etc.
This is what the update sheet1 will look like when someone dumps some new data in (I can easily write a macro that formats it to match the database)
So basically I want the VBA to merge this sheet1 data into the database sheet, as you can see, there is simply a few 1's added to various columns for "Car Dudes" and "Robin Manufactures" but "Jimmys Lollies" row is completely new and not in the database.
So the marco would basically match up either the Job number or the HB number of the rows in sheet1 and if there is a match in the database sheet it will update the cells data, and if there is no match it will add the row to the database sheet.
The issue is the data in sheet1 might not be fully complete all the time, it might only have a job number and not a HB number, it might not have a client name or whatever. Basically the data users dump into sheet1 will come from many different reports, many different sources, what I'm trying to do is essentially merge all this data into a common formatted database sheet, for further working.
So in a nut shell, a macro to:
1. Match Job/HB numbers in sheet1 with rows already in database sheet and update row accordingly with any new data.
2. Add any new Job/HB rows from sheet1 into database.
I am trying to write a creative procedure that on workbook open will check the name of the worksheet if it includes the day's date in sheetname & if it doesn't rename the sheet & delete current region from A1, else exit the sub.
Then runs a query on an Access DB to place in the above worksheet.
I need this due to novice XL & DB (8)users.
This is what I have at the moment on a test DB courtesy of J Walkenbach's sample files .....
I have the following macro:
Sub ScheduleUpdate()
WaitHours = 0
WaitMin = 15
WaitSec = 0
NextTime = Now + TimeSerial(WaitHours, WaitMin, WaitSec)
Application.OnTime EarliestTime:=NextTime, Procedure:=LinkUpdates()
Application.Run
End Sub
Sub LinkUpdates()
Sheets("PRICING SHEET").Select
Sheets("PRICING SHEET").UpdateLink Name:="P:CommonPRICINGCOMPILATION.xls", Type:=
xlExcelLinks
End Sub
The code does not work at all, just the skeleton to have an idea. I also need help recreating an entirely new macro which preferably works well!
The idea is that everyday at 8:00AM this macro starts automatically updating the links every 15 mins (SHOULD NOT MATTER IF THE SHEET IS OPEN OR NOT). But here is the catch.
This code should be the code for a checkbox. So when the box is checked, or TRUE, the macro should run normally updating every 15 mins. But when the box is unchecked, the macro should stop (no update occurring).
I want this macro because this sheet is linked to several other sheets and it needs constant updating. However, if I need to make a change in the main sheet, no updates should be happening.
I have a 15 excel files which are part of the forecast system. There is another file MASTER which is the host file for the macro. Every months multiple users are asked to update their forecast in the 15 excel files .As these files also display actuals, so they are linked to bunch of other files. The links to the files should not be updated unless I run my macro from MASTER to do the series of tasks.
Everytime these users open the files , they are asked to update links.
Is there a way for me to stop this warning and still able to update the links only when i run my macro from the Master.
I have several workbooks in folder C:Parts &Service where the data needs to be updated with downloaded files in C:extract
I have a macro that allows me to open up a workbook in c:Parts & SVC Sales. One opened I then need to select the appropriate file in C:extract
VB:
Sub Update_Workbooks()
ChDir ("C:Parts & SVC Sales")
Application.DisplayAlerts = False
[Code].....
The are two types of workbooks in C:Parts & SVC Sales "Parts Sales" and "service Sales" . The branch name is at the beginning of the file name
Instead of opening up each file individually in C:Parts & SVC Sales and selecting the appropriate csv file in C:/extract using the Update_Macro, I would like the macro to open up all the files in the directory C:Parts & SVC Sales and update each of these with the appropriate csv file in C:extract
The name and description type must match the csv files and then updated eg Br1 Parts Sales to be updated with csv file Br1 Salesperson 01-07-2014 (the date in the file is not important for match the parts file -the branch name for eg BR1 and "salesperson is) i.e Br1 Parts Sales must select BR1 Salesperson 01-07-2014
BR1 Service Sales Must be updated with Br1 Service order repair register.csv (branch name for eg Br1 in this instance name and Service order repair register (pertain to Service Sales) is important)
Br2 Parts Sales to be updated with Br2 Salesperson
Br2 Service Sales must be updated with Br2 Service order repair register etc
[URL]
I've got a spreadsheet that pulls data from a SQL server.
I'm looking to have a button on the first sheet that extracts the data from SQL and inserts it into sheet 2 of spreadsheet. The data connection from SqL is working fine but when I click on the button sheet 2 is blank.