Automatically copy entire rows from one Master excel tab to another sheet

[Will_Xcel]

Hi everyone,

I would highly appreciate if you could help me with the following Excel requirement I have:

I have a database of companies which will constantly be updated (row by row) in a sheet called Master Tab.

Starting with Row 2, the database will be populated with information related to my companies. Specifically, Column C represents the Type of company, as text (e.g. Software, Manufacturer). I will have many such types.

In addition to my Master Tab, I will have other sheets for each type of company (e.g. there will be a sheet called Software, Manufacturer, etc.).

I need each of those other sheets to be automatically populated with rows from the Master Tab based on their respective Company types.

Is there any way to do that automatically as I include new rows in Master Tab?

Many thanks!

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your "Master Tab" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter all the data in all the columns except in column C. Enter the company type in Column C last and exit the cell. Make sure that you have sheets that correspond to every company type and named exactly as you would enter then name in column C.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Target.EntireRow.Copy Sheets(Target.Value).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Application.ScreenUpdating = True
End Sub

 

[Will_Xcel]

This works great, thank you very much! Could I kindly ask you for something similar - I now have my criteria in column Z instead of column C, and information up to column X. However, this time I only want to copy the rows up to column F. Everything else is the same (i.e. I want the rows to be copied onto a separate sheet named after column Z criteria, which I will have created beforehand).

Many thanks!

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("Z:Z")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Range("A" & Target.Row).Resize(, 7).Copy Sheets(Target.Value).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Application.ScreenUpdating = True
End Sub

Make sure you enter the company type in Column Z last and exit the cell.

 

[Clmartin]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your "Master Tab" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter all the data in all the columns except in column C. Enter the company type in Column C last and exit the cell. Make sure that you have sheets that correspond to every company type and named exactly as you would enter then name in column C.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Target.EntireRow.Copy Sheets(Target.Value).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Application.ScreenUpdating = True
End Sub

OK, this does work great, but to a point. When it comes to populating Column C, in the example above, it is forcing me to update it one rows at a time! I have over 12,000 rows to populate, I can't do that one row at a time. I receive an '13' Mismatch Type error when I try to 'fill' Column C by copying more than one cell/row. Even trying to fill 2 cells/rows Column C returns the '13'. TIA, Curtis

 

[mumps]

This should work but if you want to copy over 12,000 rows at one time, it might be slow.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In Target
        rng.EntireRow.Copy Sheets(Target.Value).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Next rng
    Application.ScreenUpdating = True
End Sub

There may be a faster way to do what you want. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet.
Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).