Clear Row Data based on Date Range

[babar1988]

Hello Everyone,

I am using Excel VBA to copy data from other sheets to the "Sales" sheet, which is working perfectly fine.

now the thing is I have two-row sales and BOB

for the sales columns, data is loading from 1st till the end of the month, which I need to remove from today till the end of month clear cell after loading this data.

for BoB data is loading the same (1st till End of the month ) in which I need to remove from the cells data 1st of the month till yesterday.

Below is screenshot.

Data - - Copy (2).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	Z	AA	AB	AC	AD	AE	AF
1	Month	1-Mar	2-Mar	3-Mar	4-Mar	5-Mar	6-Mar	7-Mar	8-Mar	9-Mar	10-Mar	11-Mar	12-Mar	13-Mar	14-Mar	15-Mar	16-Mar	17-Mar	18-Mar	19-Mar	20-Mar	21-Mar	22-Mar	23-Mar	24-Mar	25-Mar	26-Mar	27-Mar	28-Mar	29-Mar	30-Mar	31-Mar
5	Sale Entry	56	56	71	72	51	49	54	53	54	516	93	92	92	97	85	90	73	79	58	28	18	15	18	25	21	24	17	16	17	19	14
6	B.O.B Entry	56	56	71	72	51	49	54	53	54	516	93	92	92	97	85	90	73	79	58	28	18	15	18	25	21	24	17	16	17	19	14
Sales

and I am looking to make it link this range change day by day.

Data - - Copy (2).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	Z	AA	AB	AC	AD	AE	AF
1	Month	1-Mar	2-Mar	3-Mar	4-Mar	5-Mar	6-Mar	7-Mar	8-Mar	9-Mar	10-Mar	11-Mar	12-Mar	13-Mar	14-Mar	15-Mar	16-Mar	17-Mar	18-Mar	19-Mar	20-Mar	21-Mar	22-Mar	23-Mar	24-Mar	25-Mar	26-Mar	27-Mar	28-Mar	29-Mar	30-Mar	31-Mar
5	Sale Entry	56	56	71	72	51	49	54	53	54
6	B.O.B Entry										516	93	92	92	97	85	90	73	79	58	28	18	15	18	25	21	24	17	16	17	19	14
Sales

and this is the current VBA code I am using.

Sub GetData()




Dim date1 As Variant

date1 = Worksheets("Total Rev").Range("M3").Value
date2 = Worksheets("Total Rev").Range("M4").Value
date3 = Worksheets("Total Rev").Range("M5").Value
date4 = Worksheets("Total Rev").Range("M6").Value
date5 = Worksheets("Total Rev").Range("M7").Value
date6 = Worksheets("Total Rev").Range("M8").Value
date7 = Worksheets("Total Rev").Range("M9").Value
date8 = Worksheets("Total Rev").Range("M10").Value
date9 = Worksheets("Total Rev").Range("M11").Value
date10 = Worksheets("Total Rev").Range("M12").Value
date11 = Worksheets("Total Rev").Range("M13").Value
date12 = Worksheets("Total Rev").Range("M14").Value
date13 = Worksheets("Total Rev").Range("M14").Value
date14 = Worksheets("Total Rev").Range("M16").Value
date15 = Worksheets("Total Rev").Range("M17").Value
date16 = Worksheets("Total Rev").Range("M18").Value
date17 = Worksheets("Total Rev").Range("M19").Value
date18 = Worksheets("Total Rev").Range("M20").Value
date19 = Worksheets("Total Rev").Range("M21").Value
date20 = Worksheets("Total Rev").Range("M22").Value
date21 = Worksheets("Total Rev").Range("M23").Value
date22 = Worksheets("Total Rev").Range("M24").Value
date23 = Worksheets("Total Rev").Range("M25").Value
date24 = Worksheets("Total Rev").Range("M26").Value
date25 = Worksheets("Total Rev").Range("M27").Value
date26 = Worksheets("Total Rev").Range("M28").Value
date27 = Worksheets("Total Rev").Range("M29").Value
date28 = Worksheets("Total Rev").Range("M30").Value
date29 = Worksheets("Total Rev").Range("M31").Value
date30 = Worksheets("Total Rev").Range("M32").Value
date31 = Worksheets("Total Rev").Range("M33").Value



Worksheets("Sales").Range("B29", Range("B30")).Value = date1
Worksheets("Sales").Range("C29", Range("C30")).Value = date2
Worksheets("Sales").Range("D29", Range("D30")).Value = date3
Worksheets("Sales").Range("E29", Range("E30")).Value = date4
Worksheets("Sales").Range("F29", Range("F30")).Value = date5
Worksheets("Sales").Range("G29", Range("G30")).Value = date6
Worksheets("Sales").Range("H29", Range("H30")).Value = date7
Worksheets("Sales").Range("I29", Range("I30")).Value = date8
Worksheets("Sales").Range("J29", Range("J30")).Value = date9
Worksheets("Sales").Range("K29", Range("K30")).Value = date10
Worksheets("Sales").Range("L29", Range("L30")).Value = date11
Worksheets("Sales").Range("M29", Range("M30")).Value = date12
Worksheets("Sales").Range("N29", Range("N30")).Value = date13
Worksheets("Sales").Range("O29", Range("O30")).Value = date14
Worksheets("Sales").Range("P29", Range("P30")).Value = date15
Worksheets("Sales").Range("Q29", Range("Q30")).Value = date16
Worksheets("Sales").Range("R29", Range("R30")).Value = date17
Worksheets("Sales").Range("S29", Range("S30")).Value = date18
Worksheets("Sales").Range("T29", Range("T30")).Value = date19
Worksheets("Sales").Range("U29", Range("U30")).Value = date20
Worksheets("Sales").Range("V29", Range("V30")).Value = date21
Worksheets("Sales").Range("W29", Range("W30")).Value = date22
Worksheets("Sales").Range("X29", Range("X30")).Value = date23
Worksheets("Sales").Range("Y29", Range("Y30")).Value = date24
Worksheets("Sales").Range("Z29", Range("Z30")).Value = date25
Worksheets("Sales").Range("AA29", Range("AA30")).Value = date26
Worksheets("Sales").Range("AB29", Range("AB30")).Value = date27
Worksheets("Sales").Range("AC29", Range("AC30")).Value = date28
Worksheets("Sales").Range("AD29", Range("AD30")).Value = date29
Worksheets("Sales").Range("AE29", Range("AE30")).Value = date30
Worksheets("Sales").Range("AF29", Range("AF30")).Value = date31



End Sub

 

[DanteAmor]

Hi and welcome to MrExcel!

In your image the data starts at B5, but in the macro it starts at B29. I kept the B29 in the macro, but you can adjust it in the range.
The macro copies the data from the "Total Rev" sheet and also leaves the cells empty.

Sub GetData1()
  Dim rng As Range
  Dim d As Long
  
  d = Day(Date) - 1
  Set rng = Sheets("Sales").Range("B29")
  rng.Resize(2, 31).ClearContents
  With Sheets("Total Rev").Range("M3")
    If d > 0 Then rng.Resize(1, d).Value = Application.Transpose(.Resize(d).Value)
    rng.Offset(1, d).Resize(1, 31 - d).Value = Application.Transpose(.Offset(d).Resize(31 - d).Value)
  End With
End Sub

 

[babar1988]

hi @DanteAmor

Thank you soo much, you are great and very helpful, the code is working perfectly fine.

once again million thanks

Best Regards,

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.