Insert Row Into Column Of Data And Automatically Update Graph
Oct 16, 2011
A colleague recorded this macro to insert a row into a column of data which a graph is plotted from to automatically update the graph each time more data is added. I can't figure out how to simplify the VBA and stop it adding a row on row 57 rather than the bottom of the graph.
Code:
Sub Button1_Click()
'
' Button1_Click Macro
'
'
Rows("56:56").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B57:F57").Select
[Code] ........
View 3 Replies
ADVERTISEMENT
Mar 17, 2008
I have a macro that extracts data from a source workbook. The data is owerwritten every time that I use the macro. Now I have a graph that everytime I use the macro does not update with the new data, Is there a clever way to do that? The code for extracting from the workbook is the following:
Sub Extract()
Dim myFileName As Variant
Dim SourceWkbk As Workbook
Dim CurrentWkbk As Workbook
Dim testWks As Worksheet
ActiveWorkbook.Sheets("CURRENCIES").Select
ActiveWindow.SelectedSheets.Delete
myFileName = Application. GetOpenFilename("Excel files,*.xls")
If myFileName = False Then
Exit Sub 'user hit cancel
End If................
View 4 Replies
View Related
Jan 24, 2014
I am trying to do the following
Unit Percentage Score Unit 1 Percent ScoreJohny SmithBetty Donovan
Unit 1 Operations & Algebraic Thinking2100.00%50.00%
Unit 1 Measurement & Data3100.00%66.67%
Unit 1 Geometry0#DIV/0!#DIV/0!
Unit 1 Number & Operations Base Ten16100.00%93.75%
1-I would like to create a type of link or button that would exist in the cell "Johny Smith" so that when a teacher clicks on that name excel would generate a graph or pie chart for the specific data associated with Johny and when a teacher clicks on Betty a different graph would pop up. I know teachers can just highlight the data and insert charts, but many of them don't know how to do that. I have been asked to make the process a one click function.
View 1 Replies
View Related
Apr 8, 2012
I am having a graph in ppt which should be updated with a data i have.. i started lyk
Code:
Set xlsApp = CreateObject("Excel.Application")
xlsApp.Visible = True
Dim path As String
path = "C:MydocumentsBook1.xls"
Set newxls = xlsApp.Workbooks.Open(path)
Worksheets(sheet3).Activate
I donno how to update it using vba code in ppt .
View 1 Replies
View Related
May 13, 2014
I have a report that has rolling week by week data (as in YTD). The graphs I have are based on a 13 week period. As I add a week (done by VBA import) I want to update graphs to lastest 13 weeks as in last 13 columns of data on data sheet.
View 4 Replies
View Related
Dec 11, 2008
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?
View 5 Replies
View Related
Jun 9, 2006
I have a graph that is showing the date on "Y" axis and a value on "X" axis, when I add a new date and value to my data the graph does not update, it just shows the data when I first created the graph.
How do I get the graph to display the new data I entered ?
I created a line graph by clicking on the "A" in column "A" (Thats where my date is) then clicking on the "B" in column "B" (Thats where my values are) and clicking the create graph button then clicked finished.
View 6 Replies
View Related
Jun 8, 2009
I have several macros running on my workbook, referring to different cells. When I add/remove a row/column in my workbook, I need to manually update all cell references in the Excel macros so that they point at the correct cells after the new rows/columns have been inserted.
View 2 Replies
View Related
Jul 4, 2006
I want to do is use a userform to automatically create a graph from the data that is shown in the worksheet. The data in the work sheet will grow and shrink all the time. Am i right in thinking I am meant to create a dynamic range? I know how to do it but am not too sure what I am meant to be naming. And secondly I am not too sure how I set up the button to produce the graph.
View 4 Replies
View Related
Mar 17, 2007
I have number of column.Say 10 columns. in Column say J5 I have word daily varience. I want a macro to check word daily varience and go 2 steps to the left and insert the column.
View 9 Replies
View Related
Feb 7, 2008
I'm unable to find VBA code to insert a blank row when the value in Column L changes. For example if cell L2 = 400 and cell L3=500 I need to insert a blank row between L2 and L3. I need the macro to search the entire sheet which will have variable numbers of rows but Column L will always have data.
View 3 Replies
View Related
Jul 28, 2014
After importing a data set into the worksheet entitled "SPACE", I am trying to find the column entitled "Variability Cpu" and then for every cell in that column that has a value in it, I would like to insert a new row beneath that cell. I thought my code made sense, but I get the "Run-time error '424': Object required" error on the line in red below so apparently I'm missing something. why I'm getting this error (doesn't the object variable shtS refer to the worksheet object "SPACE"? Why is it saying object required?). Here's what I've got so far:
[Code] .....
View 5 Replies
View Related
Jul 28, 2009
Produce a chart (not a problem) that can be updated should the length of the X and Y data change?
This may not even need to be a macro, if it is possible to have a chart whose X and Y axis updates depending on the contents of a column?
If you search through the history of even this forum you will see quite a lot of people are looking for this solution.
View 9 Replies
View Related
Nov 19, 2009
I already have various dependent validation lists established using the INDIRECT function.
Now I want to figure out how I can automatically change the selected values given the selected list. For example:
Cell D11 is a validation list with options: Boat/Car
Cell D15 is a validation list where if D11 = Boat the options are Catamaran/Sailboat/Tugboat. If D11=Car the options are Convertible/Sedan/Midsize.
The problem is that if D15 is preselected as Sailboat, for example, and you go back and change D11 to equal Car, cell D15 remains an option from the Boat validation list. I would like it to automatically update as the 1st item on the car validation list.
View 9 Replies
View Related
Aug 19, 2006
I have attached a copy of the chart and data. My chart starts on the bottom. I had to take out a lot of data but beneath the data shown is much more data..
I want my chart to automatically update when new data is added. Here are the issues. I have several lines of headers and below that my data. Then below my data I have other data that I don't want displayed in the chart (its not shown here because of size limitation of attachmnet).
The new row will be added each time right below the header, right above the
most recent data given. So a row will be added above row 8!
View 9 Replies
View Related
Nov 19, 2008
I have two worksheets in my workbook;
1. Table
2. Raw / Source data.
I have entered all the formulas into my table (sheet 1) referenced to the source data but I want to upadate the source data regularly but when I delete sheet 2 all the formulas come up with #Ref error. how i can update my source data so my table automatically updates
View 2 Replies
View Related
Apr 15, 2011
On Sheet2, data validation is applied to cells D21:D70 to limit data to values to a list, which is a named range on another worksheet. There are ranges on two other worksheets with the same data validation rule. The range for the list is ‘Sheet1’!D12:D26 (which is named “Processes”).
On Sheet1, the user enters a list of their processes. Then on Sheet2 (and the other sheets where the data validation is applied), they select one of the processes from the list. This all works fine until they go back to Sheet1 and modify one of the previously entered values.
For example, "Brush Teeth" is entered as one of the processes on Sheet1. It is then selected on Sheet2. Then, the user goes back to Sheet1 and changes the value to "Brush Hair." When you return to Sheet2, the value is still "Brush Teeth." It can be manually be changed by selecting “Brush Hair” from the drop-down list (“Brush Teeth” is no longer available), but it doesn’t automatically update.
Data validation was applied to a single cell (A3) and the list was on the same worksheet (B1:B3).
Option Explicit
Private oldValue As String
Private Const DVCell As String = "A1"
Private Const dataList As String = "B1:B10"
[Code]...
'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in.
On Sheet2 (I assume I need to add this code to every applicable sheet), I tried editing the first part of the code as follows (changes in bold), but when I click on any cell on Sheet2 that has the data validation rule applied, VB returns the error “Run-time error ‘1004’: Method ‘Range” of object ‘_Worksheet’ failed.” If I go into the debugger, the second instance of “If Not Intersect(Target, Range(dataList)) Is Nothing Then” is highlighted.
[Code] .....
View 9 Replies
View Related
Sep 25, 2013
I have a worksheet with data in columns F,G,H and I. There is a total in column J. (=SUM(F1:I1)
I've written some code that allows a user to insert additional columns. However, I'm finding it difficult to find a way of changing the calculation in the total column automatically when a new column is inserted.
View 1 Replies
View Related
Oct 18, 2013
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.
View 13 Replies
View Related
Nov 4, 2008
I have a spreadsheet which I am trying to use to capture customer data. What I want to happen is whenever I enter a new record, the date appears automatically in a Date column ....
View 6 Replies
View Related
Oct 20, 2007
I am trying to make a line graph of a company's profitability. Each day I add a new page and use data I collect to determine the day's profit. I want the graph to update each time I add a new sheet. The cell that contains the day's final profit is the same cell on every sheet.
View 2 Replies
View Related
Apr 21, 2009
I have a Big Question about the SMALL Function in Excel. I would like to know if it is possible to use a syntax that will make the small function update my graph dynamically.
Personnel transfer out of the Department on know dates... but they come in to the Department all the time. I would like to know if I can increase the "range of my data" even if I don't have data in the cells (i.e. B2:B6 have data, I want the range to include B7:B16, which has no data)... When I have no data in the cells I get errors/ circular references...
Ideally, I would like be able to add or remove personnel and the SMALL function would "organize/update" my graph dynamically. Is this possible?
View 6 Replies
View Related
Jun 18, 2014
Anyway, I have a column graph that shows company totals on the left, in three columns, and sub-group totals to the right. These columns represent Revenue Targets (Blue), and current projections (Red) as of the current time. Ignore the green column. I would like to add incremental amounts to the Red column weekly, as the projections change. So I want to show an increment on top of the red column.
So if LG picks up 100 of additional revenue, I want to show the increment of 100 on top of the 2000 that is there, using a different pattern or cross-hatch.
View 3 Replies
View Related
Apr 22, 2009
What I'd like to do is; If column C contains data then insert a blank column and shift column C to the right.
View 4 Replies
View Related
Aug 5, 2008
Is there a way to make multiple charts off of 1 big data sheet. Sounds easy but there is more.
Sheet name is Field
Column A is Name ( about 40 different names)
Column B is Date (x axis primary and secondary)
Column D is Oil (y primary axis)
Column G is Water (y primary axis)
Column E is Gas ( y secondary axis)
Column M is MCFE (y secondary axis)
There is about 2-3 thousand rows, updated monthly. I need to make a graph by each well name.
1) can I make multiple graphs that can just look at the "field" sheet and then breakout just the 1 well name that shows up on the chart sheet that I create
I've tried pivot charts but you can't have secondary acess because it resets every time.
Basically, make multiple charts that will graph the data of a specific name in column A
View 9 Replies
View Related
Dec 6, 2006
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).
View 2 Replies
View Related
Jul 25, 2007
I have a lot of data broken up into numeric decimal form in one column. I need all this data to be transferred to different worksheets based on the amount of decimals. Attached is some of the data I need sorted. The first sheet is my main sheet, then the next sheets need to be ascending in numeric order by sorting the data with two decimals under the column Tear number system.
For example I need the next tab to be the data with 0.21 with 2 decimal places, then the next tab would be the data under 0.28 with 2 decimal places, then the data under 0.29 with 2 decimal places. So the 0.21 worksheet would be 0.21 and have all the data for 0.21.1, 0.21.2 all the way through 0.21.6.
I also need this to be compatible with new inserts to the main table. Meaning if a new number is entered in, the number and data would automatically be entered and sorted to the tab that the number pertains to. I have tried a vlookup function but have not had much success. I have also tried recording a macro but am having problems with that as well.
View 9 Replies
View Related
Nov 23, 2012
I am after a macro to insert a column lets say between column A and B. Once column inserted new column B should be filled by a text say "January" but it should be only filled upto the data of column A so if column A has data upto A600 then column B should be filled from B1 to B600.
I tried recording but it fills data upto B65000+.
View 2 Replies
View Related
Feb 2, 2014
i have two sheets, sheet 1 is a list of some items i sell on my website and the stock quantity.sheet 2 is a list of what my supplier has in stock.i want a quick way of my stock values on sheet 1 in column G to match the stock values on sheet 2 column E.
the product codes are the same (sheet 1 is column E for product codes, on sheet 2 the codes are in column B).
on my website (sheet 1) the products size options (column J) are more detailed than that of my suppliers (sheet 2 column F)
for example, my sheet (sheet 1) will say on some items for example MEDIUM: DRESS SIZE 10-12 but my supplier sheet will just say medium also my sheet (sheet 1) will say ONE SIZE or PLUS SIZE where as my suppliers (sheet 2) will be blank however this means that there is only one stock value to update anyway as there is only one size option
is it possible to magically make sheet 1 match the relating stock values from sheet two?
so on sheet 1, item 10035 Child Gangster Suit in size small would change from 7 to 13 and item 10001 Jumbo Syringe would change from 11 to 20
i would also need the updated values on sheet 1 to be highlighted so i know if any have been missed, for example my supplier may have stopped supplying an item in medium but obviously i would still have a stock value for it on my sheet so therefore that value wouldnt be updated so i would need to be made aware of this so i can then remove the option (which has to be done manually) on my website...
View 2 Replies
View Related
Jun 30, 2009
I have a word document that has all kinds of graphs embedded in it. When I double click the graphs, an excel spreadsheet comes up. The spreadsheet contains quite a few years worth of information. I, however, need it to automatically refresh to show only the most recent 18 months of data on the graph. The documents are in MSOffice 2007.
View 9 Replies
View Related
