Move data to another sheet with few conditions

[Anbuselvam K]

Dear Excel Genius,

I have an excel workbook having the sheets "Production", "Sales", and "Free Sample". Production Sheet J6 to LastRow have a data validation list (Sales, FreeSample, Modified, Rejected, Storage)
I need a VBA Code to move data whenever I run the code to the respective sheets depending on the data validation list name (If it is "Tranfered Sum" then no need to Move)

For example, If cell J100 have "Free Sample" then that entire row has to move to the sheet "Free Sample". likewise, all rows with it are formats only No Formulas, All numbers should be 3 decimals "0.000" If no sheet name existed then it has to create one with the list name. For example, If I added a new name in the data validation list called "Return Items" and it is placed in the J column then need a new sheet with the name "Return Items" and then move the data while executing the code

All the sheets in row 5 should be Headings as same as Sheet "Production" (Note: If I added/change the column in the production sheet then it has to do the same with all the sheets with blank rows of data, I will add new data manually for the existing transferred rows. Also, in Rows 1 to 4 users are free to use formulas in all the sheets)

It has to move the data to the next row of the LastRow to the respective sheet (Production to Sales sheet I transferred 10 rows of data from rows 6 to 15, so next time it has to move the data from row 16 of the sales sheet)

Each time before initiating the transfer need to show MsgBox Each category name and how many rows are ready to transfer. Yes to Proceed No to cancel

All the sheets in the list name Sales, FreeSample, Modified, Rejected, Storage) If I select After column "B" and above row 5, then it has to show the MsgBox as below. The target cell should be 1, if more cells are selected then show a warning msg.

Target row values with the below headings and From Column R to lastCol headings with target row values except "0"


Expecting your valuable reply. Thanks in advance.

 

[Marc L]

Hello, following your logic the execution will be 50-100 times slower than Excel basics any Excel user can operate …​

If it is "Tranfered Sum" then no need to Move

Easy to code if working only with existing worksheets …​

Is this 'Tranfered Sum' is an unique case ?​

​

If no sheet name existed then it has to create one with the list name.

Opposite of previous point easy code …​

​

All numbers should be 3 decimals "0.000"

Moving row so as it is !​

​

Each time before initiating the transfer need to show MsgBox Each category name and how many rows are ready to transfer. Yes to Proceed No to cancel

As counting how many rows by category is useless, double process so wasting more time …​

(Can be achieved without doubling the process with arrays but cells formatting will be lost !)​

Even the message box is useless if the VBA procedure is launched via a button.​

​

If I select After column "B" and above row 5, then it has to show […]

As contradictory with « in Rows 1 to 4 users are free to use formulas in all the sheets »​

and as obviously it is a different subject so can't be coded in the same VBA procedure,​

to ask in a future thread if only the Logic is respected …​

​

 

[Anbuselvam K]

Easy to code if working only with existing worksheets …

Boss, I will make the respective sheets available before initiating the code.

Is this 'Tranfered Sum' is an unique case ?

Transfered Sum "Sales"
Transfered Sum "Free Sample"
Transfered Sum "Modified"
Transfered Sum "Rejected"
Transfered Sum "Storage"
All of the above should be placed in rows 6 to 10 in the Production Sheet. it is the sum of each RM's (Column J and From Column BP to LastCol only have values) Even though we transfer all the rows to the respective sheet, I need the sum of each RM's Consumption and the Cost in the production sheet for the calculation purpose.

Opposite of previous point easy code …

As I mentioned I make sure the sheet is exists before initiating the code.

As counting how many rows by category is useless, double process so wasting more time …(Can be achieved without doubling the process with arrays but cells formatting will be lost !)Even the message box is useless if the VBA procedure is launched via a button.

Okay, then we can make only selected rows can be transferred. For Example, if I select 1 or 10 cells in column J, then only those rows can be transferred instead of all the rows after row 5. because I may have some incomplete data in rows which should transfer later.

As contradictory with « in Rows 1 to 4 users are free to use formulas in all the sheets »and as obviously it is a different subject so can't be coded in the same VBA procedure,to ask in a future thread if only the Logic is respected …

Sorry, Boss, It is my typo mistake. It is after row 5 not above row 5.

 

[Marc L]

Ok don't bother now for non existing worksheet 'cause is an unique case with cell starting with 'Transfered Sum *'​

but to be sure - again - attach at least a better workbook relative to all possible cases​

or you may have to fit yourself my VBA demonstration to your real workbook !​

Don't bother neither for the message box counting rows by category but the 'Transfered Sum *' will not be included obviously.​

​

Moving all the non 'Transfered Sum *' at once following my way according to your initial post it will be instant but​

if you prefer following your way only from the column J selected cells - with or without the message box ? -​

it will be 50-100 times slower, not very a concern with only ten cells selected but as a reminder, the more cells, the slower …​

​

 

[Marc L]

because I may have some incomplete data in rows which should transfer later

So only the complete data could be moved if only you well describe what exactly 'incomplete' means …​

​

 

[Anbuselvam K]

Ok don't bother now for non existing worksheet 'cause is an unique case with cell starting with 'Transfered Sum *'but to be sure - again - attach at least a better workbook relative to all possible casesor you may have to fit yourself my VBA demonstration to your real workbook !

Attached here is the sample sheet with sales and free sample transferred and transferred sum sales and free samples added for your understanding.

Don't bother neither for the message box counting rows by category but the 'Transfered Sum *' will not be included obviously.

Yes, No need included 'Transfered Sum' MsgBox

Moving all the non 'Transfered Sum *' at once following my way according to your initial post it will be instant butif you prefer following your way only from the column J selected cells - with or without the message box ? -it will be 50-100 times slower, not very a concern with only ten cells selected but as a reminder, the more cells, the slower …

Okay, in this case, we can transfer only the row which has filled all data from column 1 to the last col. otherwise, let the code warns of missing data.

So only the complete data could be moved if only you well describe what exactly 'incomplete' means …

Any blank cells between Column 1 to LastCol are called Incomplete.

 

[Marc L]

According to your last attachment - why so few data in Production ?! - no need to check column J for 'Transferred Sum *'​

'cause this kind of data has always some blank cells so incomplete like 'Modified', 'Rejected' & 'Storage' as well ?​

​

 

[Anbuselvam K]

According to your last attachment - why so few data in Production ?!

In the last attachment, I have transferred all the completed rows of data to the respective sheet. that is why only a few rows of data are there.

no need to check column J for 'Transferred Sum *'

Note that Transferred Sum always has blanks from Column A to till consumption Starts. So whenever J6 to LastRow(Starts with the letter "Transferred") no need to consider transfer as well it is blank cells. this row is just for calculation purposes only.

'cause this kind of data has always some blank cells so incomplete like 'Modified', 'Rejected' & 'Storage' as well ?

In another way, we can consider for the transfer data only the values not starting with "Transferred" like Sales, Free Sample, Modified, Rejected, and Storage.

Even Column J has Sales, Free Sample, Modified, Rejected, and Storage but there are blank cells from column A to Q, it is incomplete and does not need to consider for transfer.

 

[Marc L]

Any blank cells between Column 1 to LastCol are called Incomplete.

As LastCol = column FK … But with​

there are blank cells from column A to Q, it is incomplete

that's so confusing ‼​

​

 

[Anbuselvam K]

Boss,

The columns A to Q are filled without blank then the remaining columns get filled with their formulas. this is the way A to Q and the Column 1 to LastCol are not blank same.

'Transferred Sum' is always blank in A to Q except for J as it is just the sum of R to LastCol.

 

[Marc L]

So what is the columns range criteria to check if incomplete : only A:Q or all A:FK ?​

​

 

[Anbuselvam K]

So what is the columns range criteria to check if incomplete : only A:Q or all A:FK ?

A:Q

 

[Marc L]

If the VBA procedure is launched via a button : from which worksheet, Production ?​

​

Actually that well works only with your initial post attachment once the content of Production row #4 is cleared​

in order to match with your real workbook : instant execution, no issue …​

​

But your real workbook is a mess : VBA error message at the opening, execution damn slow !​

I suppose it's relative to the worksheet design - far different than your initial post attachment ! - or maybe my elder Excel versions …​

So you must indicate what is your exact Excel version in order another helper with the same version can give it a try.​

​

As your initial post attachment does not have any Excel table my VBA procedure has a minor issue with your real workbook​

which has 3 tables within the same Production worksheet !​

Another difference : in your real workbook Production has empty columns in BP:BR then some columns filled after, BS:DP , DT:FQ and NU:NX.​

As my actual code based upon your initial post attachment works with consecutive columns so A:BO​

but as there is no expected result in your real workbook I have no clue if it is the expected result !​

​

So that leads to the same feeling than your previous threads like I just wasted my time​

with again an initial post under the level of what any Excel forum expects for …​

​

 

[Anbuselvam K]

If the VBA procedure is launched via a button : from which worksheet, Production ?

Yes

Actually that well works only with your initial post attachment once the content of Production row #4 is clearedin order to match with your real workbook : instant execution, no issue …

Yes #4 is same as real worksheet.

But your real workbook is a mess : VBA error message at the opening, execution damn slow !I suppose it's relative to the worksheet design - far different than your initial post attachment ! - or maybe my elder Excel versions …So you must indicate what is your exact Excel version in order another helper with the same version can give it a try.

Just for your Idea, I shared the real workbook which is damn slow. As far as my initial post request of move data to another sheet works all versions of excel.

As your initial post attachment does not have any Excel table my VBA procedure has a minor issue with your real workbookwhich has 3 tables within the same Production worksheet !Another difference : in your real workbook Production has empty columns in BP:BR then some columns filled after, BS:DP , DT:FQ and NU:NX.As my actual code based upon your initial post attachment works with consecutive columns so A:BObut as there is no expected result in your real workbook I have no clue if it is the expected result !

I am just modifying with no tables of real worksheets similar to the initial post attachment. So you provide the code as per the initial post.

So that leads to the same feeling than your previous threads like I just wasted my timewith again an initial post under the level of what any Excel forum expects for …

If you provide the code as per the initial post attachments, then none of our time will be wasted.

 

[Marc L]

Yes #4 is same as real worksheet.

No as row #4 is not 'blank / empty' in your initial post attachment so that obviously fails with the real workbook !​

​

Following your initial post logic with its attachment, a classic code needs a bit more than 10 seconds.​

Following my faster way the message box comes instantly and the execution needs slightly more than 0.1s​

the reason why I wrote « following your logic the execution will be 50-100 times slower than Excel basics any Excel user can operate » …​

​

With your real workbook my faster way displays the message box after more than 50s​

versus instant with your initial post attachment so 500 times slower​

but as the execution needs around 73s more then it's more than 720 times slower​

so combining both times its more than 1200 times slower ‼ ​

​

As I still do not know which is the expected layout with your real workbook (again which columns ?!)​

so my tricky faster code may not be the way to go then with the classic way it could require more than an hour …​

​

Maybe I'm totally wrong for the path to follow so another helper may have a better idea​

but as it may depend on which Excel version you use, as guessing can't be helping ! …​

​

 

[Marc L]

With these conditions as my tricky faster way can't work as it is with your real workbook​

then I would post something closer to a classic way like any VBA beginner can give it a try,​

easier to mod for another context than the specific initial post attachment …​

​

Or maybe you do not ever need to keep the cells formatting (could be easier but faster with raw data) ?​

​

 

[Anbuselvam K]

@Marc L
Thanks a lot for your time spent towards my thread. Finally I couldn’t supply the required information in my first post. If possible post your lighting fast code as per the initial post. Don’t consider the real work book as its having more mess.
thanks again for your help.

 

[Marc L]

Any answer to my previous post question ?!​

​

 

[Marc L]

According to your initial post attachment the message box should be like this ('Oui' = Yes) :​

​

​

​

 

[Anbuselvam K]

Boss,
The above heading 5th row is not in our calculation area. So I did not mention it.

Yes, Boss,
The MsgBox is correct. As there are blank cells in 'Floor/Flat Number'
In the first run of code, I will transfer 77+22=99 then I will get the blank to be filled for the second run.

 

[Marc L]

The above heading 5th row is not in our calculation area. So I did not mention it.

No clue why you wrote this …​

​

And about my post #16 question ?​

​

 

[Anbuselvam K]

Please note that I will transfer all the existing data very carefully after filled

With these conditions as my tricky faster way can't work as it is with your real workbookthen I would post something closer to a classic way like any VBA beginner can give it a try,easier to mod for another context than the specific initial post attachment …Or maybe you do not ever need to keep the cells formatting (could be easier but faster with raw data) ?

Yes, Boss, Please don't use any dictionary code. I need a code which can easily understand and is transferable for future changes.

Sorry, Boss, It is my typo mistake. It is after row 5 not above row 5.

The above heading 5th row is not in our calculation area. So I did not mention it.

 

[Marc L]

Please don't use any dictionary code.
I need a code which can easily understand and is transferable for future changes.

No dictionary, no message box … So direct execution ?​

As the code is not such easy and can't be transferable according to your initial post attachment layout 'design' …​

​

And again I did not receive a crystal clear anwer to my post #16 :​

Or maybe you do not ever need to keep the cells formatting (could be easier but faster with raw data) ?

 

[Anbuselvam K]

#16
With these conditions as my tricky faster way can't work as it is with your real workbook
Noted Boss.

then I would post something closer to a classic way like any VBA beginner can give it a try,
easier to mod for another context than the specific initial post attachment …
I am okay with the classic way code as it can be easily modified by the beginner.

Or maybe you do not ever need to keep the cells formatting (could be easier but faster with raw data) ?
Boss,
The transfer of the format of the cells is important but with VBA Code if it is difficult, then I copy and paste the format only to the destination before initiating the VBA code.

 

[Anbuselvam K]

No dictionary, no message box … So direct execution ?As the code is not such easy and can't be transferable according to your initial post attachment layout 'design' …

I meant I need an easy understanding of code, not a complex or tricky one.