Hi, I have a small issue this is a daily report I need to run, checking the previous day to check which are the new clients and paid clients.

[TAA]

I did a macro for this
But it does not run when new information is posted again. This data goes on for 100 rows.

Can you help to make this Dynamic and to select the no fill cells in the today's sheet (16.06.2023) to update the previous days sheet (15.06.2023) and this sheet to add in new customers and delete lines to take out paid customers and this will be named as the total clients later.

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+w
'
Range("H2").Select
Sheets("15.06.2023").Select
Range("A2:A9").Select
Selection.Copy
Sheets("16.06.2023").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H2:H9,A2:A10").Select
Range("A2").Activate
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("J11").Select
Sheets("15.06.2023").Select
Range("A7").Select
Sheets("15.06.2023").Select
Rows("5:7").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A5").Select
Sheets("16.06.2023").Select
Range("A3:G3,A6:G7").Select
Range("A6").Activate
Selection.Copy
Sheets("15.06.2023").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Sheets("16.06.2023").Select
Range("A1").Select
Sheets("15.06.2023").Select
Range("4:4,8:8").Select
Range("A8").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
Sheets("16.06.2023").Select
End Sub

Thank you.