Posts by Batman

    First thoughts are that:


    1) Your administrator saves the attachments preferably, but not necessarily, to a standard directory;


    2) She opens the myWorkbook book and runs a macro from a button or drop down menu which:


    a) Displays the Excel 'File Open' form to select a file to open;
    b) Opens the selected file;
    c) Copies any data in Sheet1 of the workbook opened to the next available row in myWorkbook;
    d) Copies down the formulas already in columns H-P to the end of the new data;
    e) Closes the import workbook;
    f) Returns to (a) to prompt for the next template to read, which can be cancelled if there are no others.


    If this is the sort of thing you are looking for, I can give you some code that will get you started.


    Regards,
    Batman.

    Possible answer to the mystery of the disappearing code.


    If you develop your code in the Visual Basic Editor, return to Excel and switch active workbooks, then return to the VBE to continue developing the code and click the Save button in the VBE, your code may be saved to what is the currently active workbook in Excel. This may not be the workbook you were expecting.


    As a rule, I generally avoid using the Save button in the VBE and return to Excel to save my code.


    Hope this helps.


    Regards,
    Batman.

    Hi tvsmvp,


    If you create a macro based on the Worksheet_Change event, the default code if you right-click the sheet tab, select View Code and select from the drop downs Worksheet and Change will be:


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    
    End Sub


    The variable Target will hold the address of the last cell(s) to be updated. You may need to check whether there have been multiple cells updated (e.g. fill down) before running your code:


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
       If Target.Cells.Count = 1 Then
          ' Do whatever
       End If
    End Sub


    Hope this helps.


    Regards,
    Batman.

    Hi,


    Going back to your original question, if you add a 'subtotal' formula to your worksheet this will trigger the Worksheet_Calculate event when a user selects a drop down filter value. Place the following code in the worksheet's code module (right-click sheet tab, select view Code):


    Code
    Private Sub Worksheet_Calculate()
       If ActiveSheet.AutoFilterMode Then
          If ActiveSheet.FilterMode Then
             Application.OnTime Now + TimeValue("00:00:03"), "UndoFilter"
          End If
       End If
    End Sub


    You will then need to place the following code in a standard code module:


    Code
    Sub UndoFilter()
       Application.EnableEvents = False
       ActiveSheet.ShowAllData
       Application.EnableEvents = True
    End Sub


    Hope this helps.


    Regards,
    Batman.

    Hi Jamap.


    I'm still a bit confused about what you want to do with the data once you have identified which column(s) the data should go in. However, if I make a basic assumption that the output column number is determined by the contents of column E in your input file, the code below will determine which column the output data should go to. You can then add your own code to do whatever calculations you want to with the data.


    The code defines an empty array to hold the defect types from column E. When a defect type is read, it checks each value in the array for the defect type. If it finds it, the program uses that position to determine the output column using the formula '=(pos * 5) - 4' (e.g. 1 returns 1, 2 returns 6, etc.). If it is not found, the defect is added to the end of the array, and the array extended by 1 field. In this way you can have as many defect types as you want and the array will keep extending to cope.



    Hope this helps.


    Regards,
    Batman.

    Hi jamap,


    Assuming that the 500 records are in a separate table, what fields in that table are you checking, and what are the criteria that determine which of the three (or more) tables you write the data to? Can you post a small sample of the data?


    Regards,
    Batman.

    Sorry, Squire King, it's probably me being a bit dense on a Friday afternoon, but ...


    Which workbook does the user have, what does he do with it, what triggers the need to transfer data from one workbook to the other, and how much data is transferred (a single record, multiple records, all records in the worksheet, etc.)?


    Is myWorkbook open at the point of wanting to make the transfer, or do you want the process to open it, copy to it, and save and close it?


    Where are the formulas to write to columns H to P? Are they in H-P of the previous record and you want them copied down to the new record(s)?


    Regards,
    Batman.

    Paul,


    I've never worked with the Range collection of a Range before, so I don't know how looping through that collection works. Do you have any other ranges that intersect with column A of sheet Database? If so, try declaring rngData as a Variant and adding .Cells to the end of your For Each ... statement so that you loop through the cells in a specified range, rather than through the ranges.


    Hope this helps.


    Regards,
    Batman.

    Change the relevant statement as below. At the moment it is finding the last row in the active sheet, which is Display, not Database:


    Code
    For Each rngData In Worksheets("DataBase").Range("A1:A" & _
            Worksheets("Database").Range("A65536").End(xlUp).Row)


    Regards,
    Batman.

    Ray,


    Would the following work for you?:


    Set the Tag property for each control you want to measure the length of to its required length and for those not required to be measured to 99. Then use a variation of the following code:



    Hope this helps.


    Regards,
    Batman.

    Jimmy,


    Change the relevant 3 lines of code to the following:


    Code
    Set rngToCopy = Range(Cells(Target.Row, 2), Cells(Target.Row, intColumns))
    
    
    Set rngCopyTo = .Range("B" & .Range("B65536").End(xlUp).Row + 1).Resize(1, intColumns - 1)
    
    
    ' Delete the archived row from the active accounts table
    rngToCopy.EntireRow.Delete


    I have made an assumption that it is OK to delete the entire row in the active accounts sheet, rather than just the individual cells before. If you have other data to the right of the active accounts table, let me know.


    Regards,
    Batman.

    Hi Jimmy,


    I have made a few changes to the code that, instead of using Copy and Paste to transfer the values to your archive table, simply assign the values from one table to the other. Replace your existing code with that below and, as before, change the path, file and worksheet names to reflect your own.



    Hope this helps.


    Regards,
    Batman.

    Hi Jasper,


    Does it not work if you record your macro to filter the first date on the custom criteria <= 25/09/04 and the second date on the custom criteria >= 25/09/04?


    Regards,
    Batman.

    Hi,


    The following should hide all rows and columns except those in the used range:



    Regards,
    Batman.

    It may not be exactly as you want, but if you right-click the Staff field header and select Field ... from the drop down menu, then change the Subtotals setting to automatic, the totals you were looking for will be displayed, albeit underneath the data instead of to the right.

    I was intending for you to modify the pivot table.


    If you right-click a data item in the pivot table to display the pivot table shortcut menu, select Wizard to display the pivot table settings, and then drag Division from Row to column. Alternatively, if you only want the totals displayed, you can drag Division to Page.


    Regards,
    Batman.

    Hi learn,


    If, in your sample workbook, you reselect the Pivot Table Wizard and drag the Division field from Row to Column, the row total should then display the grand total you are looking for.


    Hope this helps.


    Regards,
    Batman.

    Hi,


    The array formula:


    =Sum(If(Left(D5:D12,2)="01",F5:F12,0))


    should work, but you need to type the formula then, instead of Enter, press Ctrl / Shift / Enter. This should place curly brackets around the formula, i.e.


    {=Sum(If(Left(D5:D12,2)="01",F5:F12,0))}


    Hope this helps.


    Regards,
    Batman.

    Hi nounei,


    Welcome to the forum.


    Instead of combo boxes, for the application you have attached, I suspect you would be better using data validation.


    Select the cells in which you want a particular drop down list to appear


    Click Data > Validation ...


    In Allow, select List


    In Source, collapse the box and select the cells containing the list to appear in the drop down, then expand the box. If, as in your example, your data is in a different worksheet to the drop down, validation (at least in Excel 97) will not allow you to enter a sheet name different to the active one. You will need to get round this by, before setting up the validation, naming the ranges you want to use (e.g. Appliance for Sheet2!B1:B21) and in the Source field type =Appliance.


    You will not need to link to individual cells as the drop downs will appear for each cell you have originally selected.


    I hope this is along the lines of what you are looking for.


    Regards,
    Batman.