Autofilling a Summary Sheet from Multiple Tabs Automatically, By Naming Convention

[JasonPDXOR]

Hello,

I've been searching for an answer for this for a while and still have not found anything. I don't know to explain what I'm looking for exactly so that could be my problem.

Here is what I am looking for:

I have a Workbook with a summary page that looks similar to this: (It will have tabs named by the date)

Day	Date	Routes	Drivers	Shuttles	Total	Profit	Average
Tue	5-1-18	28	2	4	34	$1704	$1704
Wed	5-2-18	26	3	3	32	$1650	$1677
Thu	5-3-18	26	2	2	30	$1689	$1681
Fri	5-4-18

<tbody>
</tbody>

I'm hoping that when I create a tab for 5-3-18 I can have all the date from the new sheet fill in to this summary sheet. The data will not move from cell to cell depending on the tab because they will all have the same format.

For example: If I create a new tab with the name of 5-4-18 all the information that I fill in will automatically populate row five above on my summary tab.

Thank you ahead of time for any help you may provide.

Jason in PDX OR

 

[mumps]

Because I don't know how your newly created sheet will be organized, I have to make some assumptions. Your summary sheet will be named "Summary". I am assuming that your newly created sheet will have the following headers in row 1 of columns A to F.

Routes Drivers Shuttles Total Profit Average

You will enter your data in row 2 under each header and exit the cell after the last entry. The newly created sheet will be named "5-4-18" (no quotes).

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your "5-4-18" 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 the data in row 2 and exit each cell. The "Summary" sheet will be automatically updated. You will have to copy this macro in the same way to each newly created sheet. I tested the macro on the data you posted and it worked properly. It may not work on your actual workbook. It is always easier to help and test possible solutions if we could work with your actual file. Perhaps 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. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A2:F2")) Is Nothing Then Exit Sub
    Dim strdate As String
    strdate = ActiveSheet.Name
    Dim foundDate As Range
    Set foundDate = Sheets("Sheet1").Range("B:B").Find(CDate(strdate), LookIn:=xlFormulas, LookAt:=xlWhole)
    Select Case Target.Column
        Case Is = 1
            foundDate.Offset(0, 1) = Target
        Case Is = 2
            foundDate.Offset(0, 2) = Target
        Case Is = 3
            foundDate.Offset(0, 3) = Target
        Case Is = 4
            foundDate.Offset(0, 4) = Target
        Case Is = 5
            foundDate.Offset(0, 5) = Target
        Case Is = 6
            foundDate.Offset(0, 6) = Target
    End Select
End Sub