VBA Macro To Create New Worksheet, Charts, Etc.
I need a macro to create the following worksheets and charts from an Excel data set:
Three (3) worksheets (already created manually in attached Excel file):
1. Chart Data.
(a) Column A in Chart Data is always numbered 1 - 600 (50 years x 12 mos/yr).
(b) Column I and column Y data sets (from Prod_Month) created in Chart Data. Each data set can be identified and collated with column F in Prod_Month (API) which is unique for each dataset.
2. Rate vs. Month - plot of Daily Gas (col. Y in Prod_Month) vs. Months (col. A in Chart Data).
3. Rate vs. Time - plot of Daily Gas (col. Y in Prod_Month) vs. Calender Time (col. B et. al. in Chart Data)
At a minimum, could someone help me create the Chart Data worksheet from the data in PROD_MONTH? Charting all the columns takes time from Chart Data but any data manipulation macro(s) help.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
VBA Macro Code To Create Multiple Charts
I need to create 63 charts from data which I have in two columns. I want to create multiple charts using one macro. For the first chart I want it to use cells K2:K80 as the x values, and M2:M80 as the y values. For the next chart I want it to use cells K81:K159 as the x values and M81:159 as the y values. For the next chart I want it to use cells K160:K238 as the x values and M160:M238 as the y values. I want to continue this, creating a chart for every 78 cells of data, all the way until the 63rd chart which uses K4900:K4978 as the x values and M4900:M4978 as the y values. I have created the following macro by " recording." This macro generates the first chart that I want: Sub Macro5() ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlXYScatterSmoothNoMarkers ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$K$2:$K$80" ActiveChart.SeriesCollection(1).Values = "=Sheet1!$M$2:$M$80" ActiveChart.ChartType = xlXYScatterSmoothNoMarkers ActiveWindow.SmallScroll Down:=-3 End Sub How can I alter this macro to create all 63 charts?. It seems like there is an easy way to do this, but I don't use macros very much (at all).
View Replies!
View Related
VBA To Create Charts/Graphs From Non Continuous Ranges
I have an excel sheet populated with loads of values. There is a space between each set of data. I need to draw a graph for each section and i cant work out how to do it. I have attached the sheet in question. The gap between each column has "space" written on row, which is needed to prevent it being deleted. The drawing of the graphs needs to be automated as there is going to be 100's of sheets containg lots of data. The column size of each section can change so it needs to be dynamic. I have attached an example sheet
View Replies!
View Related
Macro To Create Dynamic Charts
i want to create dynamic Excel Charts in one sheet ,The sheeet contain dynamic columns and rows and i want to create for each 4 columns a chart in a new sheet ,i started to try it in VBA and didnt founs the way to select 4 columns as a range, i succeseed to do it with msgboox that the user enter the range but i want to do it in a loop
View Replies!
View Related
VBA Macro To Create & Format Text Box On Worksheet
I have code that creates a textbox on a worksheet, copies the contents of a cell from another worksheet to that textbox. I want to be able to select that text and format its appearence ( center the text, bold and font style and size). This is what I have so far but I do not know how to select the text that was imported. Sub textmove() Dim bname As String Sheets("cover").Shapes.AddTextbox(msoTextOrientationHorizontal, 96.75, 512.25, _ 230.25, 120#).Name = "client" bname = Sheets("data").Range("a3").Value Sheets("cover").Shapes("client").TextFrame.Characters.Text = bname With Selection.Characters(Start:=1, Length:=17).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With End Sub
View Replies!
View Related
Create Dymanic Charts Or Graphs
I'd like to put a spinner button on a chart that will change the x-axis date range. The data is in day/month/year format, but ideally I'd like to have the spinner button move in 1 year increments. The most recent date should always be present and changes should just impact how far back in history to go. I have basically no experience with vba coding, so please be as thorough and simple with your responses/solutions.
View Replies!
View Related
Create Charts/Based On ListBox Value
i am having a problem of updating my graphs in the application i devloped, my application is like this, go to sheet 1 click showuserform button and suer form contains combo and list box select the sheetname in the combo and select two values in the list and click plot.it plots the graphs acording to the selected values but my problem is it makes a newchart every time when we select a new week range it is getting stacked...it is not getting updated with the most recently plotted graph ??? i am facing this problem when i am opening my application in other computer...u can see my problem when u open my application. i am attaching my workbook and this is the code i used for making the chart Sub MakeChart() Dim rng1 As Range, rng2 As Range Dim gs As String gs = ComboBox1.Value Application. ScreenUpdating = True 'First selected For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) Then Set rng1 = Columns(1). Find(ListBox1.List(i)) Exit For End If Next 'Last selected For i = ListBox1.ListCount - 1 To 1 Step -1..............................
View Replies!
View Related
Create SQL Query In VBA And Display Result As Table In Worksheet
I need the VBA script in excel that will do a SQL Query, for this case I need to select a value where there are 2-3 tags and between certain period then display the result as a table in a worksheet. Example I have a database with 3 types of tags "Tag A", "Tag B", and "Tag C", and each tag have a value with different timestamp. the database looks like this: No. Timestamp Tags Value 1 1-Jan-2010 Tag A 18 2 1-Jan-2010 Tag C 20 3 2-Jan-2010 Tag A 20 4 3-Jan-2010 Tag B 17 5 3-Jan-2010 Tag C 19 6 4-Jan-2010 Tag B 18 7 4-Jan-2010 Tag A 20 8 5-Jan-2010 Tag A 22 9 5-Jan-2010 Tag B 18 10 5-JAn-2010 Tag C 20
View Replies!
View Related
Macro Will Not Create New Worksheet And Workbook
Im trying to create a document that creates a new workbook when certain functions are selected and have written a macro to assist with this. Basically im working on a comprehensive checklist with all possible scenarios that will need to be tailored for use, so you can create a document with just what is applicable to you and not the whole checklist. I have attached a sample of what i mean, along with clearer descriptors of what the document is designed to do.
View Replies!
View Related
Create New Worksheet With Macro Buttons
Is it possible to use a macro to create a new worksheet with a button on it, that has a macro attached to it, that i can send out to people? I've looked at the Template example, but i'm not sure this will work when emailed as i'm unsure whether the template will be sent to them.
View Replies!
View Related
Create Multiple Graphs/Charts From Data Groups
I need to make a macro that creates a specified number of graphs depending on the file's number of data sets. I know the number of sets that are in the data, and I know the number of data points that were taken. Here is what I have: Sub Graphs() Dim Startpoint As Integer Dim Endpoint As Integer Dim count As Integer Dim xStart As String Dim xEnd As String Dim NumberSets As Integer Dim yStart As String Dim yEnd As String Dim DataSet As Integer Dim Data Startpoint = 11 'The first set always starts in row 11 Endpoint = Range("L4").Value + 10 'Thefirst set always ends after the value of L4+10 NumberSets = Range("L7").Value 'number of times I need the loop to work count = 1..........................
View Replies!
View Related
Macro To Create Pivot Tables And Sort Out Data In Other Worksheet
The first sheet is the row data given to me, the second one is where I want my data to be analysed automatically by macros. I managed to create a pivot table manually to give me the info I want but then I still have to copy all the results from the pivot table sheet to my analysis sheet. How can I create a macro able to do all that by it self? I tried to record a macro using the recorder but then when I try to play it it gives me an error straight at the beginning in the definition of the pivot table I think. An example of what I want the pivot table to do is: in the first sheet, go in the small table in column K and L, take the value of L1, then in the row data in column A to J, in column C look for the value in L1, once found, look for the value 1 in column J then do the average on the values in column E and put the result in my second sheet in cell F5 then do the same but look for 2 in column J and put it in F8, then 3 in F11, ... until 7 found (the data is in ss.000 and so the results in the second sheet should be formated the same way) then do the same all over again for value in L2 etc etc etc this should be done for the 6 values in column L, each having values up to 7 in the J column. Then I need to do the same for other columns and not only averages but minimums as well but I can adapt the code I think.
View Replies!
View Related
Create Button & Assign Macro When Worksheet Added
Is there a VBA code that will enable me to create a button and assign a certain macro to it everytime I insert a new worksheet? how to insert a new worksheet with VBA, what I want is that when I insert that worksheet, there is already a button there with a specific macro(already made) assigned to it.
View Replies!
View Related
Planting Your Name Into A Worksheet/charts
I am currently completing work for a senior manager who has the tendency to show off to the other seniors about various ideas/spreadsheet programs, usually at the expense of myself, the creator. Is there a way I can put my name on a particular worksheet, and (even better) any charts that are created using it? I'm not trying to be spiteful; just trying to get my name known as these things are passed around the whole company.
View Replies!
View Related
Charts Missing When Copying Worksheet...
Does anyone know why, when I copy a Worksheet (right click Worksheet > Move or Copy > Create a copy), Excel occasionally fails to copy across the embedded objects contained within the Worksheet (e.g. Charts, Command Buttons, etc.)? This is very frustrating, and I'm wondering whether it's the way my Worksheet is configured, or whether this is a known bug.
View Replies!
View Related
VBA Macro To Create Multiple Data Validation Lists From Variables & Named Ranges
I cant seem to find the correct syntax for creating 14 validation lists using array members as the source of the named ranged. The validation lists are stored on a different worksheet, the Named Ranges are created fine, as are the ranges that are having the validation applied. The Syntax I am having a problem with is Public Sub assignDVList(WSD As Worksheet, sListName As String) Dim DVListName As String DVListName = "DV" & sListName Application.Goto Reference:=sListName With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=" & DVListName It is the Formula1:="=" & DVListName that is creating the headache. The sub is called as the array moves through the columns, using the header row as the Name for the Named Range, and the data Validation worksheet uses the same naming except it has DV in front.
View Replies!
View Related
Dsiplay Multiple Pivot Charts On One Worksheet
I have a spreadsheet with multiple pivot tables and graphs. Is there a way to add a scrollbar on one sheet so that when you scroll through it, it will display(one by one) each graph that I created with the pivot table(s) so that they're all on one page(still dynamic) as opposed to them being on multiple worksheets
View Replies!
View Related
XYScatter Charts In VBA
I am having lots of issues with generating a scatter chart in vba. I tried recording a macro but the only thing that recorded was my different selection clicks on the chart. No formatting was recorded. I have the following ....
View Replies!
View Related
Creating Charts With VBA ..
I've got a table which has additives in Column A and products on Row 1. There are 13 additives and 10 products. Each combination of product and additive has a price. So I have 130 different prices in my table. The prices change monthly and the new prices are added to the sheet so that last months prices will move to left so that the newest price data table is A1:J13 and previous month is K1:T13 and so on. I need to make an individual chart to every combination which contains the history of the price changes. I have two cells (A20 and B20) where the user can pick a product and an additive (created a menu by using Data|Validation...). These selections are combined in a cell C20 (=A20&B20). Then I have a macro that is linked to the cell C20 and is basically Case Is = 1st combination --> Call Macro1, Case Is = 2nd combination --> Call Macro2 etc. That macro will work out just fine, BUT is there any way I could make all the 130 charts easier? Now I've recorded a macro that creates the first chart and then I've just copy-pasted it, changed the attributes (cell numbers, names and so on) that define the data to the next charts. This will take ages... Every combination will have at least 13 changes (12 monthly price cells and the name of the cart) so in total this will mean about 1500 manual changes. In my case this will mean a high danger of typos. I know, it would be SO much easier if I could give you the Excel file to look at...hopefully you understood what I mean. I'll only follow the changes within a year so prices older than a year will not be shown in the charts.
View Replies!
View Related
VBA And Charts In 2007...
I have a piece of code that arranges a chart to fit the data it should show. The code used to work perfectly in the old Excel version, but after an "upgrade" to Excel 2007 I get "Automation Error" from the line where the scale type is set.
View Replies!
View Related
Creating Pie Charts With VBA
Now I need to create multiple pie charts based on data in tables on many sheets. I need to create the same set of charts, for each sheet separately. I recorded a macro for one sheet, which created following ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlPie ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).Name = "='GALLERY ON 4TH'!$B$2" ActiveChart.SeriesCollection(1).Values = "='GALLERY ON 4TH'!$D$6:$D$8" ActiveChart.SeriesCollection(1).XValues = "='GALLERY ON 4TH'!$A$6:$A$8" Range("A11").Select ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlPie ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).Name = "='GALLERY ON 4TH'!$A$6" ActiveChart.SeriesCollection(1).Values = "='GALLERY ON 4TH'!$B$6:$C$6" ActiveChart.SeriesCollection(1).XValues = "='GALLERY ON 4TH'!$B$5:$C$5" ActiveWindow.SmallScroll Down:=3 Range("A11").Select ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlPie ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).Name = "='GALLERY ON 4TH'!$A$7" ActiveChart.SeriesCollection(1).Values = "='GALLERY ON 4TH'!$B$7:$C$7" ActiveChart.SeriesCollection(1).XValues = "='GALLERY ON 4TH'!$B$5:$C$5" However I cannot automate. I tried different methods for creating the charts, as found on the site [prefer .chartobjects.add(x,y,z,a) since I can immediately position the chart] , but get stuck at seriescollection everytime. It looks like when the chart is created by XL (with suggested data in it) I cannot address the series data. I got it right to generate a chartobject that is empty, then added the series data to it, but when opening the new chartobject, it is again populated with suggested data. How do I generate an empty chartobject everytime, so I can insert seriescollection info with code? Any other way to generate charts?
View Replies!
View Related
Creating Multiple Charts Via VBA
am trying to create multiple pie charts now but I'm having problems my errors are highlighted in red. What I'm trying to achieve is to create a pie chart for the first table then another for the next table and so on but its not working out.
View Replies!
View Related
Create Charts From Same Data But Each Based Ob Data Criteria
Hi, have a question regarding dynamic charts, specifically I am trying to create several dymanic charts based on data in a single column (So i can only say my first set of data will be starting a a set location in the chart, the rest has to shift down based chart data above it). This data should organized in charts depending on the data in several other columns. For instance, need to pull out values from column based some ID and some PartNumber. A sample excel or VBA would be much appreciated. I know VBA but do not know Excel VBA much.
View Replies!
View Related
Worksheet Function In VBA Macro Code
I am trying to write a function in excel to use the worksheet function "small" In the vba immediate window it doesn't return anything and in the spreadshhet I get a # NAME? error when called in a cell the code is below. Sub UseFunction() Dim myRange As Range Dim answer As Integer Set myRange = Worksheets("Sheet1").Range("A1:F6") answer = Application.WorksheetFunction.Small((myRange), 1) MsgBox answer End Sub
View Replies!
View Related
Run VBA Macro Code On Protected Worksheet
I have incorporated print/print-preview command buttons/VBA into a workbook with protected sheets. To enable these command buttons to function when the relevant sheet is protected, I have had to add VBA code to unprotect the sheet before generating the print preview, and then to protect it again afterwards. However, the code I have used (see below) prompts the user to enter the protection password, is there any code I can use where I can write the password into the code itself to unprotect the worksheet without the using having to enter the password? 'Unprotect Sheet ActiveSheet.Unprotect 'Print preview & cell formatting code 'Protect Sheet ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingColumns:=True
View Replies!
View Related
VBA Macro To Copy Blocks/Areas & Append To Another Worksheet
i am making a spreadsheet for recording employee telesales activity each day. administrators will update a shared workbook each day with the information employee names are across the top and the activities are down the side (calls, CVs sent, interviews, meetings) each day has it's own block of data. on a friday I would like to run a macro to select these blocks and paste them into the first free row on a separate worksheet. i have something similar working elsewhere which copies one column and pastes that into the last free column of a new sheet but i have no idea how to do it for rows going down a page. I have attached a file example to clarify.
View Replies!
View Related
VBA Macro To Multiple Text Files Based On Headings On Worksheet
I have created an Profit&Loss excel sheet with all the accounts in column A and then the values for each month, January to December in column B to column K like this: Column A...............Col B............Col C........Col D........Col E.....and so on Account 1..............2222............58452........5255.........5844.... Account2................5.................45...............25...........458 .... I've made a macro that creates a textfile and saves it on my desktop, but I get a text file with all the info in the same file, what I want is to get one text file for every month, that is text file number one(january) is: The heading info+column A+columnB text file number two(February) is: The heading info+column A+columnC text file number three(March) is: The heading info+column A+columnD and so on In the heading info I have to state the period and then I need the macro to change this for every text file, that is for the january file it has to be "1 to 1", for february "2 to 2" and so on. As it is now it will always say "1 to 1" because that is the values I have entered. Sub btnSkapaFil_Klicka() Dim strAnv As String Dim strBeloppJan As String Dim strBeloppFeb As String Dim strBeloppMars As String Dim strBeloppApr As String Dim strBeloppMaj As String Dim strBeloppJun As String Dim strBeloppJul As String Dim strBeloppAug As String Dim strBeloppSept As String Dim strBeloppOkt As String Dim strBeloppNov As String Dim strBeloppDec As String Dim strSavepath As String Dim intRad As Integer Dim intStartrad As Integer Dim intSlutrad As Integer Dim fs, a strOrder = Range("I2")...............
View Replies!
View Related
Create Macro Code With VBA Code
I'm trying to add buttons to an excel spreadsheet at runtime. Each row in the spreadsheet should have its own buttons. I was able to create and edit them with the ActiveSheet. OLEObjects.Add() function, but after that, when i was trying to create code dynamically to react on the buttons' click events excel crashes (actually it works for one button, but not if my routine for adding a new button and event code is called more than once in a row!) The code below works if the AddCmdbuttonWithCode() is called once, but crashes if it is called two or more times. Excel tries to restore the document after the crash and the first button and its corresponding click event code is visible and works... but NOT the second button and its event code... The only way I can create multiple buttons right now is by calling my method once per click, opening the vba editor, changing the parameters for my AddCmdbuttonWithCode() routine and execute it again. After that I have mutliple buttons in different lines which all work fine (so the concept seems to work). The problem seems to be the insertLine method, since everything seems to work if i leave it out (except for the code generation of course, since this is done by this part of the code :-) ). Is it possible that calling the insertLine Method can't be called multiple times? I don't know... any ideas? Feel free to test my code - it's small, easy to understand and has comments. 'this code calls the method which creates the buttons in specific cells with specific names Private Sub CommandButton3_Click() 'the first call always works! AddCmdbuttonWithCode "Edit_111_111_114", 23 'the second one crashes excel AddCmdbuttonWithCode "Edit_111_111_115", 27 End Sub
View Replies!
View Related
Macro-generated Values Not Updating Charts And Sums
The question is pretty much described in the post title. Here is the situation: I have a workbook that has data plugged into it manually throughout many sheets, and at the end there are a few sheets with different graphs and charts that are generated by a macro. This macros works perfectly - all the data is accurate and the figures are perfect when compared with a manual count. The problem I have is that the cells where the macro puts the summed values are in a chart that is supposed to create a bar graph. Normally, if I change a value that a bar graph uses I see the bar graph change - but that does not happen with the cells that have values placed in them by the macro. I also have a section that sums the values in the chart to make sure it's running right, and these values do not update automatically as well. The cells have the formula =SUM(B3:B12) and so on, in them. Right now, every time that I run the macro I need to go into the cell with the formula, hit 'enter', and then it does the summation. Is there a way to correct this problem, or is this something that is part of excel?
View Replies!
View Related
Pasting Charts Between 2 Instances Copies Macro Code Into Hidden Workbook
Consider two instances of Excel - one with a workbook containing a chart and some VBA code, the other containing a blank workbook. When I paste a chart from the first instance of Excel to the other, all the VBA code from the original workbook is also pasted into a new third hidden workbook in the second instance of Excel! If that weren't weird enough ON WINDOWS XP (but not Windows 2000) if the VBA code included WorkbookOpen or AutoOpen this code is executed in the second instance of Excel, which of course fails if it references sheets that only existed in the workbook open in the first instance of Excel! This is the first time I've seen the same version of Excel do different things on different versions of Windows! Example attached - What's going on!? Auto Merged Post Until 24 Hrs Passes;...note: this does not happen when pasting charts between workbooks open in the SAME instance of Excel
View Replies!
View Related
Using VBA To Create VBA Code On The Fly
I have a vba module that reads a value from a txt file and saves that value in a variable called "iniInfo". I want to permanently assign that value programatically by using VBA code to create a Public variable e.g. "PUBLIC CONST conFIg = iniInfo" in my modGlobalVar Module. (Of course, this would only create the code if the "conFig" variable doesn't already exist.)
View Replies!
View Related
Resource Error Message "cannot Complete This Task With Available Resources" When Changing Charts Thru VBA
I have the following code for a sheet in my workbook that has 3 charts: Private Sub Worksheet_Change(ByVal Target As Range) Application.Calculation = xlCalculationManual ActiveSheet.ChartObjects("RdteObs").Chart.SetSourceData ThisWorkbook.Names("GSumRdteObs").RefersToRange ActiveSheet.ChartObjects("RdteWip").Chart.SetSourceData ThisWorkbook.Names("GSumRdteWip").RefersToRange ActiveSheet.ChartObjects("RdteExp").Chart.SetSourceData ThisWorkbook.Names("GSumRdteExp").RefersToRange Application.Calculation = xlCalculationAutomatic End Sub but whenever the sub runs, I get this error message: "Excel cannot complete this task with available resources. Choose less data or close other applications." Does anyone have an idea what's going on?
View Replies!
View Related
Create New Worksheet
how do I copy the last worksheet (e.g. sheet3) using the name keyed in the textbox1 as a sheet name? I got the following code but it creates 4 worksheets instead of just one with the new name. Worksheets("invsetting").Activate ActiveWorkbook.Sheets.Copy after:=Worksheets(Worksheets.Count) Worksheets(Worksheets.Count).Name = Ucase(Textbox1.value) If I got 10 names in column "C", can I use the following code to create the worksheets with names in column "c"? For k= 1 To 10 ActiveWorkbook.Sheets.Copy after:=Worksheets(Worksheets.Count) Worksheets(Worksheets.Count).Name = Ucase(range("c" & k).value) Next
View Replies!
View Related
Create New Worksheet From Commandbutton
I have a userform which collects fuel data from a pump and populates 2 worksheets (pump1) and (pump2). What I am tyring to accomplish is the following: When fuelling is finished for the day, the operator selects the (Shutdown) button. When this happens I need the userform to perform the following. 1. For each worksheet (pump1) and (pump2) create new worksheets named (pump1_date) and (pump2_date). 2. Populate these new worksheets with the entire data from the original worksheets. 3. Clear out all but the last row of data from the original sheets. The last row contains the meter readings for the fuel pumps and needs to stay as an opening ballance for the next day. This last row to become the top row of the original sheet.
View Replies!
View Related
Create A Button In Worksheet
creating a button (using VBA code) in a excel worksheet that can initiate a range of output data in a col (say (C1:C100)) by activating the therein formula of each cell (same as what F2 does) and then entering the cell. Actually I have to do manually each time for each output cell after opening the worksheet that I don't want.
View Replies!
View Related
Create New Worksheet With Function
Can you have a function create a new worksheet? i know this is possible with a Sub but i haven't been able to do this with a function. Example is below. I'm planning to call the function from an Excel cell (i.e. =CreateNewSheet()) Function CreateNewSheet() Sheets.Add End Function
View Replies!
View Related
|
TRACKER
