1 Attachment(s)
Moving different rows data to one row based on criteria to optimise on printing
Hi ,
I need help on how we can automate this task through VBA as actually we are doing this manually and is very time consuming and many errors also are being made.
Thank in advance for any tips and guidance.
We have actually a visual planning which we have already generated through many calculations (was possible also with the forum help to automate it).
Refer to the attached file on sheet Actual_Visual_Planning. This result is obtain after all the calculation is done and is generated with the help of VBA as the initial calculation are not as such.
The visual planning are printed and given to all department to follow on which date what order is starting and also on which line number, etc.
The problem when we are printing for info the planning itself is with almost 1000 rows with 26 line of production and almost 8 -25 orders plan per production line.) this is taking a lot of pages like almost 20-25 pages sometime even more.
We have come up with a solution where we are copy and pasting the planned orders from the below row for the first row of the same Line Number and deleting all the orders plan below thus keeping only the data pasted in the single row.
Refer to the sheet Exp_Visual_Planning to have a better idea of what we are doing actually manually.
When pasting we are just moving the planned data same as the date it starting thus keeping them in the same column as it was initially on sheet Actual_Visual_Planning.
And same apply when the line number changes, we will paste to the first row of the concern line number.
So its just like moving up the planned data for sheet Actual_Visual_Planning up on the same column but on the first row for each production line.
This is taking a considerably amount of time for doing it as its being done manually and we have to copy paste for all the 26 lines like that taking into consideration different orders we have and also the risk of mistake we are making.
By so doing we have been able to print the visual plan in almost 6-8 pages instead of 20-25 pages.
I will be much grateful if I can get some guidance and tips on how I can automate the copy, paste and delete the below lines through vba please.
: pray2:: pray2::banghead::banghead:
1 Attachment(s)
Moving different rows data to one row based on criteria to optimise on printing
Hi Okami,
thank you so much works very well with the overlapping. can you please for a last one advise how we can place the borders same as the below example file attached.
thanks again for the kind help and time.
1 Attachment(s)
Moving different rows data to one row based on criteria to optimise on printing
Hi okami,
As requested here attached the file with part of the data. I am just sending the part which is concern with the visual planning report .
- You will see on the file there are 5 sheets( some of the sheets has been added just for your info.
- Sheet Customer_Color_Codes is where the customer colors are keep so as to place the color as per the customer color we have define.
- Sheet Master_Visual is where we will have the data to start generating the visual planning report. This data is obtain from calculation which are done before.
- Sheet VISUAL DISPLAY REPORT is the report which have already been generated.
- Sheet LINEAR VISUAL PLAN this is the part that you have help me to be able to show the user the orders plan in one line and improve for the printing.
- You will see I have place the code on buttons that I have place on the master_Visual.
The steps I am proceeding actually are as follows:
For better understanding please do run as explain below then you will see exactly the process.
- Click on the button populate lines.
- After it has duplicate the lines I need to add Number 1 at the end on the column G. this is because to keep the last row in odd number to help for line insertion in between the order reference which were duplicated otherwise it will insert line in between the order reference itself. Sheet Visual Planning format error shows how the error will be if the Number 1 is not place.
- <<This one takes almost 57 secs to process.>>
- Click on button Build Visual Display Report this will generate the visual same as sheet VISUAL DISPLAY REPORT. <<This one takes almost 25 secs to process.>>
- After the Visual display report has been generated then click on the button Linear Visual Plan to have the last final results same as Sheet LINEAR VISUAL PLAN (the one that you have help). <<This one takes nearly 8-10 secs to process.>>
Thank you in advance if you can guide on speeding this part. Just to inform all these were done manual to get the final results which were taking days to do with all the mistakes and involving 2 people. Today we have been able to do it in let say may be 5 mins compare to days.
If I can still improve it then it good enough.
1 Attachment(s)
Moving different rows data to one row based on criteria to optimise on printing
Hi Okami,
I have tested the code you have done it works very well and very quick too but i dont know why all of a sudden am having error to generate the report. Am attaching the file below for your understanding.
i have even run it with the break F8 and still could find the mistake if you can just give some idea on it.
1 Attachment(s)
Moving different rows data to one row based on criteria to optimise on printing
Hi Okami,
thanks for the clarification and help.
one last help brother.
for the T-A-O-OF-VA-L or T-A-O-OF-N-L
which is done by this code :
Code:
If Left(.Value, 1) = "T" Then .Characters(Start:=1, Length:=1).Font.Color = IIf(arr(rw, 12) = "", -16776961, -11489280) ' T 'Use your method intact, only change worksheet function into vba.
If Mid(.Value, 3, 1) = "A" Then .Characters(Start:=3, Length:=1).Font.Color = IIf(arr(rw, 10) = "", -16776961, -11489280) ' A
If Mid(.Value, 5, 1) = "O" Then .Characters(Start:=5, Length:=1).Font.Color = IIf(arr(rw, 19) = "", -16776961, -11489280) 'O
If Mid(.Value, 7, 2) = "OF" Then .Characters(Start:=7, Length:=2).Font.Color = IIf(arr(rw, 25) = "", -16776961, -11489280) 'OF
If Mid(.Value, 9, 2) = "VA" Or Mid(.Value, 9, 1) = "N" Then .Characters(Start:=9, Length:=2).Font.Color = IIf(arr(rw, 19) = "", -16776961, -11489280) 'O
If Mid(.Value, 11, 1) = "L" Then .Characters(Start:=9, Length:=2).Font.Color = IIf(arr(rw, 19) = "", -16776961, -11489280) 'O
I have added some code more so that but if you will run the code you will see the VA and L are not changing color based on the condition that are set for
Code:
If Mid(.Value, 5, 1) = "O" Then .Characters(Start:=5, Length:=1).Font.Color = IIf(arr(rw, 19) = "", -16776961, -11489280) 'O
can you please help on this part please i have tried many times it remain the same it don't change the color on this part. which means if the O is red the VA and the L also shall be Red else Green.
And also on this part of the code where it takes the interior color of the cells:
Code:
For Each c In dTmp.keys
r = r + 3
arr1 = Split(dTmp(c), "+")
.Cells(r, 1).Resize(2, 2) = Split(c, ",")
.Cells(r, 1).Resize(2, 2).Interior.Color = arr1(0)
For i = 1 To UBound(arr1) Step 6
It shall also change the font color accordingly as it changes only the interior color and leaving the font color black for which in some lines the font is not visible where its darker. see row 15-16 . 18-19 for example the font for the lines number and name shall have been white.
Thank you in advance.