Posts by rollis13

    My suggestion without knowing any thing about you project:

    With the help of your Function, before updating column B I would copy the old data from column B into a hidden helper column (maybe somewhere in the sheet, in your example lets say column H) and only then update column B. With a formula in column C compare new data in column B against old data in the hidden column. Formula for column C:


    If you can't make any changes to your Function just have the copy of the old data done by the macro you have showen above.

    That's because you are activating the sheets Sheet(ws(i)).Activate to fetch the data and the last activated sheet will be the last of the ws Array so it's the "3. ...". It isn't necessary to activate a sheet to fetch data from it; just give the right reference to the ranges used.

    Now, first of all get rid of the trailing spaces in the sheet names, they only create confusion since they aren't visible.

    Then try this:

    Do you mean that every sheet has to create it's own template ? Just get rid of the ws Array and the cycle For i.

    Just set a variable (ws) to read the name of the sheet from which you launch the macro.

    Also I would close the template just after saving it if you don't need it straight away (see note in macro).

    There was an extra space at the end of sheet "3. ...." too.

    Yes, if the macro gets interrupted you need to manually delete the "Newsheet" but you could implement, at the beginning of the macro, a check if there already is a sheet named Newsheet and delete it before proceeding. But this can only happen while testing, once the macro is definitve this should no longer occur.

    Also done some cleaning out, have a try:

    'Loop through all sheets in sheets array
    For i = LBound(ws) To UBound(ws)

    Here you are looping through all the sheets but the loop ends before the code that saves the new file. That means that the new file will always contain copy of all the sheets. Move the line Next i to the end of the macro and move the lines Set shtName = ActiveSheet and nr = 2 just inside the For i loop.

    Leave it to you to spot/fix any other issues (probably there won't be any).

    As said it would be static since the name of the sheet is hardcode and was already in your macro, here:

    For Each cell In Worksheets("1.Power Distribution - Dimmer").Range("D5:D9, ...

    How will the macro work with other sheet names ?

    So, all you have to do is create a new variable (example: shtName) at the beginning and assign to it the name of the active sheet to be used here in the For/Each and later down in the macro when you assign to the variable N the path and the name of the file to be saved.


    Hi to all.

    This is what I came up with, have a try. Paste it wherever you prefer but it has no reference to a specific sheet so, as it is, it works on the activesheet:

    See if you can fit this macro in your project; needs to be pasted in the sheet's module.

    From Marius44 :

    The macro writes data in column D (so you can compare them to the desired column).