Solved

Excel VBA help with calling the sheet diffrently

Posted on 2014-11-12
31
84 Views
Last Modified: 2014-11-19
0
Comment
Question by:Hakum
  • 16
  • 15
31 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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.
gowflow
0
 
LVL 1

Author Comment

by:Hakum
Comment Utility
Understood, what i want to achieve is this:

I want that the lettertype and sheet name able to be different like this:
Capture.PNG this is a screenshot from "example_2.1.xlsm"

I want the changes to in this workbook: "Scramed---12.11.2014-V03.xlsm"

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.
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.

( yes i know i'm not good in excel...sorry)


I hope this made sense...
Scramed---12.11.2014-V03.xlsm
example-2.1.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Sorry I still don't understand what you want:

In sheet Data
You have Col K that Is a formula from ColL and M
Col L is a dropdown from Sheet Setup Col F
Col M is a dropdown from Sheet Setup Col E

So what is needed then ?
Sorry sometimes I am slow !
gowflow
0
 
LVL 1

Author Comment

by:Hakum
Comment Utility
No worries. i think its me that is not able to explain myself , ill try even better:)

What i need is be able to is make Col L & col M into one text/string in col K

Like this:
Capture.PNG
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 "A1 - Letter1" it should transfer the data into sheet: "Letter A1", so the data in col K and the sheet name does not need to be the same... just like you made in the "example_2.1.xlsm" file...

What i have done is that i made a formula that does this:

Capture.PNG
And joins the textstring into one string and this row will now be transfered into the sheet called: "Contact Letter A"


make sense?
0
 
LVL 1

Author Comment

by:Hakum
Comment Utility
The formula i made insert a space(" ") meaning that i was need to make a new sheet called " " for the code to work since some rows need to be blank.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Still not clear what you want from me.

Please state what is the problem and what you need me to do.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Having read your post several times and having done a thinking I believe I maybe 'discover' what you are trying to do.

Let me explain what you want to do and propose my solution.

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.

Please advise if this is correct.
gowflow
0
 
LVL 1

Author Comment

by:Hakum
Comment Utility
Okay,

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 ="ContactLetter A" it should transfer this row into the sheet called "Letter A1"

In otherwords

If Col K in a row in the data sheet is "Contact Letter A" i still want it to be able to transfer the entire row to sheet "Letter A1"
Or another example, if its Col K in a row in the data sheet is " No Contact Letter A" I still want it to be able to transfer the entire row to "Letter A2"

and so on..

This is why:

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.

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.

The above mentioned processes are manually done by people,

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).


I think you made this in the sample file you provided the first time, but i might be wrong...
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
WEll I noted the following:


"ContactLetter A" it should transfer this row into the sheet called "Letter A1"
" No Contact Letter A" I still want it to be able to transfer the entire row to "Letter A2"

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.

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.

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.

gowflow
0
 
LVL 1

Author Comment

by:Hakum
Comment Utility
First of all I'm sooo sorry for confusing you more then before! and thank you for your patience!!!! really appreciated!!!!

YES!!! that's what i want!!! a simple translation in the setup sheet! :D how stupid of me not to say it that ways!

Nothing is wrong with the macro! and the sex of angels sound nice though but don't think its needed!
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
To make your life and my life easier please do the following:

1) Make a copy of the workbook you just send me here Scramed---12.11.2014-V03.xlsm and call it Scramed---12.11.2014-V04.xlsm
2) Open Scramed---12.11.2014-V04.xlsm
3) Put this formula in sheet Data cell K2
=IF(AND(L2="",M2=""),"Misc",TRIM(L2&" "&M2))
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.

Now what you have is the following:
1) If you have Col that have blank it will remove the first trailing space so you have 'Manuel Letter' instead of having
 ' Manuel Letter' note the first blank space.
2) If both cells L and M are blank you will have Misc or we can put whatever you want there like others etc...

NOW
My intention was:
1) Delete all the sheets you already created and keep only 1 hidden for me for the macro.
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:
      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.
      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

etc.... till end of data.

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

Please let me know.
If I am offtrack I appreciate you simply explain in plain English.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
uff more lost here !!!

THEN WHAT IS WRONG ?????

WHY YOU POSTED THIS QUESTION ?

gowflow
0
 
LVL 1

Author Comment

by:Hakum
Comment Utility
Sorry :(

What is wrong that it does not translate from the setup sheet anymore..
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
FINALLY !

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 !!

Did you try what I suggest before the new formula and the way I proposed ?
gowflow
0
 
LVL 1

Author Comment

by:Hakum
Comment Utility
You can use the Col B in the setup sheet yes!
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):

"Contact Letter A" translate to "Letter A1"
"No Contact Letter A" translate to "Letter A2"
"Contact Letter B" translate to "Letter B1"
"No Contact Letter B" translate to "Letter B2"
"Manuel Letter" translate to "Manuel Letter"
"No Letter" translate to "No Letter"

I did insert the formula you wrote and it works like a charm, thank you!

Here is the workbook with what you have told me to do.
Scramed---12.11.2014-V04.xlsm
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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:

"Contact Letter A" translate to "Letter A1"
"No Contact Letter A" translate to "Letter A2"
"Contact Letter B" translate to "Letter B1"
"No Contact Letter B" translate to "Letter B2"
"Manuel Letter" translate to "Manuel Letter"
"No Letter" translate to "No Letter"

Now we have more:
YOu have sheets:
Contact Letter A
No Contact Letter A
Contact Letter B
No Contact LetterB
Manuel Letter
No Letter

Shouldn't these sheets be renamed to:
Letter A1
LetterA2
Letter B1
Letter B2
Manuel Letter
No Letter

????
pls advise gowflow
0
 
LVL 1

Author Comment

by:Hakum
Comment Utility
Sorry Gowflow!!! i really appreciate you being soo patient and cool minded! i owe you big time!! so thank you and its noted!

Yes please rename them, to streamline it, its my fault i made a few changes so forgot to change it in the newest workbook
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok last but not least !
Shall we go on what I proposed ? or we keep it fix ?

ie
I mean by that delete all the sheets and create as we go along ... ?
gowflow
0
 
LVL 1

Author Comment

by:Hakum
Comment Utility
if it checks the translation then yes,
0
 
LVL 1

Author Comment

by:Hakum
Comment Utility
No sorry! please keep it fixed! as it is now! since i will be need to mode code in the fixed sheets!
0
 
LVL 1

Author Comment

by:Hakum
Comment Utility
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 :)
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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.

Let me concentrate on getting this one correctly.
Will revert shortly..
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
You have removed in the formula
Misc and replaced it by ""

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.

What is it ??

gowflow
0
 
LVL 1

Author Comment

by:Hakum
Comment Utility
Yes it replaced Misc with "" 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.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok last point do you want to 'delete' the rows we processed from Data the one transferred ? or just keep them there ?
gowflow
0
 
LVL 1

Author Comment

by:Hakum
Comment Utility
just keep them there, thank you....
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
Here it is

Let me know
gowflow
Scramed---12.11.2014-V04.xlsm
0
 
LVL 1

Author Comment

by:Hakum
Comment Utility
Works like a Charm thank soooo soooo soooo much!!! and sooo soooo sorry for everything i wish i could give you more points!

I have learned alot from this experience!! and i will be more precise and clear in the future!
0
 
LVL 1

Author Closing Comment

by:Hakum
Comment Utility
Gowflow you rock!!!
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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.

If you want something in Outlook I sure have no problem much easier than Word, Word in VBA is a real Pain !

Cheers
gowflow
0
 
LVL 1

Author Comment

by:Hakum
Comment Utility
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now