Macro Code To Add More Rows To Existing Chart

Jun 17, 2008

I have managed to create something similar to what i am working for using an example from Lacher and Gant Charts. i am now stuck as I can enter more than 40 status as it then gives me an error. The following is the code: Can any1 highlight where i need to make any changes to stop the error from occuring:

Option Explicit
Sub CreateTimeChartData()
Dim vTimeData As Variant
Dim i As Integer
Dim sRoom As String
Dim vLastEndTime As Variant
Dim oSeries As Series
' set up
Application. ScreenUpdating = False
Application.DisplayAlerts = False
' create chart data worksheet
With Worksheets("TimeData"). Range("TimeList"). CurrentRegion
.Sort Key1:="Room", Key2:="Start Time", Header:=xlYes
vTimeData = .Value
On Error Resume Next

View 3 Replies


Enhance Existing Code To Hide Rows Between Specific Text

Mar 24, 2014

I have a workbook which contains 8 worksheets. I want to able to run a macro that looks for specific words in column A of each worksheet and hides any rows in between the specified words. The following code works except if a worksheet does not contain the specified words. Is there a better way to accomplish this?

Attached is an example of the spreadsheet. The code below works just fine on the tabs highlighted in green, but halts on the tab highlighted in red. The tab highlighted in yellow is showing you the rows I need to hide.


View 5 Replies View Related

Amending Existing Code To Add New Column To Macro Output

Jun 4, 2014

Attached is my sample data Sample Data_Combine Sheets.xlsm.

What the code in the attached sheet does is that it combines the data in Sheets Set1 and Set2 into the worksheet named "Combine".

Both input sheets share same columns. Code I use is as below:

Sub BuildPivotData()
Dim wrsht As Variant
Dim i As Integer

wrsht = [{"Set1", "Set2"}]
For i = 1 To UBound(wrsht)
Sheets(wrsht(i)).[a2].CurrentRegion.Offset(1).Copy Sheets("Combine").Range("A65536").End(xlUp)(2)
Next i
End Sub

I want to amend the output in such a way that when the macro is run , in the "Combine" worksheet an additional column is added at the start(col A) with header as "Category".

The category should be nothing but the input Sheet name i.e Set1 or Set2.

Here is an example ... Output Sheet.xlsx.

View 3 Replies View Related

Adding Comparison Line Chart To And Existing Population Pyramid Chart

Jul 14, 2012

I have created a population pyramid and want to add another chart on the secondary axis which I can do. The part which I seem not to be able to complete is getting the line chart secondary axis to read from the primary y axis. It seems to add a new axis, even when I delete this the line chart does not read correctly.

View 1 Replies View Related

Assign 2 Macros To 1 Button (or Adding VBA Code To The Existing Macro)

Dec 23, 2009

I'm verrry new to editing VBA code and I've been trying to figure out how to send a column of data ("Min Data" AB3:AB21 from "Sheet1") to a worksheet ("F4" - Min) and sending another column of data ("Max Data" AC3:AC21 from "Sheet1") to a different worksheet ("F4" - Max) by using only the one button ("Record Data" from "Sheet1"

I've been trying to figure out ways to edit the VBA in "Module 1" to make this work

Can anyone help me out here? If what i'm asking for isn't clear enough, just let me know and i'll try to explain in more detail.

View 6 Replies View Related

Macro To Apply Existing Formula To All Rows In A Sheet

Jan 26, 2009

I'm currently applying these formulae manually, and also instructing other people to do the same, due to my lack of understanding of macros. Hopefully someone can give me some instruction as to how to do this automatically. I'd like people to be able to open sheets sent as normal CSV's and apply a macro to get them into the right format, changing only rows with data in them.

These are the instructions I'm currently sending:

In cell n1 enter:
full phone number

in cell m2 enter:
=RIGHT("00000000" & J2,8)

Drag m2 to last record

In cell n2 enter:

Drag n2 to last record

In cell o1 enter:
Date of call

in cell o2 enter:

View 6 Replies View Related

Changing Existing Macro To Transpose Rows To Columns

Feb 22, 2008

I have the following macro which I use to transpose a number of columns in one sheet to rows in another sheet.

Sub task1()Dim i As Integer, n As LongFor i = 5 To Columns.Count Step 2    n = n + 1    With Sheets("Working_Checklist_1").Cells(10, i).Resize(16)        Sheets("Summary_Intermediate").Cells(n + 2, "c").Resize(.Columns.Count, .Rows.Count) _        .FormulaArray = "=if(transpose(Working_Checklist_1!" & .Address & ")=0,"""",transpose(Working_Checklist_1!" & .Address & "))"    End WithNextEnd Sub 

I'd like to change this macro so to transpose from rows to columns. I've tried a couple of things, but can't quite get it to work.

I'd like to transpose every second cell starting from E7 to IV7 in Working_Checklist into column BU in Summary_Intermediate, starting from BU3.

View 9 Replies View Related

Macro Inserts 3 Rows Below Each Existing Row Of Data And Copies And Pastes That Data Into Each Of The Empty Rows

Nov 30, 2009

need to create a macro that inserts 3 rows below each existing row of data and simply copies and pastes that data into each of the empty rows before moving on to the next unique row and doing the same thing again.

This is what I have so far, but I can't seem to get the loop right.

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

activecell.Offset(-1, 0).Select
Range(activecell, activecell.Offset(0, 5)).Copy
activecell.Offset(1, 0).PasteSpecial
activecell.Offset(1, 0).PasteSpecial
activecell.Offset(1, 0).PasteSpecial
Selection.Offset(1, 0).EntireRow.Select

View 5 Replies View Related

Excel 2007 :: Macro - Match Data And Copy Rows To Existing Sheet

Jan 11, 2012

I've already found a TON of threads about this process but nothing that matches specifically what I'm trying to do.

I have a spreadsheet that I'm using to auto fill other tabs with data that only matches specific criteria. Here's what I'm looking to do:

Columns I, J, K, and L may be marked as either Y or N (or blank). I have different sheets that require 1, 2, 3, or 4 of those columns to match Y. For example, on sheet 2 I want to copy the entire row if there's a "Y" match on column I and J. On sheet 3 I want to match "Y" against, I, J, and K. Sheet 4 I need to match only L, etc.

I need the data copied into the existing sheets to start on row 7. I have other data on rows 1-6 that cannot be moved.

I'm running Excel 2007.

View 5 Replies View Related

Add Data To Existing Chart With VBA

Aug 5, 2013

I am trying to add data to an existing chart with VBA. I have defined and populated my arrays, but I m not sure how to add them to graph. I am getting a Invalid proedure or call argument on the code below.

Sub Plotting()
Xvalues(1) = 50
Xvalues(2) = 48
Xvalues(3) = 46
Xvalues(4) = 44
Xvalues(5) = 42
Xvalues(6) = 40
Xvalues(7) = 38
Xvalues(8) = 36


View 1 Replies View Related

Chart Title Manipulation With Macro Code

Sep 2, 2006

I have a program that updates values quarterly when run. The spreadsheet that it works with also contains a couple of pie charts that correspond to the updated data. the charts only have one series with category values. I recorded a macro to see the code excel uses for creating a new chart and tried to modify the statements to my needs but have been running into runtime error 1004 ("Method ' Cells' of Object '_Global' failed"). The code is as follows I don't have any trouble until the last two statements.

Sub Chart_Updater()
With ActiveChart
.HasTitle = True
If Chart1var = "COLI VUL 1 Fund Chart" Then
.ChartTitle.Text = "COLI VUL 1 Allocation by Fund Provider " _
& sday & "-" & sday & "-" & Lyear
ElseIf Chart1var = "COLI VUL 2 Fund Chart" Then
.ChartTitle.Text = "COLI VUL 2 Allocation by Fund Provider " _
& sday & "-" & sday & "-" & Lyear
ElseIf Chart1var = "COLI VUL 7 Fund Chart" Then
.ChartTitle.Text = "COLI VUL 7 Allocation by Fund Provider " _

The variables fundtr1, fundtr2, and emptycolvar are public, and are all returning values, so there is no problem there. Also I can't use ranges like "A1:F7" because the range needs to be variable.

View 5 Replies View Related

Date Based Bar Chart Macro Code

Mar 21, 2008

I am working on a manning document which tells me when people report, and depart based on their job position. I have all of the info in one worksheet, and want to generate a bar like graph on another worksheet in the workbook (by just changing the cell color for ease) for a snapshot to view holes in job positions.

The snapshot worksheet has the list of jobs on the left, and months/years in a line acorss the top i.e.

| J | F | M | A | M | J | J | A | S | O | N | D |
Job Position X X X X X X
Job Position X X X X X X

Here was the code I started with, and it doesn't do anything!

Private Sub Workbook_CreateCalendar(ByVal Sh As Object, ByVal Target As Range)

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim Fcol As Date
Dim Lcol As Date
Dim Lencol As Long
Dim CalcMode As Long
Dim ViewMode As Long
'Set the Start Date of your Sheet
Dim DatTim1 As Date
DatTim1 = #1/1/2006#

View 5 Replies View Related

Delete Chart Series Via Macro Code

May 27, 2008

I have a spreadsheet (see attached) in which there are many series and i want to be able to delete most of the series except the first 4.

(The 0%, 5% 10% air voids and the 95% comp line)

iv got this code, which is attacted to a command button, which works fine to delete all the series but makes the graph a blank white box. id prefer it so it delete just the series not the graph "picture?" ...

View 7 Replies View Related

Add Secondary Chart X-axis Below Primary Via Macro Code

Apr 27, 2008

In VBA, how to add a secondary x-axis in the lower portion of the chart if I already have the primary axis in the upper part?

This code

ActiveChart.HasAxis(xlCategory, xlSecondary) = True

just adds it to the upper if I have the primary at lower.

View 4 Replies View Related

Macro Code To Count Number Of Chart Series

Jul 1, 2008

I have about 50 lincharts that I wont to update with VBA but they have different number of series. Some have two series, some three and others four. Is there a way to count how many series a chart has? Otherwise could I get tips on how to approach this problem.

View 2 Replies View Related

Change Order For Chart Series Collection Macro Code

Mar 18, 2008

I have a bar graph with 2 series of data. Anyone knows how I can specify the order in which they appear? code looks similar to the following. The bar graph shows the second series before the first series. How do I reverse the order?

With cht
.ChartType = xlColumnClustered

' first data series
Set ser = .SeriesCollection.NewSeries
With ser
.Values = someValues
.XValues = someValues
End With

' second data series
Set ser = .SeriesCollection.NewSeries
With ser
.Values = someValues
.XValues = someValues
End With

View 2 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

Macro: Plot Graph/Chart By Series Of Rows

Jun 30, 2008

I've been trying to work out a code for a while now to plot some series in rows on a graph. The wonderful Mr Pope helped me with one before but for series in columns but i just don't seem to understand it enough to use it for this problem. the graph already has 2 series plotted (called the envelope) so i have to use seperate series to plot the data.

Basically the name of series is down column A
but the range is from E1 to Y1
and the data to be plotted on the graph which already has two series plotted in it is E2 to Y2, E3 to Y3 etc. iv been trying to use this code but failing miserably

Sub adddatatopsd(Name As Range, XData As Range, YData As Range)
With ActiveSheet.ChartObjects(1).Chart
With .SeriesCollection.NewSeries
.ChartType = xlColumnClustered
.XValues = XData
.Values = YData
.Name = Name
.ChartType = xlXYScatterSmooth
End With
End With
End Sub...........................

View 4 Replies View Related

How To Create Duplicate Rows Of All The Existing Rows

Jun 26, 2013

Everyone always want to get rid of duplicate rows.

Is it possible to create duplicate rows of all the existing rows?

View 5 Replies View Related

Trying To Combine Existing Code

Mar 27, 2009

I am trying to combine the 'Mail Range' from this code with the 'Mail to address in A1' from this code and can't make it work. Maybe this is the wrong tactic to take but I have very little knowledge about writing my own code. The ideal solution would make a copy of a a specific range of cells from a specific sheet, mail it to a recipiant (could change based on what is entered in the field) and delete the copy. I will try to attach an example next week.

View 4 Replies View Related

Putting Input Box Into Existing Code

Jan 22, 2009

On the "If not Isempty" line, I am trying to input a box prompt. Instead of standardizing this macro, I want the user to be able to select a dollar amount to search and place on sheet3. Right now it works fine using 1.29 as the amount. However, I have other stores where this dollar amount is different, so I want the store managers to be able to choose an amount to search, for example 1.39, 2.99 etc...

View 2 Replies View Related

Adding Second Range To Existing VBA Code?

Nov 13, 2011

adding an second range (J1089:AI1147) to the follwing code.

Sub Clear_Range()
Application.EnableEvents = False
With Range("H3:AL1058")


View 2 Replies View Related

Add Date Last Modified To Existing VBA Code

Feb 10, 2014

I copied this snippet from the net and it has been working like a charm. I would like to add DateLastModified to it, but I can't get it to work, I'm still kind of new to more complex VBA codes. Here is the code...

Private Sub CommandButton1_Click()

Dim cell As Range
Dim selcell As Range
Dim Value As String
Dim Folder As Variant
Dim a As Long

ReDim Folders(0)

Set cell = Range("B8")

[Code] .........

View 7 Replies View Related

Code To Use Existing IE Tab To Pass Different Queries On Google

Jun 20, 2013

I need a code to use existing IE tab to pass different queries on google search.

For example:- If active cell contains "manoj Kumar" then macro will search "manoj Kumar" on google and if i select another cell then code will pass the another google search query on the same active IE window(It will not open new window or new tab).

View 4 Replies View Related

Debuggin The Existing Code To Copy/paste

Jul 11, 2007

I use for another template and it works fine. Only change I would like to make is the column headers starts in Row 4. Col B. Some how this is not working for me.


Application.ScreenUpdating = False

With Sheets("Data").Rows("1:1")
Set c = .Find("CONTRACT #", LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Range("B3").Insert shift:=xlRight
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address FirstAddress
End If
End With
End Sub

Again...incase what I said does not make sense: I want to copy certain columns with fixed header names, e.g. "CONTRACT #" into another sheet name "Test1" and paste starting into cell B3. Another column would be "Contract Name" and so on. Also, these col header names may not be in order. In "Data" sheet, col header names starts in row 4.

View 9 Replies View Related

Automatic Update In Other Sheets: Need Some Changes To The Existing Code

Aug 29, 2008

I have already got an anwer for this long back from this site. The code was writted by Mr. Krishnakumar
the thread is here :[url]

i need some changes to be made in this code. The existing code creates and updates the details in the sheets automatically from the master data. I just need the sum of Column I in all the sheets after the last row of Column I.

selecting all the sheets and typing the formula in I column is not possible because, the last row in Column I is different in all the sheets.
In sheet 1, the last row of Column I is Row 15, in sheet 2 Row150 is the last row.
I guess something could be done in macros.

follwing is the existing
Sub TestIt()
Dim sWS As Worksheet
Dim Sellers As Range, Seller As Range
Dim lRow As Long, fRow As Integer
Dim CopyRng As Range, ws As Worksheet

View 9 Replies View Related

Code To Copy, Paste Without Replacing The Existing Data

May 21, 2009

I have two sheets - sheet1 and sheet2. Everytime i run a macro in the sheet1and manually copy the rows from sheet1 {starting (A2:AI2)} and paste as values it in the sheet2 {starting (A2:AI2)}.

Then I ll delete the available datas in the sheet1 and will run a macro for different customer.

Again I need to repeat the same action (manually copy and paste the datas to sheet2) without replacing the existing data in the sheet1.

In simple I require a macro code where it copy the available information in sheet1 and paste it as values in the sheet2 without replacing the existing one.

View 14 Replies View Related

Excel 2003 :: How To Amend Existing Code In Workbook

Feb 3, 2013

How to amend existing code in a workbook that is used by many different users. I am working in Excel 2003. The existing code copies data from certain cells in the active worksheet (which is password protected) into a new worksheet and saves that new worksheet to a temp folder, sends it to a particular email address using outlook and closes it, returning the user to the first workbook. You will see that it also unhides then rehides columns of the first worksheet during this process so that the user never has to see them. Problem here is that none of the users are using outlook anymore and I would prefer that this new spreadsheet that is created just be saved to the users desktop. I have found bits and pieces of what I needed on the web, but I can't seem to put it all together (I usually end up being sent to the debugger just short of having the newly created spreadsheet saved to desktop). Here is the existing code:

Private Sub CommandButton1_Click()

Essentially what I need to do is circumvent the whole email bit and save the spreadsheet that is created to the users desktop instead of a temp folder.

View 2 Replies View Related

Making Existing Code Work For Specific Worksheet/s

May 30, 2013

I would like the following code to be run for all those sheet/s that has a name = "single" (Not case sensitive neither an exact match) of my active workbook.

Dim LR As Long, i As Long
LR = Range("I" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With Range("I" & i)
If .Offset(, -1).Value = 1 Then .Value = .Value & "-"
End With
Next i
End Sub

View 3 Replies View Related

Add Stipulation That Specific Cell Must Not Be Blank To Existing Code

Oct 21, 2013

I have a code now that in excel, when the button is clicked, will put file into a new email and format the email.

What I have been trying to do, is make it so that if Cell C10 is blank, you get a prompt telling you it must be filled out before you can proceed with the submission.

Here is the code now:

Sub SendTimeCard()
On Error GoTo err_handler
Dim OutApp As Object
Dim OutMail As Object

Dim subject As String
Dim body As String
' Be sure the workbook is saved first

[Code] ........

View 2 Replies View Related

Copyrights 2005-15, All rights reserved