How to Automate data extract frm M'sheet into separate worksheets.

**manz767** · 04-29-2012, 09:20 AM

Hello,

Every mth I generate a Billing Report using a third party provider and export the raw data into a excel spreadsheet to filter/sort and copy/paste, according to criteria "Accounts" into separate worksheets reflecting. Its a very tedious process which is flawed by the manual copy/paste function.

What I'd like to do is when I export the raw data into the Mastersheet worksheet, excel filters the range and exports the data into its respective worksheets according to the criteria being Account number? But I also want the spreadsheet to be dynamic in that when I paste NEW raw data into the Mastersheet, excel will change to reflect it?

Im sure this is possible -unfortunately I do not have the vast experience and knowledge to do this. I have looked at YOUTUBE [where would we be without youtube] but majority of tutorials either just miss out what Im wanting or its too complicated for my little brain to understand!?

Could someone assist?

**davesexcel** · 04-29-2012, 10:22 AM

Sub SendToSheets()
    Dim Rws As Long, Rng As Range, Wks As Worksheet, Sht As Worksheet, fRng As Range

    Set Wks = Worksheets("mastersheet")

    With Wks
        Rws = .Cells(Rows.Count, "D").End(xlUp).Row
        Set Rng = Range(.Cells(1, "A"), .Cells(Rws, "I"))
    End With

    For Each Sht In Sheets

        If Sht.Name <> Wks.Name Then

            Rng.AutoFilter Field:=4, Criteria1:=Sht.Name
            Set fRng = Range(Cells(2, "A"), Cells(Rws, "I"))
            fRng.Copy Destination:=Sht.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

        End If

    Next Sht

    Rng.AutoFilter

End Sub

You may require filtering by dates as well if you want to copy the most recent data

**mrice** · 04-29-2012, 10:29 AM

One way to do this is to recreate the separate sheets whenever they are activated.

Try pasting this code into the ThisWorkbook tab in the VBA editor ALT F11.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.ScreenUpdating = False
If Sh.Name <> "mastersheet" Then
    Sh.Cells.Clear
    Sheets("mastersheet").Rows(1).Copy Destination:=Rows(1)
    For N = 2 To Sheets("mastersheet").Cells(Rows.Count, 4).End(xlUp).Row
        If Trim(Sheets("mastersheet").Cells(N, 4)) = Sh.Name Then
            Sheets("mastersheet").Rows(N).Copy Destination:=Rows(1).Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).EntireRow
        End If
    Next N
End If
Columns.AutoFit
Application.ScreenUpdating = True
End Sub

**manz767** · 05-01-2012, 09:20 AM

Guys, Thank you so much for your example

- it is definitely what I am wanting my worksheet to do and already getting excited what it will do - ie reducing the processing time dramatically and increasing time management for other duties. My only dilemma is actually understanding, for myself, how the formula works, how to reconstruct it [ie the sample i gave you did not include headings on 1st 4 rows on all pges & other worksheets within this report, which by simple equals formula, then populates certain data across as part of 2nd Stage of the monthly report] for it to work correctly in the Monthly Template which, as mentioned contains Headings..

And by the looks of it I will need to make myself proficient in VBA - which i will do. With that said, do you have tutorials yourself that i can read/watch &/or suggest some/someone? And if i was to research further, what IS the correct technical excel term for my query other than automate data extract frm m'sheet into separate worksheet? haha

If it would help i can send you another example of the actual template for your preview?

Again thank you for your valuable time and assistance