I 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 attached a sheet where I have 4 graphs on one sheet. I prefer this to an overlay. I would like to add a 5th graph but it would make all the the graphs too small. Is there a way to make the graph background longer. For instance, extend the length of this so that I can put multiple graphs on it and then just scroll from top to bottom to review them? Also, how do I copy and paste these individual graphs into a newly created graph. copy/paste doesn't work on these?
Boiler.xls
I have this following code to create the PPT with a specific template. But in this, the first slide of the PPT are coming blank. How to remove those blank slides?
PHP Code:
Sub CreatePowerPoint()Answer = MsgBox("Are you sure you want to continue?" & vbNewLine &
"Clicking 'YES' will create the DU Dashboard PPT" & vbNewLine & "You may want to set the range & Update VOC graph",
vbYesNoCancel + vbInformation, "Warning: DU Dashboard graph")If Answer = vbYes Then Else Exit Sub
'Add a reference to the Microsoft PowerPoint Library by: '1. Go to Tools in the VBA menu '2. Click on Reference
[Code]...
I need to create a macro to create 3000+ graphs (maybe not necessarily all in the same worksheet or workbook). I have code for what I would like the graphs to look like:
Option Explicit
Sub OzgridExample()
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Data Sheet"). Range("C7:G7"), _
PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = "='Data Sheet'!R3C1:R3C7"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Data Sheet"
With ActiveChart ..................
I would like to create 2 graphs for each set of data in my worksheet. the first set of data that I would like to create a 2D line chart for is in cells AQ3:AS19 (not sure if that is the correct syntax??) and the second is AW3:AY3.
I would like to position the first chart (top right corner) at BA3 and re-size it so that it is 17 cells tall and 7 cells wide. the second chart should be positioned at BI3 and the same dimensions.
I would like to delete the chart legend for both graphs
then I need to repeat about 1000 times. Each new set of data starts 20 cells lower (ie. AQ3, AQ22, AQ41..). Or every time "Band" is found in column AP.
I am trying to use VB to create 50 charts each with 3 lines using data from 3 different excel spreadsheets in the same workbook. I am able to create one chart with 3 lines using data from the three spreadsheets, this is good. However I exprience problems when i try to insert a loop to create 50 charts that correspond the the rows in each spreadsheet. I am new to VB and am very inexperienced with VB. In each worksheet my data is arranged in rows. row 1 of each worksheet has the header information and rows 2 through row 50 have my corresponding data. for example A2 lists the name and F2 through T2 lists the data. I have tried a bunch of different things but nothing seems to alllow me to be able to create multiple charts which correspond to each row of data?
Sub Chart2PPT()
Dim arow As Integer
Dim acol As Integer
Dim StartPoint As Integer
Dim EndPoint As Integer
Dim rStartPoint As String
Dim rEndPoint As String
StartPoint = 2
EndPoint = 4
For arow = 2 To 5
rStartPoint = "f" & StartPoint
rEndPoint = "t" & EndPoint
Charts.Add
ActiveChart.ChartArea.Select
ActiveChart.ChartType = xlLine 'Type of graph........................
I've been using a VBA code to look through my spreadsheet and find any graphs in any tab and move it to powerpoint. I have about 70 tabs with 7 graphs each.
I have is that the order of the graphs in the slidepack isn't in the same as found on each excel tab.I also tried renaming them (chart1-chart7) but problem still remains.
There were 2 macros. One printed all of the defined ranges (40) on separate pages, and the other printed all the graphs ("charts" - 39) on separate pages. I tried to combine the VBA code to print each range and then the corresponding graph. Everything is still on separate pages, but it saved time because I didn't have to collate after printing. It seemed to work. Then I tried figure out if I could print them all to a specific tray of the printer as set in the workbook or as the printer default. Now both the combined macro and the original macro are giving errors.
Is the code I have correct to do what I am trying to do (print each range and then the graph all on separate pages)? Is there any way to put the output tray choice into the macro?
Sub Load_Data_Report()
'
' Load_Data_Report Macro (print all tables & graphs)
' Macro recorded 12/21/00 by xxx
'
' Keyboard Shortcut: Ctrl+j
'
[Code] ........
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 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
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?
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.
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'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 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 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.