Solved

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

Posted on 2014-11-11
12
126 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
0
Comment
Question by:Hakum
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 36

Expert Comment

by:Kimputer
ID: 40434829
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
 
LVL 30

Expert Comment

by:gowflow
ID: 40434871
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
 
LVL 1

Author Comment

by:Hakum
ID: 40434910
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Expert Comment

by:gowflow
ID: 40434922
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
 
LVL 1

Author Comment

by:Hakum
ID: 40435076
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
 
LVL 7

Expert Comment

by:tomfarrar
ID: 40435481
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
 
LVL 30

Expert Comment

by:gowflow
ID: 40435530
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
 
LVL 30

Expert Comment

by:gowflow
ID: 40435735
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
 
LVL 1

Author Comment

by:Hakum
ID: 40436811
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
 
LVL 30

Accepted Solution

by:
gowflow earned 500 total points
ID: 40436858
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
 
LVL 1

Author Comment

by:Hakum
ID: 40436897
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
 
LVL 30

Expert Comment

by:gowflow
ID: 40436919
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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

717 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