Posts by JohnCleary

    Re: With Outmail - Conditional Email String for .HTMLBody tag

    Is possibly the SET statement - this is only used to assign Objects to a variable. A String is not an Object, but a simple string.

    Untested, unchecked - I now have a headache from reading that and scrolling left and right like a lunatic (Not your fault - it's the way the board parses code... On second thoughts it IS your fault. Use Line continuations. It'll help you too) :)

    Re: Copy Text based on Criteria

    Assumptions as there has been no reply:
    The values go to the last used row on Dashboard
    The code runs in the code module of the sheet 'InsufficientSoH'

    Re: Copy Text based on Criteria

    Your initial post should have started "I have a spreadsheet ...". Everything before that is pointless.

    You want to copy the values from Cols A, D & F to the sheet Dashboard where F contains a numeric value?

    Where on the destination sheet do these value go?

    Re: Add a Second Event Handler

    In general, native worksheet functions are preferable to VBA, if for no other reason than they are a lot faster. I don't believe you need VBA for this but if that's your preference, then that's your preference.

    Re: Add a Second Event Handler


    the fact that B15 contains a formula made it impossible for me to get conditional formatting to work

    Disagree. There is absolutely no problem having Conditional Formatting in a cell that already contains a 'standard' formula.

    Re: Add a Second Event Handler

    After reading your post again, I don't understand the relationship between J2 & B15. You say "whenever the value in Range("J2") on that worksheet is changed" you check the value in B15. What I don't understand is If B15 does not refer to J2 by means of a formula why are you checking B15?

    I don't think you need code for this, Conditional Formatting in B15 using =B15<>"" as the fomula and the formatting set to highlight the cell with the appropriate colour (I've no idea which colour ColorIndex 35 is).

    Re: Macro that populates a cell on based on the value of another cell


    Using default references to the range so must go in the worksheet code module

    Re: Find PivotTable Name and set as PivotTable variable in VBA

    You don't understand the concept of the 'Index' property. Every object, whether a Worksheet, Workbook, Chart or Pivot Table has an index property- and for any particular type of object, the index will start at 1 and continue the sequence for other objects of the same type. Index 1 will be the first object of that type created in that context.

    It doesn't matter what the name of the pivot table is - the first pivot will have an Index of 1, so

    Set pvt = ActiveSheet.PivotTables(1)

    will always set a reference to the first PT, even if it's Name is 'PivotTableWastingSpaceInTheWorksheet 99"

    Of course, if there's more than one Pivot Table on a sheet and you don't know the name of the one you want or any other identifying feature like the number of columns, a unique Row/column label, Heading etc, then the technical term for that is "You're Stuffed".

    Another property, Count, can be queried to find out how many objects of a particular type exist...

    If ActiveSheet.PivotTables.Count <> 1 Then   
        MsgBox "Dunno wat 2 du", vbShreik
    End if

    Re: Find PivotTable Name and set as PivotTable variable in VBA

    Silly examples

    Sets a reference to a pivottable using its index, then by using a name - but as you don't know that I guess you'll have to rely on there being only 1 pivot per sheet.

    Re: Extract Variable Number of Rows on Multiple Worksheets to one Master Tab


    replace it with the below.

    I sorry, but that will error, SourceWS was declarared as a Worksheet, vSheets as a variant array. You cannot assign anything to a worksheet object except another worksheet object

    The code was tested and it works.

    If there's an issue then it's something to do with the local environment. For example, the code contains the comment


    ' add all sheets to copy

    Was that done? Do the names added exactly match the actual names of the worksheets?

    Another example. The code works with the active workbook. If you run the code from the VBA editor when a different workbook is active in the Excel window, then it will error.

    Re: Print sheets from all .XLSX documents in a folder and its sub folders.

    The code is fine - most likely there's some error with one of the worksheet, perhaps it doesn't exist? Change the error handler to show a message box if any errors happen

        If Err.Number <> 0 Then
          MsgBox Err.Description
          debug.assert false
        End If
        Application.ScreenUpdating = True

    After the message box, the code window will be displayed and you use F8 to step the code - it will return to the line that caused the error so you can check.

    Re: Extract Variable Number of Rows on Multiple Worksheets to one Master Tab

    Maybe this might help?

    The data being copied should be just values - formulas will probably mess it up a bit.