SOS!! Comments disappearing after running macros

Sunshine8790

Board Regular
Joined
Jun 1, 2021
Messages
86
Office Version
  1. 365
Platform
  1. 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:

1634819988752-png.49516
1634820005051-png.49517

Same tabs AFTER running:

1634820052031-png.49518
1634820071533-png.49519


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


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
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
Control Center


Mini Sheet (Small selection, too big to do the whole thing) from Data tab with current data:
DNRDailyLog.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
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
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???

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:

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hi. So it's not immediately obvious to me why it is that the various cells are being deleted, but I would make two comments that will hopefully get you a bit closer to the solution.
  • Macro#1 - This appears to be code generated by the Macro Recorder. It's perfectly fine to use it, and I use it all the time, but it can sometimes cause problems. At a minimum, it's a bit tricky to read, but luckily, not that difficult to clean up and optimise. Basically, you need to get rid of the .Select and Selections and connect the remain parts of each line of code. So, for example, where it says:
VBA Code:
    Columns("J:J").Select
    Selection.Delete Shift:=x1ToLeft

can be changed to

VBA Code:
    Columns("J:J").Delete Shift:=x1ToLeft

Where a Select is followed by a With Selection..., you just need to replace the Selection with the remaining part of the first line. So:

VBA Code:
Columns("B:X").Select
    With Selection.Font

becomes

VBA Code:
With Columns("B:X").Font

Why am I focusing on this? Because like Select can sometimes cause problems. When things start changing on sheets that you didn't intend to change, its sometimes the case that the user may have inadvertently selected multiple sheets. So when that happens, a change in one cell on one sheet will be mirrored across every other selection. It's not obvious that that is what has happened here, but then I've not made it all the way through code yet! :)

  • Debugging your code - what line in which macro is causing the problems? When there are problems with your code, there a 'step through' function in VBA which allows you go through the code with VBA one line at a time - you do this by press F8 for each line. If you can do this, you will able to pinpoint which macro, and which line of that macro, is causing the problems. (LINK)
Hope that helps.
 
Upvote 0
I think I fixed Macro #1, and I appreciate that - but I still don't know how to solve my actual problem.
I'll keep trying to fiddle with it until someone with a keen eye can create a miracle for me lol.
 
Upvote 0
The code is too messy to make a lot of sense of, but one observation.
Here, in the second macro, you're clearing the used range of ws. ws appears to be referencing each of the dated sheets in a loop, the comments are in the used range so will be removed when the contents are cleared.
VBA Code:
        Set ws = Sheets(key)
        ws.UsedRange.Clear
        
        With sht.Range("A1:A" & lr)
              .AutoFilter 1, key
              .Resize(, 25).Copy ws.[A1]
              .AutoFilter
        End With
Noting that you appear to be restoring the content from the 'Data' sheet, there are several lines in the first macro that are deleting columns and shifting the remaining data to the left. It is likely that this is causing the comments to move several columns to the left of the original location and they may have been overwritten by other data.

Looking at the code, I can only see that it should be moved to column W in the data sheet rather than being deleted, but as I said, it's messy so I could be missing things.
 
Upvote 0
Hello Sunshine,

As you are manually adding comments (Column Y) to the destination sheets you are adding to the range(usedrange) which is cleared each time the code is executed with this line:

VBA Code:
ws.UsedRange.Clear

Place an apostrophe in front of the line of code which will de-activate it. You'll note that the comments are then maintained in Column Y but you will end up with duplicates in each destination sheet if the relevant rows of data in the main sheet are not cleared while the code is executing.

Cheerio,
vcoolio.
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,264
Members
452,627
Latest member
KitkatToby

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