Posts by EdFromAZ

    Re: Pivot table: filter dates and group by year


    Sorry - this isn't helping as much as I thought. Possibly because I'm using XL2007?
    According to this KB >> APPLIES TO:
    Microsoft Excel 97 Standard Edition
    Microsoft Excel 95 Standard Edition
    Microsoft Excel 5.0 Standard Edition
    Microsoft Excel 98 for Macintosh


    I created a new pivot table from scratch on a different worksheet. But yes, it does refer to the same data table on a sheet in this workbook.


    And when I grouped these dates by month, lo and behold it also changed all four pivot tables on another sheet, which had been grouped by year!


    Fortunately, I did find an Ozgrid reference to this:
    http://www.contextures.com/xlPivot07.html#Another
    This workaround did work and fixed my issue.


    *whew!!*
    Ed

    Re: Pivot table: filter dates and group by year


    Quote


    If you have already created the new PivotTable, you must delete the existing PivotTable, and then create another new PivotTable, using the steps above.


    No way to clear or disconnect the memory cache?! That bites!!
    Yes, I created one pivot, and then copied it and just changed the one field.
    Well, I guess it's a good way to learn things.
    Or sumpin' like that!!


    Thanks for the info.
    Ed

    In Excel 2007, I am trying to set up four separate pivot tables. All are based on data range "AllData!Table1". This is a table of projects and the reports submitted on those projects. The reports needed to meet four separate date milestones, and I'm trying to create a separate pivot table against each milestone to show how many were late.


    All pivot tables have the same basic format:
    - Row labels are: ProjNum and FDate
    - Report Filter is: Status ("done")
    - Values is: Count of FDate
    - Column Label is different: Late MS1, MS2, MS3, or MS4


    Each pivot table displays with the ProjNum as a group heading, with totals across that row, and dates below with totals on each row.


    I would like to filter the dates for each pivot table on one sheet as "before 1/1/2013" and then group by year.


    Obviously, I'm losing! :(


    When I set the Date filter, all my dates change to display "<1/1/2013".
    Plus, making a change in one pivot table affects all four!
    EDIT: Not only that, but changes affect for other pivot tables on the next sheet!!


    Can someone please explain what I'm doing wrong and how to do it right?


    Thanks!
    Ed

    Re: Can code in an Add-In evaluate every workbook opened?


    Thanks, cytop. I tried something like this last night, but as I mentioned it would only fire for workbooks openeing as the add-in was initially loading. Once the add-in was opened, the code didn't fire for any other workbook that opened up.


    I'll give it another shot in a little bit and we'll see what happens.
    Ed

    Many of my users are sporadic about making back-up copies of their workbook files. These are workbooks based on a template that I built, and all use an add-in that I created and keep up-to-date. I'm trying to think of ways to "help them" make back-ups.


    It's too late to put code in This Workbook for the exisiting files (but the template has been updated for new files). But I wondered if I jcould put code in the add-in that would do this? The code would have to evaluate every Excel file the user opened; if a certain named range was in the workbook, then I could simply use SaveCopyAs to make a back-up and write the date or some other "code word" into the file so it only gets done once on any given day.


    If this were on my own computer, I know I could put code using the Application object in my Personal macro workbook. And I did see Chip Pearson's page on doing things like that. But I couldn't see if it could be adapted for use in an add-in.


    Has anyone else done this?
    Ed

    Re: Named range in add-in


    Hi, gijsmo - thanks for responding.

    I'm not quite sure we're understanding the same thing.
    I have a formula like:
    =IF(D6>0,WORKDAY(D6,HLOOKUP(F6,WhenDue,2,FALSE),Holidays),"")
    "Holidays" is a named range residing on another worksheet in the active workbook.

    I'd like to move this name to an add-in.
    -- Is that possible/recommended?
    -- If it's not a problem-maker, do I have to change how it's referenced in the formula?

    Ed

    I have several named ranges in a workbook. Some are used in formulas in cells, and some are used in formulas for conditional formatting. Can I move some or all of these named ranges to an add-in? If so, is there a different way to reference them?


    Ed

    (XL2007, Vista) I am creating a new worklbook from a template. One of the sheets in the new workbook has a selection of three cells as "buttons". The Worksheet_SelectionChange event is supposed to call a macro in an add-in and pass the Target.Address as a string variable.

    I did just have the three cells call three separate macros from the add-in, without passing any variables, and those worked fine. But then it dawned on me that I couldn't add any new functionality in files created on that template because I couldn't get to the VBE to add a call to a new macro. So if I just call a macro in the add-in that figured out what to do based on the Target.Address, then I could update the add-in as needed with new functions.

    Now, though, calling the add-in macro gets me a 1004 error:
    "Cannont run the macro 'MyAddIn.xlam!doTarget(strAddress)'. The macro may not be available in this workbook, or all macros may be disabled."

    This error occurs no matter which cell I select on the sheet. The code stops on the "Application.Run" line.

    Any help on fixing this?
    Ed

    In worksheet:

    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim strAddress As String
    strAddress = Target.Address
    Application.Run "MyAddIn.xlam!doTarget(strAddress)"
    End Sub



    In add-in:

    Code
    Sub doTarget(strAddress As String)
    Select Case strAddress
      Case "$H$23:$J$24"
        Call doRows
      Case "$H$27:$J$28"
        Call doNames
      Case "$H$31:$J$32"
        Call doFreeze
    End Select
    End Sub

    I've been asking tons of questions lately about problems I'm having updating a workbook. I put out a patch program yesterday, and it caused more problems than it fixed! Here's the scenario:


    There's about 35 of us keeping track of our reports in workbooks (XL2007, Vista). There's maybe 250 of these trackers in various server locations. Many are used as shared files.


    The boss decided we needed a new tracker and asked me to build it. I created a workbook that met the requirements: named ranges with lists for validation drop-downs, columns A-Q locked, a macro to insert and delete rows in the locked area, 12 different conditional formats, and some other bells and whistles. Saved it as XLSM and put it on the server so it can be downloaded as needed and filled with information, then szved as a new file.


    Now it's a few months down the road, and it's time to fix the glitches we've found. I pounded out a program using Word and Excel VBA, wrote up (what I thought were) detailed instructons, and sent it out. I think it took 10 minutes for my phone to start ringing!!


    I'm limited in how and what I can do for a patch program:
    -- I'm not a trained programmer, just a VBA fanatic
    -- can't access the VBE to change code
    -- gotta do everything in VBA or VBscript


    This patch ran Excel through Word, and included a "new and improved" XLSM file with updated code modules. I copied all the data sheets into the new file, which put their data on top of new code. Then I fixed formulas and updated lists and such.


    And of course there were problems! Yes, some due to bad code on my part. But many more due to the various ways 35 people managed to mangle a file so my code wouldn't run!


    Of course I now think that had I created an actual template file, some of this would have been avoided. An add-in would have helped too. With 250 data-filled workbooks out there, is it too late to migrate to this?


    Ed

    Using Vista and Excel 2007.
    One of my co-workers had a computer crash a few days ago.
    Since then, he can open a new Excel file, but not an exisiting one.
    Not one saved on his computer, not one on a network server - nothing.

    As the most handy Excel monkey, I am trying to help him until IT gets around to calling back.
    I usually do a shut down and reboot, and clear tmp files.

    What else can y'all suggest?

    Ed

    Re: Execute code based on specific text in target range of cells


    I'll take a closer look tomorrow.
    Right off the top, look at

    Quote


    Sheet1.Cells(Rows.Count, "c")



    What Rows are you counting? From which worksheet? You and I understand Sheet1 - but Excel may not.
    Sheet1.Cells(Sheet1.Rows.Count, "c") will keep you out of that trouble, at least.
    Just remember - any property needs to be linked back to its object, or your results may not be what you expect.

    Ed

    Re: Execute code based on specific text in target range of cells


    First, please tell us what error you're getting and on what line of code. Otherwise, it's like going to the doctor and saying "It hurts!", but not saying where or how it hurts.

    Second, let's look at how you're setting your objects.

    Quote


    Set wksSource = Sheet1
    Set wksDestination = Sheet4



    If you're setting a worksheet object by its name, then you need to put the name in quotes:
    Set wksSource = "Sheet1"

    Quote


    If Range("B4,e4") = "published" Then
    Range("b4:e11").Delete Shift:=xlUp



    Range is a property of a worksheet (in this case). You can't use Range without qualifying which worksheet it belongs to.

    With wksSource
    .Range("B4") 'notice the . before Range
    End With

    wksSource.Range("B4")

    Either one is valid.

    Also,

    Quote


    If Range("B4,e4") = "published" Then



    Should that be ("B4:E4"), with a colon instead of a comma?

    Get in the habit of going up to Debug and using Compile VBA Project. It can highlight several of these issues before you get errors.

    And don't give up! Can't always promise a _speedy_ answer, but you will get some answers. And I might get stuck myself 'cuz I'm not the top of the class by any means. But we'll get you there.

    Ed

    Re: Execute code based on specific text in target range of cells


    Quote


    On Sheet 1 I have 3 main buttons. Right now the only issue I have is the "Published Workflow" button. I need the information that is cut from column C to be pasted into Sheet 4 under its respective month (according to the intended publication date"). Also I'd like --on sheet 3--for the information copied from column 3 to be pasted in sheet 1 under their respective month.


    Is there a macro that can tell excel to paste the information being copied under the month indicated in the "intended publication date" queue?



    Try using a named range for each month. Click in the cell for each month, type the name of the month in the address area just to the left of the formula bar, and hit enter. Now in VBA, you can access that range: Sheet1.Range("May") - .Row will give you the row that range sits in, and you can increment down from there to find the first empty spot, and .Column should give you the left-most column of your merged cells.

    Does that make sense?
    Ed

    Re: Execute code based on specific text in target range of cells


    I'm not going to have a lot of time to work with this until after the weekend. In the meantime, here's a few things for you to do:

    -- In Module 1, every single one of your Button_Click modules has the same two variables dim'd. I'd suggest putting them at the top of the module in the Declarations area. (In the VBE, you'll see the name of your macro in a drop-down list at the top right of the work area. If you click the arrow to expand the list, you'll see "(Declarations)".) If you put your common variables there, you can avaoid re-declaring them in every macro.

    -- Also, you have "Range("C5:C116").Value". There is no worksheet object attached to this range. That can be dangerous and lead to unpredictable results. You really need to set your range as a property of a specific worksheet. I would Dim a worksheet variable up in the Declarations area, and set it to the worksheet being worked on inside the button macro. Then adjust your Range codes so they become attached to that worksheet - something like "wksWorkflow.Range("C5:C116").Value".

    -- I would reconstruct your button macros to be more compact and less confusing. The MyNote and Answer with the double IFs is a bit confusing. It's just as easy to do something like:

    If MsgBox("Are you sure you want to clear May workflow?", _
    vbQuestion + vbYesNo, "Clear Workflow Detail Alert") = vbYes Then
    'Work with these ranges
    Else
    'Work with these ranges
    End If


    You've also just eliminated the MyNote and Answer variables.

    And what is "Value = True" supposed to do? If it's meant to NOT clear the values, then simply eliminate the Else (or Answer = vbNo) portin of the code.

    -- Give your buttons some descriptive names - something like btnClearMay, and put a comment or two in each button macro to tell you at a glance which one you're looking at and what it's going to do. (I couldn't access the properties for these buttons - how did you set them?)

    -- Did you understand the code I gave you? Do you understand what it's trying to do? Can you see where it needs to be adjusted?

    -- You said:

    Quote


    If--for instance-- on sheet 1 range b4:e4 has the word "published" written in it then i want the info in range c5:c10 to be copied into the next available queue on sheet 4


    You do not have sheets named Sheet1, Sheet4, sheet 1, sheet 4, or anything resembling that. Please use your actual sheet names. And tell us which button needs to be clicked. It's all obvious to you, because you're in the middle of building this. But for anyone else, there's a lot that is not obvious. Using these specifics can really streamilne getting help.

    Cheers!
    Ed

    [Resolved] XL2007:Setting ExclusiveAccess stops coded dead


    I resolved the issue by avoiding it.

    I re-coded everything to run through Word VBA. That allowed me to take a lot code out of the "master template" Excel file, avoiding potential log jams in the Excel VBE.

    I also am requiring the user to open the Excel files one at a time and manually turn off sharing. That avoids the whole ExclusiveAccess issue altogether.

    It all ran great - did five files in less than five minutes. I can live with that!

    Ed

    Re: Execute code based on specific text in target range of cells


    I remember being brand new to VBA! Sometimes I think I'm not much further along!! But let's see what we can do here.

    First, every macro starts with "Sub YourMacroName" and ends with "End Sub". You copied over the "End Sub", but not the "Sub YourMacroName". So now we're left to wonder what's happened before the bit of code you posted, and that could make a difference in what you need.

    Second, when you say "if the word "published" is in that specific region", what "region" are you talking about? In the cells you've selected to copy? In the title of the worksheet you want to paste into? In a specfic cell in one of the worksheets?

    In your macros, you'd be a lot better off working with worksheets and ranges as objects, rather than as selections. The Selection object is very useful, but most of the time it's not needed.

    So let's try something. I'm making the following assumptions:
    -- the code you posted is the whole macro, just without the beginning line
    -- you are cutting from the "Upcoming Workflow" sheet
    -- "Publised" was a typo in your post, and your worksheet name is really spelled correctly as "Published"

    When you're a bit more specific with what "region" you need to see "Published" in, we can deal with that.

    Ed