Avatar of Harsh Kumar
Harsh Kumar
Flag for Denmark asked on

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
Microsoft ExcelMicrosoft OfficeVisual Basic Classic

Avatar of undefined
Last Comment
gowflow

8/22/2022 - Mon
Kimputer

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

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
Harsh Kumar

ASKER
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?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
gowflow

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
Harsh Kumar

ASKER
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 Farrar

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
gowflow

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
gowflow

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
Harsh Kumar

ASKER
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
gowflow

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Harsh Kumar

ASKER
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
gowflow

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