Macro To Create Gaant Chart Style Table From Leave Data?

Jun 11, 2014

I have a leave history report with data for leave taken for the 2014 year. The sheet contains the following headings:

Name; Leave Start Date; Leave End Date; Date Paid; Days Taken; Roundup; Offset

The roundup column rounds the days taken up, for example if someone took 0.4 days off it would round it to 1.The offset column just subtracts the Roundup value by 1 (=Roundup-1) I think we will need this for what I am trying to do.

I have created a new spreadsheet labelled gaant chart, this shows the name of the employee with the working dates for the year and has the following data:



What I would like for the macro to do is lookup Employee 1 from my gaant chart table and search the leave history data if it finds a relevant start date for the employee it marks that date with a 1, it then looks in the offset column and marks the number of offsets to the right. For example if employee 2 took leave from 1/1/14 to 3/1/14 the macro would mark a 1 on the 1/1/14, and search the offset column which would have 2, it will then mark a 1 in the 2 cells to the right of 1/1/14 also (these being 2/1/14 and 3/1/14)

View 1 Replies


Fix Chart Style To Pivot Table

Mar 29, 2007

I have a data table and want to create six charts. In theory pivotcharts would be the way to go. However every time I refresh the charts they revert to excel's default chart and all my colour scheme and nice layouts are lost.

View 3 Replies View Related

Create Chart From Table Of Data?

Jun 19, 2014

Trying to create a chart from a table of data, however my column of data has zeros throughout. How do I insert a chart with just the data that are not zeros?

View 1 Replies View Related

Macro To Create A Statistics Table From Another Data Table (containing Merged Cells)

Apr 14, 2009

I would like to have a macro to automatically generate a statistics table (on the "statistics" tab) with the 5 following fields:
Fragment names / # samples / # of failed samples / % of success / # of variations in the fragment (SNP). At the bottom of this table, I would like to have a cell with the average % of success for all fragments. The data to generate these statistics are on the "gene name" tab (please note that this name will change every time I will work on a new gene). To make things easier, I think the macro should be run from this tab.

1. The Fragment names are displayed in row #5. I use one column per variation per fragment. If one fragment has 3 variations, there will be three columns and I will merge together the fragment name cells. The fact that some cells are merged can be a problem when copy-paste to the stats table (as I would like to get rid of the merging).

2. # of samples corresponds to the number of cells in blue in column A. The number of samples can change from one report to another but is always constant in the same report.

3. # of failed sequences. In the table, I type "Failed Sequence" (if the analysis has failed) and "Missing Sequence" (if the analysis has not been done). When a sample is failed or missing, it is for the who fragment, no matter how many variation there is in the fragment, so I usually merge the cells of all variations for this failed sample.

4. % of success: this is quite easy #sample/#of failed+missing sequence for this fragment

5. # of variation is equal to the number of variations for this fragment (can be 0, 1, 2, etc.). When there is no variation in a fragment, I put '-- in all cells of the corresponding fragment on the "gene name" tab. Fragment 3 on my file is an example of 0 variation.

View 3 Replies View Related

Create Macro To Chart Data With Location As Object In Active Sheet

Oct 11, 2006

I have been trying to create a macro in excel to chart a selection of data and to output the chart on the active sheet where the data was taken (as opposed to a named sheet). So basically, I have about 300 worksheets with data, and I would like to have a button on each page that automatically charts that data when clicked, and outputs the chart to the page where the macro was clicked. However, I have not been able to figure out a relative reference that will allow me to make the LocationasObject reference simply the ActiveSheet as opposed to a specifically named sheet. See my code below, which references an output to a worksheet called "Charts". Right now, all of my charts are outputting to the sheet called "Charts", as opposed to the active sheet.

Sub ConsDiscChart()
ActiveCell.Offset(29, 11).Range("A1").Select
ActiveCell.Offset(0, 1).Range("A1:B1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Offset(0, -1).Range("A1:C24").Select
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

View 5 Replies View Related

Macro To Create Table From Spaced Data

Aug 26, 2009

I’m trying to do is create two tables from data evenly spaced throughout columns A to I. To make the screenshot easier to view I moved the second table under the first. The data is arranged so that each “Subject” has 42 columns and is arranged by “Type” and then by “Subject.”

The tricky part is that the number of subjects will vary so everything has to be done in some sort of loop. From the little I know about VBA I was able to record and edit a macro to add spaces every six rows and average the “MaxResponse.” (that’s what stage the screenshot was taken at) To make the table I think I need to write some sort of Dim statement but that is way beyond my comprehension.

Here is a link to a photo of the table

Excel 2003
VBA 6.5
Win XP Pro

View 9 Replies View Related

How To Replace Border Style With Macro Across Entire Data

Jul 11, 2014

How can I replace the border style with a macro across my entire data?

I would have assumed something like this would work:

[Code] .......

View 2 Replies View Related

Excel 2003 :: Filter Data In Graph / Chart (without Having To Create New Chart)

Dec 29, 2011

I have a spreadsheet created in Excel 2003 (which is what we use at work, unfortunately).

My employees periodically take a test to ensure they have certain items memorized (or are making progress to that end). The spreadsheet rows show all 46 of my employees, and their test scores. The columns are the dates that the tests are administered. I can create a line graph based on the chart data, and interpolate these data with no problems.

The problem is that there are 46 employees! 46 lines on the same graph make for a very cluttered, hard to understand visual. I want to simplify the view by "filtering out" some of the data.

I have an additional column in my spreadsheet for each employee's work area (Area 1, Area 2, etc), and another column with data based on first letter of last name (the values here could be "A-G", "H-M", "N-S" and "T-Z", for example). I figure i could filter my line graph based on these two columns. For example, somehow select just Area 1, and reduce the number of lines on the graph to 16. Or better yet, Choose "Area 2" AND "A-M" and end up with 7 employees (and therefore 7 lines on the graph).

Here's what i have tried:

1) Select the work area column, and use the Filter, which created a drop-down list at the column heading. When i use this drop-down list, i can easily filter the data in the worksheet by Work Area, but this is not reflected in the line graph, which still shows all 46 lines. The problem was that i forgot that i had set the Calculation Options to "Manual". Setting this to "Automatic" (or leaving it on Manual and pressing F9) solved the problem, as the chart now updates when i use the filters. Calculation options are under the "Formula" tab in 2007, or in Tools -> Options -> [either calculation or formula, i forget what it's called] in 2003.

2) Create several separate line graphs in several separate sheets. I wouldn't want to assign someone else the task of maintaining a spreadsheet of such inefficient design.

View 1 Replies View Related

Alternate Colors In Table With Table Style When String Value In Column A Changes

Feb 25, 2014

When I add a table in Excel, I can choose from Table Styles, different styles that set one color for even rows and another color for odd rows.

Is there a way to say to this Table Style that assign one color for rows that have the same string in column A in consecutive rows and another color when the string in colum A changes?

I mean,
If A1= XYZ, A2=XYZ assign blue to row 1 and 2.
If A3:A9=FTG assign green to rows 3 to 9.
If A10:A13=LLKF assign blue again to rows 10 to 13.
If A14:A22=WUR882 assign green again to rows 14 to 22.

and so on.

View 2 Replies View Related

Create Dynamic Chart From Source Table?

Apr 17, 2014

I have a table of company data, and I want to be able to select different companies data and create a chart automatically perhaps by pressing a 'create' button.

The table data contains three values I want to populate in the chart: red & green sales which I would like shown as a stacked bar, and red as a percentage of total sales which I would like on a different axis as a line.

I tried to create a macro but my VBA skills are almost non-existent.

View 2 Replies View Related

Create Pie Chart From Table Summary Results

Jul 2, 2009

Attached is five months of dummy sales data for eight products. My objective is to filter this data with date ranges (using Column A in Data sheet), and have the corresponding pie slices (shown in Pie Chart Sheet) be the summation of the Products grouped by their designated letter. Currently, the result I am getting is 6 pie slices for "Product A" and this is wrong. I want to see "Product A" show up only once in the LEGEND and only as one aggregated pie slice. The same holds true for the rest of the Product Letters.

I want to avoid using Pivot Tables. My objective is to keep my layout clean and simple. I would like to know if anyone out there could help me figure out a formula that will automatically catagorize all of the resulting filtered data by the Product Letter they share. Once the formula categorizes or groups together all of the resulting filtered data by Product Letter, I would like the formula to calculate a summation for each Product Letter category. After the formula finishes the summation task I would like the formula to feed these results to a Pie Chart. The resulting chart I would like to see will show individual pie slices for each Product Letter resulting from the date range filtering operation performed on Column A. The resulting letters can be anywhere from "A" to "H". My goal is to only have one individual pie slice representing each of the Product Letters resulting from a Date range filter. I thought such a task would be as easy as baking a pie, but it's turned out to be beyond my Excel skills.

View 9 Replies View Related

Macro Code Create Pivot Table Based On Dynamic Source Data

Nov 28, 2006

The "Sum" sheet can change its number of rows. The pivot table is based on it. I'm having trouble with the SourceData portion of the code in my macro ....

View 9 Replies View Related

Macro To Clear Some Data Within A Cell But Leave Other Data

Dec 24, 2009

What i want to do is clear some data within a merged cell but leave the rest.

I have added a sample workbook. The data i want to clear is highlighted in Red so the 11111 will be replaced with ...... and the 09/12/09 will also be replaced with .......

View 7 Replies View Related

Leave Gaps For Blanks & Zeros In Chart

Nov 21, 2006

Instead of treating cells with a blank or a text value as zero in a line graph, how can I create a gap in the line?

View 6 Replies View Related

Pivot Style - Modify 1st Style Under Light Category

Oct 26, 2012

Is there any way to modify the 1st style under the Light category? I like it because it's the most simple but it won't allow me to Modify or Duplicate. Is there a work around to duplicating/copying it? I've tried manually creating a new style but I can't get it to get the lines fills correctly.

View 2 Replies View Related

Create Annual Leave Database Using Excel?

Jun 27, 2012

I am trying to create simple leave data base using Excel. Is it easy or complicated?

View 2 Replies View Related

Macro To Create A Chart Over Used Range In Worksheet

Mar 20, 2012

I am trying to write a simple macro to create a chart over the used range in a worksheet. The first part of the macro correctly selects the used range which in this case is A1 to F19 when I single step through the macro. A listing of the macro is shown below:


Sub AddChartObject()
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select


When I place the formula given in MyArea as an argument for the source data, I receive a syntax error, so obviously it is the wrong argument. Numerous attempts to correct it also failed. As a sanity check, I placed the used range in an argument for the source data and was able to create the expected chart. fix the syntax error in the macro

View 4 Replies View Related

Excel 2010 :: Writing Macro To Create Chart?

Feb 12, 2014

I have 6 columns. The 5th and 6th columns are the most important. The 5th contains a figure and the 6th contains a type.

Bascially I am trying to do a basic chart showing the following 2 things. A. Do a percentage chart of column 6 (service) but not of percentage of hoem many lines the product is in. Not based on percentage of how many times Apples are shown compared to other products. I need it to add up everything, then do a percentage of total amounts of oranges and total of for Apples from the figures on column 5. Also, if possible, the pie chart will even show teh actual total figure per product in each (or next to) each pie chart slice.

know the easiest way to do this (step by step) in Excel 2010 and then I can perhaps write a macro for the future?

View 1 Replies View Related

Create Macro To Add Fill Effect On The Bars Of Chart

Jan 24, 2012

I'm trying to create a macro to add a fill effect on the bars of my chart.

However, I don't want it to change the bar's current color. Just to do the effect on the current color.

Sub fill()
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With


View 2 Replies View Related

Create Multiple Chart Macro From Single Sheet

Jan 31, 2014

I have a data set where the rows contain monthly sales data (from January to December) and the columns contain the names of employees (joe, bob, sue, etc.) and a single "group average" column:

I would like to create a chart for each employee showing the employee's monthly sales vs. group average. For example, the first chart would be for joe...

Then the next chart will be for bob and so forth...

View 3 Replies View Related

List Box To Create Chart Data

Nov 18, 2008

I am trying to have a list box that when clicked will change the chart data to whatever is selected. There are 2 options you can choose from. Order Cost (Net) and Order Total (Gross) The problem I'm running into is that the data is in 2 different rows. How can I create the drop down box to display the one that is selected? ...

View 9 Replies View Related

Excel 2007 :: Create Macro To Copy Chart As Picture

Sep 1, 2012

Trying to create a macro to run through the following steps when I select a chart and run the macro:

Paste>As Picture>Copy As Picture>As Shown When Printed>OK

I used the macro record feature and when I enter the shortcut it always returns the same chart. How do I get it to run on whatever chart I have selected instead?

I am constantly using this function to copy charts from Excel into PowerPoint presentations and am frustrated with all the extra steps it takes (versus just Ctrl-C).

View 9 Replies View Related

Create Surface Chart Via Macro Code Based On Selection

May 28, 2008

I want to write a macro to add a surface chart for a Data Range which includes 6000 data points. But the series selected for the chart don't cover all Data Ranges, only part of them.

Public Sub AddChart2(LastRow As Integer, LastCol As Integer)

Dim cht As ChartObject, currentSheet As Worksheet
Dim rng As Range, newRow As Range, srcRange As Range
Dim colIdx As Integer

colIdx = 5

View 7 Replies View Related

Create Bar Chart With Ordered Binary Data?

May 28, 2013

I want to create the chart below. I have data that is coded with a binary flag (0 = red, 1 = blue) that is also ordered. For example, the data I would use to hypothetically create the left hand column below looks like this

Binary Flag






View 1 Replies View Related

Create Three Stack Bars Chart With Different Data

Oct 17, 2008

Here is my scenario, I have the following data:

Data Series 1

Tot 10

Data Series 2

Total 10

Data series 3
k=4 (d+e)
l=3 (f+g)
m=2 (h+i)
n=1 (j)

I want the three stack bars together, for the series 2 & 3 I want them without any gap so I can see how data series 3 is created.

I can't find a way to create a three stack bars chart that don't have the same number of data series, I don't want percentages.

View 9 Replies View Related

Excel 2010 :: Can't Save Custom Table Style Format So That It Is Available Next Time When Open Workbook

Jun 10, 2013

I'm a fan of the Excel 2010 table styles, but can't figure one thing out. When I create a table in excel (Ctrl+T), I like to reformat it with a new defined style. When I save the style, I can use it while I'm in that instance of excel, but when I close and reopen excel, the style is gone. How to save the custom formats to they are always available?

View 3 Replies View Related

Loop Through Data And Create Chart With A Number Of Series

Apr 8, 2008

I am looking to create a number of series on a chart (xyscattersmooth) from a datatable. Below is my data Table....

View 9 Replies View Related

Dropdown For Dynamic Sheets With Static Data To Create Chart

May 20, 2013

I need drop-down combo box to change a graph based on the option selected in the drop-down

Challenge: my Combo box resides on the First sheet only, but the items that populate in the drop-down list are dynamic and change depending on how many sheets exist for this document. The options in the list are Overall, Week 1, Week 2, ... Week N. Overall needs to sum up the data (or I can pull just from the summary page if I am lazy) and the Week N corresponds to a specific sheet added.

I currently am able to get my drop down to populate items correctly, and my macros to create the sheets dynamically with templates just fine (not bad for newbie).

So what I need to figure out is how to tie my drop down to make a scatter plot/bar graph based on the selected option and action. I can't find an attachment option here but here is some code:

Sheet 1 Code:

Private Sub Worksheet_Activate()
Me.ComboBox1.AddItem "Overall"


View 2 Replies View Related

Employee Leave Tracker & Check Leave Time Is Valid

Feb 27, 2008

I need to create a leave tracker wherein i will get the names of the person who want to take leave in that particular month. I have prepared a calendar to make an entry. I need to take care of the following things

1) The person must have sufficient leave balance available to take the leave. so i have to first check if he/she has sufficient leave balance.

2) There are four team leaders and each team has around 13-14 members. so if lots of people from the same team apply for the leave then they will not be eligible for leave. this i want to know in percentage as to how much percentage of people are taking leave from that particular team.

View 5 Replies View Related

Macro To Create Pivot Table?

Jun 3, 2014

Can record the all those actions which we need to do on a regular basis. In my work i have to create the pivot table to a data which gets changed every day but the data sequence remains the same.

I have tried to record the macro for the pivot table but when i tried to run the macro again then the below error message comes.

"run time error 5" "Invalid procedure call or argument" when i click on debug then the below macro program is highlighted in yellow color.

View 1 Replies View Related

Copyrights 2005-15, All rights reserved