<div>
This is the same file that you posted before not sure what you need to achieve ! and it is not the solution that was delivered on previous question. a bit lost here.<br />
gowflow
</div>


<div>
Understood, what i want to achieve is this:<br />
<br />
I want that the lettertype and sheet name able to be different like this:<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/11_w46/882575/Capture.PNG" target="_blank" title="Capture.PNG (5 KB)"><img alt="Capture.PNG" class="file-block lazyload" style="max-width: 238px;" data-src="https://filedb.experts-exchange.com/incoming/2014/11_w46/882575/Capture.PNG" /></a> this is a screenshot from &quot;example_2.1.xlsm&quot;<br />
<br />
I want the changes to in this workbook: &quot;Scramed---12.11.2014-V03.<wbr />xlsm&quot;<br />
<br />
Reason for this is that one person/department is going to choose what lettertype it is and another person/Department will choose is there has been contact or no contact, from that it will generate a string which will be the key for the code to know in which sheet to copy the row into.<br />
As for now i have tried to make a formula but end with a space which i needed to make a new sheet with this name for the code to work. <br />
<br />
( yes i know i'm not good in excel...sorry)<br />
<br />
<br />
I hope this made sense...<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/11_w46/882577/Scramed---12.11.2014-V03.xlsm" target="_blank" class="file-inline" title="Scramed---12.11.2014-V03.xlsm (74 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Scramed---12.11.2014-V03.xlsm</a><br />
<a href="https://filedb.experts-exchange.com/incoming/2014/11_w46/882578/example-2.1.xlsm" target="_blank" class="file-inline" title="example-2.1.xlsm (30 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>example-2.1.xlsm</a>
</div>


Capture.PNG

Scramed---12.11.2014-V03.xlsm

example-2.1.xlsm

<div>
Sorry I still don't understand what you want:<br />
<br />
In sheet Data<br />
You have Col K that Is a formula from ColL and M<br />
Col L is a dropdown from Sheet Setup Col F<br />
Col M is a dropdown from Sheet Setup Col E<br />
<br />
So what is needed then ?<br />
Sorry sometimes I am slow !<br />
gowflow
</div>


<div>
No worries. i think its me that is not able to explain myself , ill try even better:)<br />
<br />
What i need is be able to is make Col L &amp;&nbsp;col M into one text/string in col K<br />
<br />
Like this:<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/11_w46/882581/Capture.PNG" target="_blank" title="Capture.PNG (4 KB)"><img alt="Capture.PNG" class="file-block lazyload" style="max-width: 207px;" data-src="https://filedb.experts-exchange.com/incoming/2014/11_w46/882581/Capture.PNG" /></a><br />
The code you made looks in Col K and transfer the data into its respectable sheet, this works and everything is good there, But i want to be able to change the name of the sheet, so its not the same as in Col K, but from the Setup it will look in col K in the data sheet and see okay it is &quot;A1 - Letter1&quot; it should transfer the data into sheet: &quot;Letter A1&quot;, so the data in col K and the sheet name does not need to be the same... just like you made in the &quot;example_2.1.xlsm&quot; file...<br />
<br />
What i have done is that i made a formula that does this:<br />
<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/11_w46/882582/Capture.PNG" target="_blank" title="Capture.PNG (3 KB)"><img alt="Capture.PNG" class="file-block lazyload" style="max-width: 213px;" data-src="https://filedb.experts-exchange.com/incoming/2014/11_w46/882582/Capture.PNG" /></a><br />
And joins the textstring into one string and this row will now be transfered into the sheet called: &quot;Contact Letter A&quot;<br />
<br />
<br />
make sense?
</div>


<div>
The formula i made insert a space(&quot; &quot;) meaning that i was need to make a new sheet called &quot;&nbsp;&quot; for the code to work since some rows need to be blank.
</div>


<div>
Still not clear what you want from me.<br />
<br />
Please state what is the problem and what you need me to do.<br />
gowflow
</div>


<div>
Having read your post several times and having done a thinking I believe I maybe 'discover' what you are trying to do.<br />
<br />
Let me explain what you want to do and propose my solution.<br />
<br />
You have Data that you want to distribute in sheet and you wanted distributed in categories. I presume it is data related to Customers and you want to distribute it by Department or whatever but the most important is to have it flexible. You want to put information in Col E and Col F of sheet Setup and you want automatically the formula that is in Col K reflected in sheet Data.<br />
<br />
Please advise if this is correct.<br />
gowflow
</div>


<div>
Okay, <br />
<br />
The sheet names can change but the data sheet will always be the same, obviously with change of data from time to time.. so if the textstring in Col K is equal to =&quot;ContactLetter A&quot; it should transfer this row into the sheet called &quot;Letter A1&quot;<br />
<br />
In otherwords<br />
<br />
If Col K in a row in the data sheet is &quot;Contact Letter A&quot; i still want it to be able to transfer the entire row to sheet &quot;Letter A1&quot;<br />
Or another example, if its Col K in a row in the data sheet is &quot;&nbsp;No Contact Letter A&quot; I still want it to be able to transfer the entire row to &quot;Letter A2&quot;<br />
<br />
and so on..<br />
<br />
This is why:<br />
<br />
Col L will be set by one department which has no knowledge about any other data in this workbook, the only this their job will be is to set if there has been contact or no contact.<br />
<br />
Col M will be set by another department which has no knowledge about any other data that is in this workbook their only job is to choose what letter type need to be send to a specific regtype.<br />
<br />
The above mentioned processes are manually done by people, <br />
<br />
From the decisions they have made in Col L and Col M, a third department needs to check the entire data sheet and then split/transfer the data into its respectively sheet (this is that code you made).<br />
<br />
<br />
I think you made this in the sample file you provided the first time, but i might be wrong...
</div>


<div>
WEll I noted the following:<br />
<br />

<blockquote>
<br />
&quot;ContactLetter A&quot; it should transfer this row into the sheet called &quot;Letter A1&quot;<br />
&quot;&nbsp;No Contact Letter A&quot; I still want it to be able to transfer the entire row to &quot;Letter A2&quot;<br />

</blockquote>
<br />
Well you are confusing me more and more. Why A1 and A2 ??? why these numbering it is changing you did not have 1 and 2 before.<br />
<br />
Furthermore what we did is if Col K has something it will be transferred to that something. What I did was a simple translation of that something in sheet setup.<br />
<br />
Please tell me what is wrong with the macro now that need to be fixed as we may continue talking about the sex of angels but are getting no where ! I think it is the language that is a barrier here.<br />
<br />
gowflow
</div>


<div>
First of all I'm sooo sorry for confusing you more then before! and thank you for your patience!!!! really appreciated!!!!<br />
<br />
YES!!! that's what i want!!! a simple translation in the setup sheet! :D how stupid of me not to say it that ways!<br />
<br />
Nothing is wrong with the macro! and the sex of angels sound nice though but don't think its needed!
</div>


<div>
To make your life and my life easier please do the following:<br />
<br />
1) Make a copy of the workbook you just send me here Scramed---12.11.2014-V03.x<wbr />lsm and call it Scramed---12.11.2014-V04.x<wbr />lsm<br />
2) Open Scramed---12.11.2014-V04.x<wbr />lsm<br />
3) Put this formula in sheet Data cell K2<br />
=IF(AND(L2=&quot;&quot;,M2=&quot;&quot;),&quot;Misc<wbr />&quot;,TRIM(L2&amp;<wbr />&quot; &quot;&amp;M2))<br />
4) in the cell of the formula or cell K2 there is a small dot on the right bottom corner of the cell just doubleclick on it and it will fill the formula down to the last row of data.<br />
<br />
Now what you have is the following:<br />
1) If you have Col that have blank it will remove the first trailing space so you have 'Manuel Letter' instead of having<br />
&nbsp;' Manuel Letter' note the first blank space.<br />
2) If both cells L and M are blank you will have Misc or we can put whatever you want there like others etc...<br />
<br />
NOW<br />
My intention was:<br />
1) Delete all the sheets you already created and keep only 1 hidden for me for the macro.<br />
2) Each time you run the macro it will look for existing sheets and clean htem and then proceed to distribute the data now here you have 2 options:<br />
&nbsp; &nbsp; &nbsp; a) If the data or row found has in Col K a string that the sheet already exist it will put the row there and move to next row.<br />
&nbsp; &nbsp; &nbsp; b) If the data or row found has in Col K a string that the sheet DOES NOT EXIST then it will create it and then append the row to it and move to next row<br />
<br />
etc.... till end of data.<br />
<br />
This way you may create whatever sequence or combination in Sheet Setup Col E and Col F and the macro will always update and create the sheets as per you denomination. Maybe today it is for you LetterA, LEtterB ... and tomorrow it will be simply Account Found, Account Not Found etc,, whatever then it will still work<br />
<br />
Please let me know.<br />
If I am offtrack I appreciate you simply explain in plain English.<br />
gowflow
</div>


<div>
uff more lost here !!!<br />
<br />
THEN WHAT IS WRONG ?????<br />
<br />
WHY YOU POSTED THIS QUESTION ?<br />
<br />
gowflow
</div>


<div>
Sorry :(<br />
<br />
What is wrong that it does not translate from the setup sheet anymore..
</div>


<div>
FINALLY !<br />
<br />
What do you want it to translate for sure it is not translating !!!! What Column can I use ? B ? what would be the translation as it was fix in the other option !!<br />
<br />
Did you try what I suggest before the new formula and the way I proposed ?<br />
gowflow
</div>


<div>
You can use the Col B in the setup sheet yes!<br />
I need it to translate it like this(it needs to be changed later on aswell so would need to NOT be fixed but the code will look into for the translation in the setup sheet, if possible):<br />
<br />
&quot;Contact Letter A&quot; translate to &quot;Letter A1&quot;<br />
&quot;No Contact Letter A&quot; translate to &quot;Letter A2&quot;<br />
&quot;Contact Letter B&quot; translate to &quot;Letter B1&quot;<br />
&quot;No Contact Letter B&quot; translate to &quot;Letter B2&quot;<br />
&quot;Manuel Letter&quot; translate to &quot;Manuel Letter&quot;<br />
&quot;No Letter&quot; translate to &quot;No Letter&quot;<br />
<br />
I did insert the formula you wrote and it works like a charm, thank you!<br />
<br />
Here is the workbook with what you have told me to do.<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/11_w46/882619/Scramed---12.11.2014-V04.xlsm" target="_blank" class="file-inline" title="Scramed---12.11.2014-V04.xlsm (68 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Scramed---12.11.2014-V04.xlsm</a>
</div>


Scramed---12.11.2014-V04.xlsm

<div>
Well please sake good order for you and for all the Experts in the future. You could have saved yourself 1 day or to be precise 1/2 day if you had posted this question properly saying that you need to have the sheet transferred be looked at the setup sheet in Col B and not as it is in Col A as follows:<br />
<br />
&quot;Contact Letter A&quot; translate to &quot;Letter A1&quot;<br />
&quot;No Contact Letter A&quot; translate to &quot;Letter A2&quot;<br />
&quot;Contact Letter B&quot; translate to &quot;Letter B1&quot;<br />
&quot;No Contact Letter B&quot; translate to &quot;Letter B2&quot;<br />
&quot;Manuel Letter&quot; translate to &quot;Manuel Letter&quot;<br />
&quot;No Letter&quot; translate to &quot;No Letter&quot;<br />
<br />
Now we have more:<br />
YOu have sheets:<br />
Contact Letter A<br />
No Contact Letter A<br />
Contact Letter B<br />
No Contact LetterB<br />
Manuel Letter<br />
No Letter<br />
<br />
Shouldn't these sheets be renamed to:<br />
Letter A1<br />
LetterA2<br />
Letter B1<br />
Letter B2<br />
Manuel Letter<br />
No Letter<br />
<br />
????<br />
pls advise gowflow
</div>


<div>
Sorry Gowflow!!! i really appreciate you being soo patient and cool minded! i owe you big time!! so thank you and its noted! <br />
<br />
Yes please rename them, to streamline it, its my fault i made a few changes so forgot to change it in the newest workbook
</div>


<div>
ok last but not least !<br />
Shall we go on what I proposed ? or we keep it fix ?<br />
<br />
ie<br />
I mean by that delete all the sheets and create as we go along ... ?<br />
gowflow
</div>


<div>
if it checks the translation then yes,
</div>


<div>
No sorry! please keep it fixed! as it is now! since i will be need to mode code in the fixed sheets!
</div>


<div>
The next step for me is to make a mail merge function into Word.. but i wont eat your brain with that if your not in for it or in otherwords your tired of me me eating your brain :)
</div>


<div>
not tiered if post is correct with all info not just bits and pieces. As to word ... I am more of an Excel guy .. but here again let do 1 at a time.<br />
<br />
Let me concentrate on getting this one correctly.<br />
Will revert shortly..<br />
gowflow
</div>


<div>
You have removed in the formula<br />
Misc and replaced it by &quot;&quot;<br />
<br />
Does this mean that you only want to process the data that has something in Col K and leave the blank rows not proceesed ? as you had before created a sheet ' ' blank and put there some rows.<br />
<br />
What is it ??<br />
<br />
gowflow
</div>


<div>
Yes it replaced Misc with &quot;&quot;&nbsp;reason being that i only want to process if there is something in Col K if its blank simply do nothing with that row, this resulting in that when it tell how many rows has been transfered it give a correct number and not something i have F!%# to make work.
</div>


<div>
ok last point do you want to 'delete' the rows we processed from Data the one transferred ? or just keep them there ?<br />
gowflow
</div>


<div>
just keep them there, thank you....
</div>


<div>
Works like a Charm thank soooo soooo soooo much!!! and sooo soooo sorry for everything i wish i could give you more points!<br />
<br />
I have learned alot from this experience!! and i will be more precise and clear in the future!
</div>


<div>
If you want to post your merge for Word .. also make sure you put a link in here but PLEASE make sure the text that explains what you want is clear.<br />
<br />
If you want something in Outlook I sure have no problem much easier than Word, Word in VBA is a real Pain !<br />
<br />
Cheers<br />
gowflow
</div>


<div>
<a href="https://www.experts-exchange.com/questions/28565297/VBA-Mail-merge-from-Excel-into-a-word-letter.html" rel="ugc">https://www.experts-exchange.com/questions/28565297/VBA-Mail-merge-from-Excel-into-a-word-letter.html</a>
</div>


<div>
<div class="content wysiwyg-content">
Continue from:<br />
<br />
<a href="https://www.experts-exchange.com/questions/28554985/Excel-Need-help-with-a-macro-that-will-look-at-2-or-more-criteria-and-move-them-to-the-sheet-it-belongs.html?anchorAnswerId=40434922#a40434922" rel="ugc">https://www.experts-exchange.com/questions/28554985/Excel-Need-help-with-a-macro-that-will-look-at-2-or-more-criteria-and-move-them-to-the-sheet-it-belongs.html?anchorAnswerId=40434922#a40434922</a>
</div>
</div>


Continue from:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28554985.html#a40434922

Excel VBA help with calling the sheet diffrently

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.

Microsoft Office

Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.