Posts by daniel.c

    Re: Automatic Updating Of Master Worbook From Slave Workbooks


    Hope this will be OK. I forgot some things :


    If you have any trouble with it, please, post the workbooks again to refresh my mind.
    Daniel

    Re: Automatic Updating Of Master Worbook From Slave Workbooks


    Hi.
    In the two macros, add the following lines at the beginning of the macro :

    Code
    Application.ScreenUpdating = False
        Calc = Application.Calculation
        Application.Calculation = xlCalculationManual


    and, at the end, add :

    Code
    Application.Calculation = Calc
        Application.ScreenUpdating = True


    At the top of the module, add :

    Code
    Public Calc As Long

    Re: Automatic Updating Of Master Worbook From Slave Workbooks


    It is important to be sure that there are no data in columns A:D in the master workbook. As the 2 slave workbooks don't have the same structure, I can't use a loop and there must be two different treatments.

    Re: Automatic Updating Of Master Worbook From Slave Workbooks


    1. 39126 means "13/02/07"; It's the way Excel computes time (day 1 is "01/01/1900" (sometimes "01/01/1904). Enter that date in a celland apply standard number format and you'll get 39126. I added a line of code to correct it :

    Code
    c.Offset(, 26).NumberFormat = "dd/mm/yy"


    The macro I post below should certainly be modified. I used column C to add the department. Moreover, if column A is not used, the filters instructions have to be modified. I created three department files :
    - dept1.xls
    - dept2.xls
    - dept3.xls
    I am just thinking that I did not change their structures (IDs in column A and dates in B).
    You better send a new sample file. Anyway, here is my code (see also attached file).


    Of course, I'll add comments when the macro will be finalized.

    Re: Automatic Updating Of Master Worbook From Slave Workbooks


    Try :


    A missing dot that matters !

    Re: Automatic Updating Of Master Worbook From Slave Workbooks


    I moved the "updated slave workbook" sheet to a new workbook which i named "SlaveBook.xls". Paste the following code in the "ThisWorkbook" module :


    As you have 3 slave files, you'll have to filter the department column too.

    Re: Automatic Updating Of Master Worbook From Slave Workbooks


    Quote


    Does this mean that I have to traverse the entire worksheet (several thousand entries), every time I open the master workbook, and crosscheck every blank payment date with the actual entry in the slave workbook to see if the payment date has been added since the last time? Or is there an easier way?


    Yes, but you did not have to read each row. You should filter the data to find the empty payment dates and work on the filtered rows. Alternatively, you may use the "Find" instruction to find blank payment date cells. Feel free to post a sample file, and i'll return the corresponding code.

    Re: Create Mailing Lists From A Spreadsheet


    The command button and the drop down are on the "Interface" sheet so, you can hide the "data" sheet. In the attached file, the sheet is "veryhidden", that means that it can only be displayed by macro.
    For further protection, the workbook must be protected with a password, so that it has to be unprotected again before the sheet can be displayed.

    Re: Prevent Editting And Selecting Of Range In Newly Created Workbook


    You should not be able to edit the cells if they are locked and the sheet protected. Is this the case or do I miss something ? To prevent the selection of locked cells, add :

    Code
    ActiveSheet.EnableSelection = xlUnlockedCells


    after :

    Code
    ActiveSheet.Protect , Password:="whatyouwant"

    Re: Automatic Updating Of Master Worbook From Slave Workbooks


    If the department ID is stored in the sales row, you don't need to store anything, it will be simple to retrieve it :
    1. retrieve the last row in the master file;
    2. retrieve the corresponding sale in the source file.
    If it's not the case, yes, store it in a cell of the master file.

    Re: Prevent Editting And Selecting Of Range In Newly Created Workbook


    Try :

    Re: Create Mailing Lists From A Spreadsheet


    Try from sheet Interface :

    Re: Automatic Updating Of Master Worbook From Slave Workbooks


    First, sorry for the folder change ;-))
    As your code works, you copy cell A1 of sheet 1 from the first file, cell A1 of sheet 2 from the second file, etc.
    I think you should replace :

    Code
    Source.Sheets(i).Range("A1").Copy Tgt


    with

    Code
    Source.Sheets(1).Range("A1").Copy Tgt


    I tested the modified macro and it seems to work (provided you only want cell A1 to be copied).
    As regards to :

    Code
    myFileName = Dir(.FoundFiles(i))


    it only returns the file name, which I use in the Select Case - End Select part.

    Re: Automatic Updating Of Master Worbook From Slave Workbooks


    Not sure it's quite what you want... Try :

    Re: Change Message Box To Select Folder Not File


    Just put it :

    Code
    Sub Test()
        Dim Msg As String, myFolder As String
        'retrieve folder name in myFolder variable
        myFolder = GetDirectory(Msg)
        '*** subsequent code
    End Sub

    Re: Change Message Box To Select Folder Not File


    If that's the case, paste the following code at the beginning of a module and execute the "test" macro. The result will be displayed in a msgbox :