We help IT Professionals succeed at work.

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

195 Views
Last Modified: 2014-11-12
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
Comment
Watch Question

KimputerIT Manager
CERTIFIED EXPERT

Commented:
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.
gowflowPartner
CERTIFIED EXPERT

Commented:
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

Author

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?
gowflowPartner
CERTIFIED EXPERT

Commented:
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

Author

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
Tom FarrarConsultant
CERTIFIED EXPERT

Commented:
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.
gowflowPartner
CERTIFIED EXPERT

Commented:
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
gowflowPartner
CERTIFIED EXPERT

Commented:
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

Author

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.
Partner
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

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:

https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28555708.html
example-2.1.xlsm
gowflowPartner
CERTIFIED EXPERT

Commented:
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.