Question about finding types of data and copying them... I hope someone can make my day helping me with this :) thanks!!!

[gorhamsj]

Hi
I have a question about doing vlookup or something

I have categrories like this:

N° DOC	Type	Date	Type of Receipt	Number Receipt	Social Reason	Concept
1	Sales
1	Small Cash
1	Taxes

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

I have tabs that says Sales, Small Cash, Taxes..

What can I put on the Sales sheet which esentially takes all of the information from the ones labled "sales"
So it could be like vlookup, sales... then I can copy it accross and it shows all data on sales page without spaces in between. Is there a formula which can take all this data?
Thanks

 

[mumps]

Try this macro. It assumes that the 3 sheets Sales, Small Cash, Taxes already exist and that the sheet that contains all your data is called "Master". Change the sheet names to suit your situation.

Sub CopyRows()
    Application.ScreenUpdating = False
    Dim bottomB As Long
    bottomB = Sheets("Master").Range("B" & Rows.Count).End(xlUp).Row
    Dim rng As Range
    Dim ws As Worksheet
    For Each ws In Sheets
        If ws.Name <> "Sheet1" Then
            Sheets("Master").Rows(1).Copy Sheets(ws.Name).Rows(1)
            ws.UsedRange.Offset(1, 0).ClearContents
            For Each rng In Sheets("Master").Range("B2:B" & bottomB)
                If ws.Name = rng Then
                    rng.EntireRow.Copy Sheets(ws.Name).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                End If
            Next rng
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[gorhamsj]

Thanks a bunch!! Sorry I am new to excel, would you mind teaching me 2 basic things
1. Where do I put the macro?
2. If I want to add any extra categories in the future on (sales, small cash) and categories such as (date, concept) what do I do to the macro?
Thanks for all of your help

 

[mumps]

Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key. The macro assumes that the categories are in column B. You can add as many different categories to column B as you like in the future. As long as you create a new sheet to match that category name before you run the macro, you don't have to do anything to the macro. It will continue to work properly. Keep in mind that macros are very picky and sheet names must match exactly the category names and in this case, you must have a "Master" sheet with all your original data. Please let me know how it works out.

 

[gorhamsj]

Thanks Mumps!! Apprecaite the help! hope weather is well in Canada!! I will try this right after lunch and will respond! really appreciate the help! you made my day!

 

[gorhamsj]

Hi i tried it.. it said all macros were disabled... so then.. I activated the in the trust center.. I even saved the document as a macro enebled worksheet but it stilll didn't work...
All of the code boxes were named solver table.. didn't know why..
i will try and figure out what the problem is.
thanks again for your help