Automatic new date tabs?

S

Sunshine8790

Board Regular
Joined
Jun 1, 2021
Messages
86
Office Version
  1. 365
Platform
  1. Windows
This is sort of a double question:

I have this workbook, and I need to download the data for the data sheet once a day, and copy and paste over existing data in the Data tab.
I also need the Data to be separated out into separate tabs per individual dates based on the dates in Column A.
How do I create all these tabs, or make excel automatically create them, and get the data to automatically go to the right tab?

Too much data apparently to put all in a mini sheet, but here's some and a screenshot:


DNRDailyLog.xlsm
ABCDEFGHIJKLMNOPQRSTUVWX
1DateWarehouseChannelShip ModePOShipmentBrandCarrierDCK_CMMTIn YardStatusShiftUserDck PODck UnitsDck CaseDck SKUDNRExpected UnitsExpected CaseRcvd UnitsRcvd CaseRcvd SKUPACKING_LIST_NO
26/18/2021GFCDIRECTAIR0003367176102023196ANTGT 1530lp-DCKD_NOT_RCVD1st ShiftLPENAGD1744391574474439000102023196
36/18/2021GFCDIRECTAIR0003367176102023196ANTGT 1530lp-DCKD_NOT_RCVD1st ShiftLPENAGD1744391574474439000102030497
46/18/2021GFCDIRECTAIR0003367176102023196ANTGT 1530lp-DCKD_NOT_RCVD1st ShiftLPENAGD1744391574474439000102030508
56/18/2021GFCDIRECTAIR0003367176102023196ANTGT 1530lp-DCKD_NOT_RCVD1st ShiftLPENAGD1744391574474439000102036420
66/18/2021GFCDIRECTROUTING GUIDE0003390458000086611UONAvm-DCKD_NOT_RCVD1st ShiftJBATISTAGD14714550000102033056
76/20/2021GFCDIRECTOCN0003237155000086625ANNAMHF44300DCKD_NOT_RCVD1st ShiftMHERNA114G13636136360000101954910
86/20/2021GFCDIRECTOCN0003237155000086625ANNAMHF-DCKD_NOT_RCVD1st ShiftMHERNA114G13636136360000101954910
96/22/2021GFCDIRECTROUTING GUIDE0003170877102049644UOSCHNIEDERwb-DCKD_NOT_RCVD2nd ShiftMKEENGD1941194941000102049644
106/22/2021GFCDIRECTROUTING GUIDE0003170877102049644UOSCHNIEDERwb-DCKD_NOT_RCVD2nd ShiftMKEENGD1941194941000011130033673215630
116/22/2021GFCDIRECTROUTING GUIDE0003170877102049644UOSCHNIEDERwb-DCKD_NOT_RCVD2nd ShiftMKEENGD1941194941000-
126/22/2021GFCDIRECTROUTING GUIDE0003301674000086661ANNAMHF-DCKD_NOT_RCVD1st ShiftMHERNA114G16666266660000102020856
136/22/2021GFCDIRECTROUTING GUIDE0003482371102052899UOUPSGRH-DCKD_NOT_RCVD1st ShiftRHUNTER14G12412424241000102052899
146/22/2021GFCDIRECTROUTING GUIDE0003515807102050920UOUPSGRH-DCKD_NOT_RCVD1st ShiftBHOFFMA14G1761176761000102050920
156/23/2021GFCDIRECTROUTING GUIDE0003102405000086703UOUPSwb-DCKD_NOT_RCVD2nd ShiftWBECHTELGD1125512120000102048760
166/23/2021GFCDIRECTROUTING GUIDE0003170877673215630UONA-DCKD_NOT_RCVD1st ShiftCSANTI314G1621194941000102049644
176/23/2021GFCDIRECTROUTING GUIDE0003170877673215630UONA-DCKD_NOT_RCVD1st ShiftCSANTI314G1621194941000011130033673215630
186/23/2021GFCDIRECTROUTING GUIDE0003170877673215630UONA-DCKD_NOT_RCVD1st ShiftCSANTI314G1621194941000-
196/23/2021GFCDIRECTROUTING GUIDE0003170895673295632UONA-DCKD_NOT_RCVD1st ShiftCSANTI314G1621162621000102049668
206/23/2021GFCDIRECTROUTING GUIDE0003170895673295632UONA-DCKD_NOT_RCVD1st ShiftCSANTI314G1621162621000011130033673295632
Data
 

Attachments

  • Dataimage.png
    Dataimage.png
    179.5 KB · Views: 13

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Hello Sunshine,

Try the following code placed in a standard module and assigned to a button:-

VBA Code: 
Sub Sunshine()

        Dim sht As Worksheet, ws As Worksheet, lr As Long, i As Long
        Dim DtID As Object, key As Variant
    
        Set sht = Sheets("Data")
        Set DtID = CreateObject("Scripting.Dictionary")
        lr = sht.Range("A" & Rows.Count).End(xlUp).Row
    
Application.ScreenUpdating = False
Application.DisplayAlerts = False
        
        For i = 2 To lr
              If Not DtID.Exists(sht.Range("A" & i).Value) Then
              DtID.Add sht.Range("A" & i).Value, 1
              End If
        Next i
        
        For Each key In DtID.keys
              If Not Evaluate("ISREF('" & key & "'!A1)") Then
              Worksheets.Add(After:=Sheets(Sheets.Count)).Name = key
        End If
        
        Set ws = Sheets(key)
        ws.UsedRange.Clear
        
        With sht.Range("A1:A" & lr)
              .AutoFilter 1, key
              .Resize(, 24).Copy ws.[A1]
              .AutoFilter
        End With
              ws.Columns.AutoFit
        Next key

sht.Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "All done!", vbExclamation

End Sub

Please note that you'll need to re-format the dates in Column A from using forward slashes(/) to hyphens(-) or dots(.). Excel will not allow forward slashes in tab names. They are reserved characters and are deemed illegal for tab names.

I hope that this helps.

Cheerio,
vcoolio.
 
Upvote 0
vcoolio said:
Hello Sunshine,

Try the following code placed in a standard module and assigned to a button:-

VBA Code: 
Sub Sunshine()

        Dim sht As Worksheet, ws As Worksheet, lr As Long, i As Long
        Dim DtID As Object, key As Variant
  
        Set sht = Sheets("Data")
        Set DtID = CreateObject("Scripting.Dictionary")
        lr = sht.Range("A" & Rows.Count).End(xlUp).Row
  
Application.ScreenUpdating = False
Application.DisplayAlerts = False
      
        For i = 2 To lr
              If Not DtID.Exists(sht.Range("A" & i).Value) Then
              DtID.Add sht.Range("A" & i).Value, 1
              End If
        Next i
      
        For Each key In DtID.keys
              If Not Evaluate("ISREF('" & key & "'!A1)") Then
              Worksheets.Add(After:=Sheets(Sheets.Count)).Name = key
        End If
      
        Set ws = Sheets(key)
        ws.UsedRange.Clear
      
        With sht.Range("A1:A" & lr)
              .AutoFilter 1, key
              .Resize(, 24).Copy ws.[A1]
              .AutoFilter
        End With
              ws.Columns.AutoFit
        Next key

sht.Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "All done!", vbExclamation

End Sub

Please note that you'll need to re-format the dates in Column A from using forward slashes(/) to hyphens(-) or dots(.). Excel will not allow forward slashes in tab names. They are reserved characters and are deemed illegal for tab names.

I hope that this helps.

Cheerio,
vcoolio.
Click to expand...
Thanks, but that did not work - and I re-formatted before hand to spaces.

I found a code that worked to create a date tab for each day of the year by creating a new tab named "Dates" and filling out Column A with all the dates I need for the year up to 12/31/21.
It automatically created the tabs for me.

Code I used:
VBA Code: 
Sub SplitMasterByDate()
    'Assumes
    '  a worksheet named master with dates in column E
    '    with data & headers starting at A1 and no blank rows
    '  a worksheet named Dates with dates in column A, starting with A1
    '    and down with no blanks.  These dates will determine
    '    data worksheet names and the rows moved to those worksheets
    '  No other worksheets (with names that conflict with 'Dates' worksheet's) dates
    'Worksheets will be created with a name like yyyy mm dd for each of the dates
    '  on the 'Dates' worksheet
   
    Dim lDatesLastRow As Long
    Dim sName As String
    Dim rngCell As Range
   
    Worksheets("Master").AutoFilterMode = False
    For Each rngCell In Worksheets("dates").Range("A1").CurrentRegion.Cells
        sName = Format(rngCell.Value, "mm dd yy")
        Worksheets.Add(after:=Sheets(Sheets.Count)).Name = sName
        Worksheets("Master").Range("A1").CurrentRegion.AutoFilter _
            Field:=5, Criteria1:=">=" & DateSerial(Year(rngCell), Month(rngCell), Day(rngCell)), Operator:=xlAnd
        Worksheets("Master").Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy _
            Destination:=Worksheets(sName).Range("A1")
    Next
     Worksheets("Master").AutoFilterMode = False
    
End Sub


Now my question is how do make the data from the Master (Data) tab separate to the tabs based on the date?
 
Upvote 0
Hello Sunshine,

I've attached a sample file based on the information you supplied in your opening post.
You'll find the file here.

Run the code and you'll note that new sheets are created for each unique date in Column A and the relevant rows of data are transferred to the relevant sheets. I've removed forward slashes and added hyphens in the dates. Spaces, if you prefer to use them instead, will not cause a problem.

Cheerio,
vcoolio.
 
Upvote 0
vcoolio said:
Hello Sunshine,

I've attached a sample file based on the information you supplied in your opening post.
You'll find the file here.

Run the code and you'll note that new sheets are created for each unique date in Column A and the relevant rows of data are transferred to the relevant sheets. I've removed forward slashes and added hyphens in the dates. Spaces, if you prefer to use them instead, will not cause a problem.

Cheerio,
vcoolio.
Click to expand...
It worked initially, but after I pasted new data into the Data tab, I get this error when trying to run the code (I made a button for the macro):
1631881977300.png

When I hit "Debug" I see this:

1631882019435.png


Help.
 
Upvote 0
It sounds like it is trying to create a worksheet with a name that is not valid.
You can see what this is by placing the line of code above the highlighted line of code, and see what the Message Box returns just before the error happens:
VBA Code: 
MsgBox key
 
Upvote 0
Joe4 said:
It sounds like it is trying to create a worksheet with a name that is not valid.
You can see what this is by placing the line of code above the highlighted line of code, and see what the Message Box returns just before the error happens:
VBA Code: 
MsgBox key
Click to expand...
Wait, what? I'm not quite clear what I'm supposed to do with the "MsgBox Key" code you just posted ?
Sorry, I'm still kind of a beginner with macros and codes.
 
Upvote 0
Sunshine8790 said:
Wait, what? I'm not quite clear what I'm supposed to do with the "MsgBox Key" code you just posted ?
Sorry, I'm still kind of a beginner with macros and codes.
Click to expand...
See the line highlighted in yellow in the screen print your posted?
Add this new line of code, just above that line.
Then re-run your code, and note the Message Box statements that pop-up, and note the LAST one before you get the error.
This will show the worksheet name that you are trying to create that it doesn't like.
Let us know the last value the Message Box is returning before you get the error.
 
Upvote 0
Joe4 said:
See the line highlighted in yellow in the screen print your posted?
Add this new line of code, just above that line.
Then re-run your code, and note the Message Box statements that pop-up, and note the LAST one before you get the error.
This will show the worksheet name that you are trying to create that it doesn't like.
Let us know the last value the Message Box is returning before you get the error.
Click to expand...
Hi Joe,
Thank you for clearing that up. Did as you said, and the message box is returning: 9/14/2021.

1631888167462.png
 
Upvote 0
You cannot use "/" in the name of your worksheet tabs. They are not valid characters.
Try changing this line:
VBA Code: 
Worksheets.Add(after:=Sheets(Sheets.Count)).Name = key
to
VBA Code: 
Worksheets.Add(after:=Sheets(Sheets.Count)).Name = Format(key,"m-d-yyyy")
which will instead name the sheets like "9-17-2021".
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question Question
Breaking down some dates using a formula
2
Replies
13
Views
539
zelarra
Z
R
  • Question Question
Formatting of Bio-metric attendence csv file
Replies
1
Views
249
awoohaw
awoohaw
G
  • Locked
  • Question Question
VBA Userform - Change from 1900 Date Format to Long Date
Replies
1
Views
58
Peter_SSs
Peter_SSs
G
  • Question Question
VBA Userform Date Format
Replies
7
Views
184
gilly01625
G
D
  • Solved
Multiple answer for particular timesets and making a box if all relevant data matches
Replies
7
Views
160
myall_blues
myall_blues

Forum statistics

Threads
1,223,909
Messages
6,175,313
Members
452,634
Latest member
cpostell

Share this page

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
Back
Top