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
Sort by date Sort by votes
Unmarking this as solved as problems have developed.

So as a refresher - this file is a Sharepoint excel file. Macro buttons do not work on Sharepoint, so in order to run these macros we must open it in desktop version.
Once our daily report is run, we delete all the data in our data tab and copy and paste the new data in there.
Then we go to my "Control Center" tab and press the 2 macro buttons.
The macro buttons delete the unnecessary columns of data, reformat the dates and font style, create new automatic tabs, all that.
Because this is supposed to save time and make things simple.

HOWEVER - I ended up editing the original script I got here in an attempt to tweak new changes.
The change is problematic. We needed to manually add a new column for "Comments" to keep track of our data each day.
But here's the catch: We need to be able to add these comments on either version (desktop or sharepoint) if possible. Either way, we're finding that the comments added are disappearing after the new data is applied with the 2 macro buttons.

Example BEFORE running macros:

1634819988752.png
1634820005051.png


Same tabs AFTER running:
1634820052031.png

1634820071533.png


Coding for first macro button:
VBA Code: 
Sub Edit()
'
' Edit Macro
'

'
    Sheets("Data").Select
    ActiveWindow.ScrollColumn = 1
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "[$-en-US]d-mmm-yy;@"
    Columns("J:J").Select
    Selection.Delete Shift:=x1ToLeft
    Columns("D:D").Select
    Selection.Delete Shift:=x1ToLeft
    Columns("B:X").Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Columns("X:X").Select
    Columns("X:X").EntireColumn.AutoFit
    Range("Y1").Select
    Selection.NumberFormat = "#,##0"
    ActiveCell.FormulaR1C1 = "Comments"
    Range("Y1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Font.Bold = True
    Columns("Y:Y").Select
    Selection.ColumnWidth = 41.88
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("Y2").Select
    Range("X15").Select
    ActiveWindow.SmallScroll Down:=-6
    
End Sub

Coding for 2nd Macro 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
        
        sht.Columns("A").Replace What:="/", Replacement:="."  'Additional line of code.
        
        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(, 25).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

How the tabs are setup:
1634820184534.png


Mini Sheet from Control center with macro buttons:
This is the sheet we need to delete and paste new data onto daily.
Comments are not actually entered on this tab.

GFCDNRDailyLog.xlsm
ABCDEFGHI
1Qlik Report (Click here)<--Press Me first.
2
3
4
5<--Press me 2nd, after new data is pasted.
6
7
8<--Press me 3rd.
9
10
11
12
13
14
15
16
Control Center


Mini Sheet (Small selection, too big to do the whole thing) from Data tab with current data:

GFCDNRDailyLog.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXY
1DateWarehouseChannelShip ModePOShipmentBrandCarrierDock_StatusIn YardStatusShiftUserDck PODck UnitsDck CaseDck SKUDNRExpected UnitsExpected CaseRcvd UnitsRcvd CaseRcvd SKUPACKING_LIST_NOComments
27.23.2021GFCWHOLESALE-000053116700005311670102068328UOW90-DCKD_NOT_RCVD1st ShiftLUSANTI14G1860186860000102068328
37.23.2021GFCDIRECTAIR0003423921102029335FPTGT 145790-DCKD_NOT_RCVD1st ShiftLPENAGD12,04140152,0412,04140000102029335
47.23.2021GFCDIRECTAIR0003423921102029335FPTGT 145790-DCKD_NOT_RCVD1st ShiftLPENAGD12,04140152,0412,04140000102030446
57.26.2021GFCDIRECTROUTING GUIDE0003540746292226845UOUPS90-DCKD_NOT_RCVD2nd ShiftWBECHTELGD1120311201203000102078681
67.26.2021GFCDIRECTROUTING GUIDE0003540746292226845UOUPS90-DCKD_NOT_RCVD2nd ShiftWBECHTELGD112031120120300060629292226845
77.28.2021GFCDIRECTROUTING GUIDE0003428219010483130UOUPS90-DCKD_NOT_RCVD2nd ShiftSTHANGGD15011505010002041503900010483119
87.28.2021GFCDIRECTROUTING GUIDE0003428219010483130UOUPS90-DCKD_NOT_RCVD2nd ShiftSTHANGGD15011505010002041503900010483120
97.28.2021GFCDIRECTROUTING GUIDE0003428219010483130UOUPS90-DCKD_NOT_RCVD2nd ShiftSTHANGGD15011505010002041503900010483121
107.28.2021GFCDIRECTROUTING GUIDE0003428219010483130UOUPS90-DCKD_NOT_RCVD2nd ShiftSTHANGGD15011505010002041503900010483122
117.28.2021GFCDIRECTROUTING GUIDE0003428219010483130UOUPS90-DCKD_NOT_RCVD2nd ShiftSTHANGGD15011505010002041503900010483123
127.28.2021GFCDIRECTROUTING GUIDE0003428219010483130UOUPS90-DCKD_NOT_RCVD2nd ShiftSTHANGGD15011505010002041503900010483124
137.28.2021GFCDIRECTROUTING GUIDE0003428219010483130UOUPS90-DCKD_NOT_RCVD2nd ShiftSTHANGGD15011505010002041503900010483125
147.28.2021GFCDIRECTROUTING GUIDE0003428219010483130UOUPS90-DCKD_NOT_RCVD2nd ShiftSTHANGGD15011505010002041503900010483126
157.28.2021GFCDIRECTROUTING GUIDE0003428219010483130UOUPS90-DCKD_NOT_RCVD2nd ShiftSTHANGGD15011505010002041503900010483127
167.28.2021GFCDIRECTROUTING GUIDE0003428219010483130UOUPS90-DCKD_NOT_RCVD2nd ShiftSTHANGGD15011505010002041503900010483128
177.28.2021GFCDIRECTROUTING GUIDE0003428219010483130UOUPS90-DCKD_NOT_RCVD2nd ShiftSTHANGGD15011505010002041503900010483129
187.28.2021GFCDIRECTROUTING GUIDE0003428219010483130UOUPS90-DCKD_NOT_RCVD2nd ShiftSTHANGGD15011505010002041503900010483130
197.28.2021GFCDIRECTROUTING GUIDE0003536744000087259FPNA90-DCKD_NOT_RCVD2nd ShiftDDAVISGD1511110000102086392
207.28.2021GFCDIRECTROUTING GUIDE0003536744000087259FPNA90-DCKD_NOT_RCVD2nd ShiftDDAVISGD1511110000-
217.29.2021GFCDIRECTROUTING GUIDE0003173657102078432UONA90-DCKD_NOT_RCVD1st ShiftERODRIG6GD1197211271272000102078432
227.29.2021GFCDIRECTROUTING GUIDE0003173657102078432UONA90-DCKD_NOT_RCVD1st ShiftERODRIG6GD1197211271272000011130034034523012
238.1.2021GFCDIRECTROUTING GUIDE0003295943288941319ANNA90-DCKD_NOT_RCVD1st ShiftJHICKS14G120813120820813000102081072
248.1.2021GFCDIRECTROUTING GUIDE0003295943288941319ANNA90-DCKD_NOT_RCVD1st ShiftJHICKS14G12081312082081300020777074720008288941319
258.1.2021GFCDIRECTROUTING GUIDE0003295943288941319ANNA90-DCKD_NOT_RCVD1st ShiftMHERNA114G120813120820813000102081072
268.1.2021GFCDIRECTROUTING GUIDE0003295943288941319ANNA90-DCKD_NOT_RCVD1st ShiftMHERNA114G12081312082081300020777074720008288941319
278.2.2021GFCDIRECTROUTING GUIDE0003511532102084554ANFEDEX90-DCKD_NOT_RCVD2nd ShiftJHICKS14G1189861951958000102084554
288.2.2021GFCDIRECTROUTING GUIDE0003511532102084554ANFEDEX90-DCKD_NOT_RCVD2nd ShiftJHICKS14G1189861951958000102088823
298.3.2021GFCDIRECTOCN0003467766000087326ANFEDEX90-DCKD_NOT_RCVD2nd ShiftJHICKS14G1325213253250000102102643
308.4.2021GFCDIRECTOCN0003350199102012339FPGILBERT 7280490-DCKD_NOT_RCVD1st ShiftLPENAGD1106621041046000102012339
318.4.2021GFCDIRECTOCN0003350199102012339FPGILBERT 7280490-DCKD_NOT_RCVD1st ShiftLPENAGD1106621041046000102038507
328.4.2021GFCDIRECTAIR0003395486000087335FPNA90-DCKD_NOT_RCVD1st ShiftCSANTI314G130611220000102074677
338.4.2021GFCDIRECTROUTING GUIDE0003503362102079803ANUPSG90-DCKD_NOT_RCVD1st ShiftTMP91007511782211078278221000102079803
348.4.2021GFCDIRECTROUTING GUIDE0003503362102079803ANUPSG90-DCKD_NOT_RCVD1st ShiftTMP91007511782211078278221000102084607
358.4.2021GFCDIRECTROUTING GUIDE0003547388102087800FPFEDEX E90-DCKD_NOT_RCVD1st ShiftLPENAGD1381138381000102087800
368.5.2021GFCDIRECTROUTING GUIDE0003542502292228588UOUPS G90-DCKD_NOT_RCVD2nd ShiftSTHANGGD1611661000102090584
378.5.2021GFCDIRECTROUTING GUIDE0003542502292228588UOUPS G90-DCKD_NOT_RCVD2nd ShiftSTHANGGD161166100060629292228588
388.5.2021GFCDIRECTROUTING GUIDE0003558695102079942ANFEDEX G90-DCKD_NOT_RCVD1st ShiftLPENAGD12021202010006910796
398.5.2021GFCDIRECTROUTING GUIDE0003558695102079942ANFEDEX G90-DCKD_NOT_RCVD1st ShiftLPENAGD1202120201000102079942
408.6.2021GFCDIRECTOCN0003143469000087418ANNA9044316DCKD_NOT_RCVD1st ShiftU208792114314311431430000101969807
418.6.2021GFCDIRECTOCN0003143469000087418ANNA9044413DCKD_NOT_RCVD1st ShiftU208792114314311431430000101969807
428.10.2021GFCDIRECTOCN0003433912102053933FPUPS90-DCKD_NOT_RCVD2nd ShiftWBECHTELGD1216351821021035000102053933
438.10.2021GFCDIRECTOCN0003433912102053933FPUPS90-DCKD_NOT_RCVD2nd ShiftWBECHTELGD1216351821021035000102053954
448.11.2021GFCDIRECTROUTING GUIDE0003226988007678526UONA90-DCKD_NOT_RCVD1st ShiftDCRUZGONGD1184111110002074413040308052100002678365
458.11.2021GFCDIRECTROUTING GUIDE0003226988007678526UONA90-DCKD_NOT_RCVD1st ShiftDCRUZGONGD1184111110002074416063308052100007678526
468.11.2021GFCDIRECTAIR0003261838000087488FPNA90-DCKD_NOT_RCVD2nd ShiftLPENAGD13111100000456572100521777121405721567387951
478.11.2021GFCDIRECTAIR0003261838000087488FPNA90-DCKD_NOT_RCVD2nd ShiftLPENAGD13111100000456572100521777121503083567461699
488.11.2021GFCDIRECTAIR0003261838000087488FPNA90-DCKD_NOT_RCVD2nd ShiftLPENAGD1311110000-
498.11.2021GFCDIRECTOCN0003281636000087472ANTGT90-DCKD_NOT_RCVD2nd ShiftWBECHTELGD175620437567560000102022641
508.11.2021GFCDIRECTAIR0003499188000087480ANTGT90-DCKD_NOT_RCVD2nd ShiftWBECHTELGD149112104914910000102087932
518.11.2021GFCDIRECTROUTING GUIDE0003533307010491407UOUPS90-DCKD_NOT_RCVD2nd ShiftSTHANGGD13011303010002041500960010491389
528.11.2021GFCDIRECTROUTING GUIDE0003533307010491407UOUPS90-DCKD_NOT_RCVD2nd ShiftSTHANGGD13011303010002041500960010491390
538.11.2021GFCDIRECTROUTING GUIDE0003533307010491407UOUPS90-DCKD_NOT_RCVD2nd ShiftSTHANGGD13011303010002041500960010491391
548.11.2021GFCDIRECTROUTING GUIDE0003533307010491407UOUPS90-DCKD_NOT_RCVD2nd ShiftSTHANGGD13011303010002041500960010491392
558.11.2021GFCDIRECTROUTING GUIDE0003533307010491407UOUPS90-DCKD_NOT_RCVD2nd ShiftSTHANGGD13011303010002041500960010491393
568.11.2021GFCDIRECTROUTING GUIDE0003533307010491407UOUPS90-DCKD_NOT_RCVD2nd ShiftSTHANGGD13011303010002041500960010491394
578.11.2021GFCDIRECTROUTING GUIDE0003533307010491407UOUPS90-DCKD_NOT_RCVD2nd ShiftSTHANGGD13011303010002041500960010491395
588.11.2021GFCDIRECTROUTING GUIDE0003533307010491407UOUPS90-DCKD_NOT_RCVD2nd ShiftSTHANGGD13011303010002041500960010491396
Data


Mini sheet from a random date tab:
Comments made in these tabs NEED to not disappear unless we decide to manually edit the cells in that column ourselves.

GFCDNRDailyLog.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXY
1DateWarehouseChannelShip ModePOShipmentBrandCarrierDock_StatusIn YardStatusShiftUserDck PODck UnitsDck CaseDck SKUDNRExpected UnitsExpected CaseRcvd UnitsRcvd CaseRcvd SKUPACKING_LIST_NOComments
210.17.2021GFCDIRECTOCN0003514581102085674UOGILBERT90-DCKD_NOT_RCVD2nd ShiftWBECHTELGD112032312012032000102085674
310.17.2021GFCDIRECTOCN0003593878102124201FPUPS90-DCKD_NOT_RCVD2nd ShiftWBECHTELGD11,00736201,0071,00736000102124201
410.17.2021GFCDIRECTAIR0003602219102128055UOTGT90-DCKD_NOT_RCVD2nd ShiftWBECHTELGD1736584846000102128055
10.17.2021


Someone please help me here. How do I fix this so that comments entered on the individual tabs don't keep disappearing???
 
Upvote 0

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sunshine8790 said:
Unmarking this as solved as problems have developed.

So as a refresher - this file is a Sharepoint excel file. Macro buttons do not work on Sharepoint, so in order to run these macros we must open it in desktop version.
Once our daily report is run, we delete all the data in our data tab and copy and paste the new data in there.
Then we go to my "Control Center" tab and press the 2 macro buttons.
The macro buttons delete the unnecessary columns of data, reformat the dates and font style, create new automatic tabs, all that.
Because this is supposed to save time and make things simple.

HOWEVER - I ended up editing the original script I got here in an attempt to tweak new changes.
The change is problematic. We needed to manually add a new column for "Comments" to keep track of our data each day.
But here's the catch: We need to be able to add these comments on either version (desktop or sharepoint) if possible. Either way, we're finding that the comments added are disappearing after the new data is applied with the 2 macro buttons.
Click to expand...
Hmmm...

That is really not the preferred way to go about this. If your ORIGINAL question was answered, you should have left the solution as-is.
If you are adding new details to the question that were not there originally, you should really post a new thread/question (you can add a link back to this thread, if you think it is useful).
 
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,910
Messages
6,175,316
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