Create new tabs with data from main tab

[tchappell]

I have a huge headache of a process in my current role. We have a large spreadsheet of expenses with a column of the properties that they need to be billed back to. I currently have to filter by the property and copy these expenses over into a separate tab (currently around 40-60 tabs), then subtotal based on one of the columns and create a pdf of each tab as an invoice backup sheet. I then have another excel file where I create the invoice cover sheet. It's an extremely manual process. I have at least created a macro when I copy the expenses over I run the macro to subtotal and format the expenses in a keystroke. What I know can be done, but I'm not as advanced with VBA, is how to automatically create a tab for each property and then fill each of those tabs with the data from the main tab. I have to go through this process twice because after the first run, they are reviewed before I actually run a final time with any changes. Any help would be GREATLY appreciated. I have used VBA but it's been pretty minimal. I know this can be done because at my old job someone had done something like this with a big file we had for something different.

 

[mumps]

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).

 

[tchappell]

Here is a shared link to the sheet with example data:

Box | Login

app.box.com

What I do after all reviews of the data is first sort the data in the Compilation tab in 3 levels:
1. Sort by Property (Column H)
2. Sort by Property Description (Column I)
3. Sort by Property Account (Column G)
I then filter by Column H (Property) each Property one at a time and copy the data from Column A through Column K and paste into the corresponding "Property" tab. So I would filter first by Property "43-10", copy the data and paste into the "43-10" tab.
The next step is to subtotal the data in the "43-10" tab, format and then summarize the subtotals at the bottom based on the change in "Property Account". I went ahead and did this in the 43-10 tab to show how the end result would look. It doesn't have to look exactly like this but there needs to be some sort of subtotals of the property account amounts.

If any of this could be automated it would be a huge help. Thank you!

 

[Logit]

Your example file is no longer available for download. ????

 

[mumps]

The link you posted takes me to the Sign-In screen. We need a direct link to the file.

 

[tchappell]

Apologies, please find a new link below, hopefully it works this time:
Test File

 

[mumps]

This macro assumes that your file contains only the sheet "Compilation". Please note that you have some blank Property Accounts in column G. Subtotals for these will not be created.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim i As Long, srcWS As Worksheet, v As Variant, dic As Object
    Set srcWS = Sheets("Compilation")
    v = srcWS.Range("H2", srcWS.Range("H" & Rows.Count).End(xlUp)).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v) To UBound(v)
        If Not dic.exists(v(i, 1)) Then
            dic.Add v(i, 1), Nothing
            With srcWS
                .Range("A1").AutoFilter 8, v(i, 1)
                If Not Evaluate("isref('" & v(i, 1) & "'!A1)") Then
                    Sheets.Add(After:=Sheets(Sheets.Count)).Name = v(i, 1)
                    .AutoFilter.Range.Copy Range("A1")
                    Cells(1, 1).Sort Key1:=Columns(7), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
                    Range("G1").Subtotal GroupBy:=7, Function:=xlSum, TotalList:=Array(3), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
                    ActiveSheet.Columns.AutoFit
                End If
            End With
        End If
    Next i
    srcWS.Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub