Link to home
Create AccountLog in
Avatar of Harsh Kumar
Harsh KumarFlag 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
Avatar of Kimputer
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.
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
Avatar of 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?
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
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
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.
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
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
You are Awesome gowflow!

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

User generated image
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.
ASKER CERTIFIED SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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/questions/28555708/Excel-VBA-help-with-calling-the-sheet-diffrently.html
example-2.1.xlsm
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