Sunshine8790
Board Regular
- Joined
- Jun 1, 2021
- Messages
- 86
- Office Version
- 365
- Platform
- Windows
Original Thread Here
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:
Same tabs AFTER running:
Coding for first macro button:
Coding for 2nd Macro button:
How the tabs are setup:
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.
Mini Sheet (Small selection, too big to do the whole thing) from Data tab with current 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.
Someone please help me here. How do I fix this so that comments entered on the individual tabs don't keep disappearing???
Is there some kind of edit to the script someone can help me make? I need the "Comments" heading pasted into each new data tab, but I need the original data in that column (Y) in all the date tabs to stay put.
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:
Same tabs AFTER running:
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:
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.
DNRDailyLog.xlsm | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | Qlik 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 | |||||||||||
Control Center |
Mini Sheet (Small selection, too big to do the whole thing) from Data tab with current data:
DNRDailyLog.xlsm | |||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | |||
1 | Date | Warehouse | Channel | Ship Mode | PO | Shipment | Brand | Carrier | Dock_Status | In Yard | Status | Shift | User | Dck PO | Dck Units | Dck Case | Dck SKU | DNR | Expected Units | Expected Case | Rcvd Units | Rcvd Case | Rcvd SKU | PACKING_LIST_NO | Comments | ||
2 | 7.23.2021 | GFC | WHOLESALE | - | 0000531167 | 00005311670102068328 | UOW | 90 | - | DCKD_NOT_RCVD | 1st Shift | LUSANTI14G | 1 | 86 | 0 | 1 | 86 | 86 | 0 | 0 | 0 | 0 | 102068328 | ||||
3 | 7.23.2021 | GFC | DIRECT | AIR | 0003423921 | 102029335 | FP | TGT 1457 | 90 | - | DCKD_NOT_RCVD | 1st Shift | LPENAGD | 1 | 2,041 | 40 | 15 | 2,041 | 2,041 | 40 | 0 | 0 | 0 | 102029335 | |||
4 | 7.23.2021 | GFC | DIRECT | AIR | 0003423921 | 102029335 | FP | TGT 1457 | 90 | - | DCKD_NOT_RCVD | 1st Shift | LPENAGD | 1 | 2,041 | 40 | 15 | 2,041 | 2,041 | 40 | 0 | 0 | 0 | 102030446 | |||
5 | 7.26.2021 | GFC | DIRECT | ROUTING GUIDE | 0003540746 | 292226845 | UO | UPS | 90 | - | DCKD_NOT_RCVD | 2nd Shift | WBECHTELGD | 1 | 120 | 3 | 1 | 120 | 120 | 3 | 0 | 0 | 0 | 102078681 | |||
6 | 7.26.2021 | GFC | DIRECT | ROUTING GUIDE | 0003540746 | 292226845 | UO | UPS | 90 | - | DCKD_NOT_RCVD | 2nd Shift | WBECHTELGD | 1 | 120 | 3 | 1 | 120 | 120 | 3 | 0 | 0 | 0 | 60629292226845 | |||
7 | 7.28.2021 | GFC | DIRECT | ROUTING GUIDE | 0003428219 | 010483130 | UO | UPS | 90 | - | DCKD_NOT_RCVD | 2nd Shift | STHANGGD | 1 | 50 | 1 | 1 | 50 | 50 | 1 | 0 | 0 | 0 | 2041503900010483119 | |||
8 | 7.28.2021 | GFC | DIRECT | ROUTING GUIDE | 0003428219 | 010483130 | UO | UPS | 90 | - | DCKD_NOT_RCVD | 2nd Shift | STHANGGD | 1 | 50 | 1 | 1 | 50 | 50 | 1 | 0 | 0 | 0 | 2041503900010483120 | |||
9 | 7.28.2021 | GFC | DIRECT | ROUTING GUIDE | 0003428219 | 010483130 | UO | UPS | 90 | - | DCKD_NOT_RCVD | 2nd Shift | STHANGGD | 1 | 50 | 1 | 1 | 50 | 50 | 1 | 0 | 0 | 0 | 2041503900010483121 | |||
10 | 7.28.2021 | GFC | DIRECT | ROUTING GUIDE | 0003428219 | 010483130 | UO | UPS | 90 | - | DCKD_NOT_RCVD | 2nd Shift | STHANGGD | 1 | 50 | 1 | 1 | 50 | 50 | 1 | 0 | 0 | 0 | 2041503900010483122 | |||
11 | 7.28.2021 | GFC | DIRECT | ROUTING GUIDE | 0003428219 | 010483130 | UO | UPS | 90 | - | DCKD_NOT_RCVD | 2nd Shift | STHANGGD | 1 | 50 | 1 | 1 | 50 | 50 | 1 | 0 | 0 | 0 | 2041503900010483123 | |||
12 | 7.28.2021 | GFC | DIRECT | ROUTING GUIDE | 0003428219 | 010483130 | UO | UPS | 90 | - | DCKD_NOT_RCVD | 2nd Shift | STHANGGD | 1 | 50 | 1 | 1 | 50 | 50 | 1 | 0 | 0 | 0 | 2041503900010483124 | |||
13 | 7.28.2021 | GFC | DIRECT | ROUTING GUIDE | 0003428219 | 010483130 | UO | UPS | 90 | - | DCKD_NOT_RCVD | 2nd Shift | STHANGGD | 1 | 50 | 1 | 1 | 50 | 50 | 1 | 0 | 0 | 0 | 2041503900010483125 | |||
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.
DNRDailyLog.xlsm | |||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | |||
1 | Date | Warehouse | Channel | Ship Mode | PO | Shipment | Brand | Carrier | Dock_Status | In Yard | Status | Shift | User | Dck PO | Dck Units | Dck Case | Dck SKU | DNR | Expected Units | Expected Case | Rcvd Units | Rcvd Case | Rcvd SKU | PACKING_LIST_NO | Comments | ||
2 | 10.17.2021 | GFC | DIRECT | OCN | 0003514581 | 102085674 | UO | GILBERT | 90 | - | DCKD_NOT_RCVD | 2nd Shift | WBECHTELGD | 1 | 120 | 32 | 3 | 120 | 120 | 32 | 0 | 0 | 0 | 102085674 | |||
3 | 10.17.2021 | GFC | DIRECT | OCN | 0003593878 | 102124201 | FP | UPS | 90 | - | DCKD_NOT_RCVD | 2nd Shift | WBECHTELGD | 1 | 1,007 | 36 | 20 | 1,007 | 1,007 | 36 | 0 | 0 | 0 | 102124201 | |||
4 | 10.17.2021 | GFC | DIRECT | AIR | 0003602219 | 102128055 | UO | TGT | 90 | - | DCKD_NOT_RCVD | 2nd Shift | WBECHTELGD | 1 | 73 | 6 | 5 | 84 | 84 | 6 | 0 | 0 | 0 | 102128055 | |||
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???
Is there some kind of edit to the script someone can help me make? I need the "Comments" heading pasted into each new data tab, but I need the original data in that column (Y) in all the date tabs to stay put.
Last edited by a moderator: