Excel - Need help with a macro that will look at 2 or more criteria and move them to the sheet it belongs

Hi Again,

Kindly help me out with this issue.

in short what i want is basically a workbook that i need to be able to use for mailmerge later on, for now i need to split the data, so each row end in its respectable sheet. eg

row 1 has a type that is "Big" and a lettertype that is "A1 - Letter1" this is should end in the sheet "Letter A1"
row 2 has a type that is "Small" and a lettertype that is "B1 - Letter1" this should end in the sheet "Letter B1"
 
But row 10 has a type that is "Large" and a lettertype that is "A1 - Letter1" this should also end in the sheet "Letter A1"

I hope this made sense.... otherwise please ask...
example.xlsx
LVL 1
HakumAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KimputerCommented:
For one, your naming scheme kinda sucks.
A1 - Letter 1 goes to Letter A1
Manual Letter goes to Manual Letter

So the first question is already, can't you just keep the lettertype and sheet name the same?
After that, just copy the whole row? (I see 3 duplicate columns anyway)

If so, it's a pretty simple macro.
0
gowflowCommented:
ok here it is. I added in sheet Setup just a conversion of the sheets in Col B.
Press o the button Transfer data in sheet Data and it will distribute the rows in their corresponding sheets.

Check it if all ok then we can delete the rows from sheet data if you want.

here is the code that you will find in the attached workbook.

Option Explicit

Sub TransferData()
Dim WS As Worksheet
Dim WSSetup As Worksheet
Dim WSLetter As Worksheet
Dim MaxRowL As Long
Dim MaxRow As Long, I As Long, lCount As Long
Dim cCell As Range

Set WS = ActiveSheet
Set WSSetup = Sheets("Setup")
MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row

For I = 2 To MaxRow
    Set cCell = WSSetup.UsedRange.Find(What:=WS.Cells(I, "C"), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    If Not cCell Is Nothing Then
        Set WSLetter = Sheets(cCell.Offset(, 1).Value)
        MaxRowL = WSLetter.Range("A" & WSLetter.Rows.Count).End(xlUp).Row + 1
        WSLetter.Cells(MaxRowL, "A") = WS.Cells(I, "E")
        WSLetter.Cells(MaxRowL, "B") = WS.Cells(I, "F")
        WSLetter.Cells(MaxRowL, "C") = WS.Cells(I, "G")
        WSLetter.Cells(MaxRowL, "D") = WS.Cells(I, "A")
        lCount = lCount + 1
    End If
Next I

MsgBox ("A total of " & lCount & " rows were transfered successfully to their coresponding sheets.")

End Sub

Open in new window



gowflow
example.xlsm
0
HakumAuthor Commented:
This rocks!!! Really awesome!

I don't know if it was obvious enough that the workbook i uploaded was not the actual workbook it need to use the code in.. therefore would it possible for you to in short explain me what the code do so i can use it and change the code if need be? i have like 10-15 col's i need to move instead of 4... would it be okay to hide the first 3 col's?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

gowflowCommented:
No sure not !!! I am not fortune teller here.

You post a workbook that is exactly like your in shape and columns (without the confidential information for sure !!!) like same headings if goes on row 2 or 1 or row 8 need all this very important you can change all the data to
dataA dataB DataC ....
DataA1
DataA2
DataA3
...

as long as it is in the same for as you have it there then it is a piece of cake for you or else will be working here for a week back and forth wasting your time and ours.

gowflow
0
HakumAuthor Commented:
I understand... and sorry for the inconvenience....

I have tried to scrambled it in the best was possible.. kindly view
Scramed---11.11.2014.xlsm
0
tomfarrarCommented:
As another option, if the data for putting into rows into other tabs is coming from one column (which I think is the case), you could use the "Show Pages" option of a pivot table which would move the data to separate tabs and put the names on the tabs too.
0
gowflowCommented:
ok but at least at least some descency !!!

1) What are the columns from data that correspond to the sheets that you wish to transfer?
2) what is the column that we suppose to look for Col K ??? presume but it is only filled with data till row 13 what after ?
3) In sheet Setup Can I use Col B ???

gowflow
0
gowflowCommented:
Here it is forget my last comment it seems your coping the whole row. We will only copy rows that have value in Col K.
Activate the button Transfer Data in Sheet Data and check results.
gowflow
Scramed---11.11.2014-V01.xlsm
0
HakumAuthor Commented:
You are Awesome gowflow!

I needed to split the col K into 2, so i did this: and i have tested and it works! :)

Capture.PNG
One finale thing is there a way that the vba could clear the the sheets before running the split? since if the code is run more times there will be multiple data in the sheets.
0
gowflowCommented:
ok here it is clearing sheets prior to process.

BTW what is the formula you need to incorporate ? as presume this was your main and first attempt when you first asked the question (the previous one I meant). If you need help in this once this once is done with I will be glad to assist if you create a new question and lay down all the details of the formula then I can build it for you. Like I noticed you had for Large some times No Letter and sometimes something else .... so this need to be clear in order to build a formula.

gowflow
Scramed---11.11.2014-V02.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HakumAuthor Commented:
this work like a charm and almost as i want, although i need some help with some code that gowflow have made in the earlier sample file.. basically what i need help with is to make it more nice viewable in other words i want the sheet name and the cell name to be able to be different... like this (please view the attached file)

i have made a new case for this:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28555708.html
example-2.1.xlsm
0
gowflowCommented:
Noted and as a comment you need to edit the new question and explain as text what you want and still you can put the link to the old question. but like this you force people to click on this question and go thru the whole threads to understand what you want. It is not advisable.
gowflow
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.