Autofilter and copy columns in array sheets- NON CONTIGUOUS COLUMNS

  • Hello everyone,

    Tryng to find the solution is driving me insane.

    the code that vcoolio very kindly provided has helped me tremendously for my planning tasks at work, It is an Autofilter that copies data into an array of sheets based on sheets name. And that saved me hours of manually copy /paste cell by cell from oe sheet to another at work... And as machines have to be changed 5 to 8 times a day and there are 19 of them...I really need this code.

    Only problem is that my master sheet has changed, as the main software has changed (I export excel master from main databas in the company).

    So now I need the code in the attached example file, to split columns (union does not work anymore as columns to be copied are not contiguous anymore)


    How can I tell the code to Autofilte but split columns as follows:

    colum C from Master must go in column B in destination sheet, column D goes in C in dest sheet, F in D, E must remain empty on destination sheet, then G goes in F, J goes in G on dest sheet and finally E from master goes in colum H destination sheet.

    I attach file with changed columns on master and machin sheets... I really hope you guys can help me, because I am loosing sleep over this one: I do not know ho to tweak the Union columns to strColsFromTo. I know I need the latter but I really have no clue on how to insert strColsFromTo in this code.


    ( vcoolio if you would be so kind as to help me again it would very very helpful,...).

    Thank you all in advance , really hoping to hear from you.

    Files

    • Clairexcel.xlsm

      (25.44 kB, downloaded 16 times, last: )
  • Try the attached.


    Code changed to

    Files

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • KjBox you literally saved my next weeks at work. I cannot thank you enough. It does exactly what I have been trying to do (and loosing sleep over it).


    Billion thanks really. Perfect. Just perfect. I will now carefully study the code.

  • You're welcome. Thanks for the Like

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • KjBox just a few questions:

    my real file has the master and machines sheets named as 2, 3, 5, 10, 12, 13, 14, 16, 17, 18, 19. Besides though there are other sheets in this wbk which do not have to be affected by this code.

    Can you please explain how to:

    1. Add the other machines? Which are the machines in the code? Are the x, y, e, i ? Do I need to add other variables? I am sorry, I am a begginer in vba, usually I understand codes, but this is quite new and complex..

    2. The sheets are already created, I see the code has an add instruction, will this add or delete my original sheets?

    3. I need the code to only add new ros from master to the sheet, under the existing ones, not cancel the old ones. How can I tweak the code to do this?

    Thank you so much, I know you already helped me a lot!

  • You do not need to worry about other machines. The code uses a Scripting Dictionary to get all the machines from the Master, it then loops through those and updates each sheet in turn.


    The .Add just adds machine numbers to the Dictionary keys, nothing to do with adding or deleting sheets or data


    The code will add to existing data in each machine sheet.


    You did not mention anything about column A of the machine sheets. Week Days can be included in the code, how do you want Week Days displayed? They can be numerical or text, for example 1/1/2021 could show as 6 or "F" or "Fri" or "Friday". If you prefer numerical then what do you want to use as the first day of a week (Sunday or Monday)?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • You do not need to worry about other machines. The code uses a Scripting Dictionary to get all the machines from the Master, it then loops through those and updates each sheet in turn.

    Ok, I was worrying about this, because as I said I am a begginer, and I did not understood you code well (because it is complex for me). So not seeing an array specified, I was worried it may affect other sheets in the wbk that also may contain manìchine numbers in column A, although the sheet names are different , e.g Production, Items, etc. I only need the code to work with the numerical named sheets.. :)


    Quote ""You did not mention anything about column A of the machine sheets. Week Days can be included in the code, how do you want Week Days displayed? They can be numerical or text, for example 1/1/2021 could show as 6 or "F" or "Fri" or "Friday". If you prefer numerical then what do you want to use as the first day of a week (Sunday or Monday)?"

    IOh I really didn't want to bother you with that. I can insert mannually ...MAchines have column A Like monday, Tuesday and so on, but week days are every other row. Monday- emtpy cell- Tuesday etc.


    There is one issue though, I am sure I am doing something wrong...

    when I put the code into my real wkbk it gives me an error (but may be because my real machine sheets begin in row 8??

    I attach the sample with you code but with of the real machine sheets templates, it only differs in the headers. I am sorry I though I can tweak the code myself, but I fell like a rookie...

    The error I get is this:

    Code
    1. .Cells(4, 1).Resize(lRow - 3, 8).Borders.Weight = 2

    thank you so much, this is really important for me, otherwse working is a error high risk nightmare

  • KjBox as you can see form the new sample with real machine templates attached, during the week there are rows already planned and I need thos to stay put. every day when new orders come in I extract them in the master file, and then I need to put the new rows (new planned items) under the already existing one in the machines, under them.

    Can you change the code so that it doesn't overwrite the old rows?

    Thank you anyway, vey appreciated.

  • So you always need an empty row between each week day?


    Is Column C ("UM" header) always "Kg"?


    What font size do you need on the machine sheets? The sample file has different sizes in different cells.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • So you always need an empty row between each week day?


    Is Column C ("UM" header) always "Kg"?


    What font size do you need on the machine sheets? The sample file has different sizes in different cells.

    1.Yes always need an empty space between week days, because it is considere morning and afternoon.. In fact, when in the MAster file in Column Q hours are more than 5, I insert the " " "" " under the item row in machine sheet, as it means the molding/pressing will keep the machine busy all day long. Otherwise I place the next Item in the next row, without " " """ ". ( doing the machines manually is hell, literally...)

    2. Column E (which has the "UM" Header ) will always contain the string KG, which I insert mannually now.

    3. Font size should be a 24 in all cells except for the G and H columns, as finishings and batches may have more characters so I need to make them smaller like size 18... The size is not uniform right now as I write (copy paste manually every single row, in each machine, many times a day and I hastily format size...

    Thank you KjBox

  • So you run the code once a day using just the data for each machine for that days date?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • KjBox, I will try to explain.

    I now do every row copy and paste manually, cell by cell, item by item, because the previous code does not work anymore, columns have been changed, so union does not apply anymore.

    If you will help me with your new code, I will run it several times each day, as many times as I update my Master sheet which contains only new ordered items to put on the machines.

    So when the Master sheet updates with new orders/items (and the items that I previously put on the machines re deleted from Master), I have to run the code, and insert in the machine sheets the new rows to be planned; and I have to insert the new rows in the first empty row of the machine sheets, under the already planned items. So I could be needing to run the code three to eight times a day.

    I hope I explained well, if not please tell me. Thanks

  • I wanted to add, that in a day I may have to add only on two or three machines new items, not on all, that is another important reason for the code to not clear the already occupied rows in the machines when I run it...

  • It seems I am not alloed to edit ugh... KjBox, I wanted to add that when I run the code is to plan ahead for the week, aka adding items under pre existing rows for the next days, week. Sorry for so many details, I just want to be clear.

  • I am a bit confused over morning and afternoon for the same date.


    Would the master sheet always be updated with bot AM and PM (where there would be a change of mould within the same day) or could there be just one (AM) for a given date in one update of the master then a second for the same date in a later update of the master?


    I hope that makes sense!

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.