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

gorhamsj

Board Regular
Joined
Dec 14, 2012
Messages
182
Hi
I have a question about doing vlookup or something :)

I have categrories like this:

N° DOC TypeDate Type of ReceiptNumber ReceiptSocial ReasonConcept
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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,108
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.
Code:
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

Board Regular
Joined
Dec 14, 2012
Messages
182
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,108
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

Board Regular
Joined
Dec 14, 2012
Messages
182
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

Board Regular
Joined
Dec 14, 2012
Messages
182
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,463
Messages
5,596,283
Members
414,051
Latest member
tabecker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top