SOS!! Comments disappearing after running macros

[Sunshine8790]

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:

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:

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.

 

[Dan_W]

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:

    Columns("J:J").Select
    Selection.Delete Shift:=x1ToLeft

can be changed to

    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:

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

becomes

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.

 

[Sunshine8790]

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.

 

[jasonb75]

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.

        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.

 

[vcoolio]

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:

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.