Posts by Robert B


    is there a way of adding a trendline to a series plotted on a secondary axis?

    The standard method displays the trend on the primary axis and is invisible due to the disparity of the 2 scales.


    Re: Selection of a linked Chart

    Thank you both for your suggestions. I wanted to avoid both asking the user to may any decision beyond selecting his area of interest, and allowing him access to the base data.

    I am attempting to solve this by using a combo box to select the category and then running a macro which selects the relevant chart by using Select Case on the Combo Box input which then open the file as an embedded object.

    So far it seems to fit the bill




    I am creating a report which summarises activity over 15 or so different areas, 1 page per category. In each page I have 4 charts which are naturally fairly small so I have created link each chart to another file in which a larger and more detailed chart resides.

    What I would like to do is to create a single page with a list box so that the user can select a particular area thus displaying the data relevant to his selection, so far so simple, however I cannot see a way making the displayed graphs dependant on the selection of the category.

    Can anybody help?



    Re: Unrecognisable Folder

    Hi Barry,

    Yes, the folder is on a network, my temporary solution has been to create a new folder, copy everything in the existing folder except the offending folder, rename the old folder "Rubbish" and name the new folder as the original. Its still there, but out of sight and, so, out of mind.

    Someday I'll get the IT guys to blat it.

    Thanks for your suggestions.



    Hi all

    I have a very annoying folder that was created during an aborted macro run. This folder, I am told by Windows, Cannot be deleted, moved or renamed: cannot read from source file or disk.

    The macro that created this folder has run several times since with no unusual occurences.

    Can somebody suggest a means of purging this file from my system?

    Many thanks



    I remember seeing a piece of code which produced an index of the sheets in a workbook, with hyperlinks to each sheet, can anybody direct me to the source for this code? Similarly, is there some code which can list workbooks in a folder also with hyperlinks?




    I have this formula, which works fine


    what I would like to do is return 0 on error, so I tried


    but this fails. I am obviously making a very basic mistake, but can't spot my error.

    Any help appreciated


    Hi All

    I would like to be able to set an active sheet as an integer so that e.g.

    n = ActiveSheet.Number

    this obviously fails but I am sure there is a way, can somebody help?



    Re: Creating File Reference in VBA

    What I am trying to do is set myFolder as a variable which is allocated the name established in the myFolder = ActiveSheet.Name statement.

    The Locals window seem to indicate that is happening.

    It obviously isn't, so where am i going wrong? Should the Filepath be constructed differently?

    Thanks again

    I am populating a summary spreadsheet from a number of other spreadsheets which are located in a location such as

    I would like to add new folders for monthly data so that the path becomes


    However when I use

    dim myFolder as string
    myFolder = ' This is the current month on the summary
    Range("A1") "=Z:\Folder\Foldera\Folderb\TargetFolder\myFolder\[filename.xls] _!Sheet1'Range("A1")"

    the macro looks for a folder called myFolder, which doesn't exist.

    I am probably doing something really dumb, but can someone help


    Hi all

    I have 2 questions regarding Pivot Tables.
    1)Given the following code

    ActiveSheet.PivotTableWizard _ 
    	SourceType :=xlDatabase, _
    	SourceData :=myRange, _
    	TableDestination :="", _
    	TableName :="PivotTable2"

    How do I define the destination to be on a specific worksheet at, say, cell (“B5”).

    2) I would like to add an additional column called “Variance” which would show the percentage difference between the successive cells in the preceding column

    RowField Data
    Total Variance
    January 100 -
    February 110 +10%
    March 99 -10%

    Hope this makes sense


    Re: Dynamic date as criterium in Auto Filter

    Thanks Andy &Will

    I amended the code as suggested but still failed to extract the data I am expecting; nothing is returned. The odd thing is that when I pause the macro and custom filter the column, myDate in dd/mm/yyyy format defaults in the selection box and the expected records are returned.

    Will, I would love to know the option indicated by your aside.



    I want to select a range of data based upon a variable date which is stored on a worksheet.

    This is an extract of the code I have created.

    Dim myDate as String
    Application.Goto Range("A2") 'this is where the date is stored
    myDate = ActiveCell
    Selection.AutoFilter Field:=1, Criteria1:= ">=myDate", Operator:=xlAnd

    This does not produce the expected result, can somebody see what I am doing wrong? I should say that the Locals window indicates that the date has been stored as required.