VBA to delete the rows which contains the data of previous week!!

[Mohit53787]

Hello Experts,
I am trying to build a macro which will help me to delete the rows which contains the data of previous week.

I want create a macro as follows:
1. Open Excel sheets based on the calender week.
Example: CW06_Report_teamA
CW06_Report_teamB
CW06_Report_teamC, and so on.... (around 30 teams = 30 Excels)

2. In Sheet1 there will be a summarized data of all the previous weeks.
I want to delete the data of all the old calender weeks and show the data of just previous week.
For example today is 14.02. and the previous Calender week was 6

(the exact cell number and range is unknown, because each week the data piles up.)
(There will be multiple tabs. I want to delete the data just from 1st tab "Sheet1")

3. Save and close the excels.

It would be a great help, if someone could help me with the code.

Thanks in advance!

 

[dermie_72]

what code have you got so far?
Are all the reports in the one folder?
Are they the only items in that folder?
How do you identify the previous weeks data in sheet1 and what range is this variable in?

 

[Mohit53787]

what code have you got so far?
Are all the reports in the one folder?
Are they the only items in that folder?
How do you identify the previous weeks data in sheet1 and what range is this variable in?

Hello @dermie_72,
Thank you for your reply.
Yes, all the reports are in one folder and those are only items in that folder according to calender weeks.

So I have bunch of macros to create weekly reports.
One of the macro helps me to create the weekly reports for all the teams. I just need to create a folder with the previous calender week and enter the calender week number in that macro.

Then that macro pulls the data from various sources and generates around 30 excels reports in that folder.

And then in each excel list on tab "sheet 1" is the summary.

And each week the summary table adds on.

So lets say if I am creating the report for calender week 45, then the summary table (as shown in screenshot) will be automatically generated for all the calender weeks (from 1 to 45), and then I have to manually delete the summary tables from calender week 1 to 44, so that I can just show the summary of calender week 45.

So to sum it up:

Current situation: The macro auto-generates the summary tables for all the previous calender weeks till date.
Aim: The summary table must show only for one calender week, for which I am preparing report (i.e. previous week).

PS: I create the report on every Monday to report the previous week.

Many thanks in advance for your support!!

 

[dermie_72]

you could try this. Again, the data wasn't 100% clear either for formatting or location on where the identifiers are, but this will loop through every file in the folder, open sheet 1. You just need to identify the way to delete the old data.

Sub Mohit53787()

Dim filesystem As Object, myfolder As Object
Dim myfiles As Object, myfile As Object
Dim SubFolders As Object

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.DisplayAlerts = False

Set filesystem = CreateObject("Scripting.filesystemobject")
Set myfolder = filesystem.GetFolder("C:\TEST\")
Set myfiles = myfolder.Files

For Each myfile In myfiles
    Workbooks.Open Filename:=myfile.Path
    Sheets("Sheet 1").Activate
        'identify what Rows you want to delete.   Can't see your data to know
        'you could do this with a weeknum
    A = Application.WorksheetFunction.WeekNum(Now())
    B = Format(Now, "YYYY")
    Test = B & " CW" & A
        
        'then use that to see if you want to delete it
        'Then select those Rows and entire row delete
    ActiveWorkbook.Save
    ActiveWorkbook.Close
Next

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.DisplayAlerts = True
MsgBox ("Done")

End Sub

 

[Mohit53787]

you could try this. Again, the data wasn't 100% clear either for formatting or location on where the identifiers are, but this will loop through every file in the folder, open sheet 1. You just need to identify the way to delete the old data.

Sub Mohit53787()

Dim filesystem As Object, myfolder As Object
Dim myfiles As Object, myfile As Object
Dim SubFolders As Object

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.DisplayAlerts = False

Set filesystem = CreateObject("Scripting.filesystemobject")
Set myfolder = filesystem.GetFolder("C:\TEST\")
Set myfiles = myfolder.Files

For Each myfile In myfiles
    Workbooks.Open Filename:=myfile.Path
    Sheets("Sheet 1").Activate
        'identify what Rows you want to delete.   Can't see your data to know
        'you could do this with a weeknum
    A = Application.WorksheetFunction.WeekNum(Now())
    B = Format(Now, "YYYY")
    Test = B & " CW" & A
       
        'then use that to see if you want to delete it
        'Then select those Rows and entire row delete
    ActiveWorkbook.Save
    ActiveWorkbook.Close
Next

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.DisplayAlerts = True
MsgBox ("Done")

End Sub

Hello,
Thank you for your response.

After editing the macro as below its working fine

---------------------------------------------------------------------------
Sub MacroTest()


Dim filesystem As Object, myfolder As Object
Dim myfiles As Object, myfile As Object
Dim SubFolders As Object

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.DisplayAlerts = False

Set filesystem = CreateObject("Scripting.filesystemobject")
Set myfolder = filesystem.GetFolder(Range("B2").Text)
Set myfiles = myfolder.Files

For Each myfile In myfiles
Workbooks.Open Filename:=myfile.Path
Sheets("Tabelle1").Activate
Rows("1:" & Cells.Find("2024 CW08").Row - 1).Delete


ActiveWorkbook.Save
ActiveWorkbook.Close
Next

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.DisplayAlerts = True
MsgBox ("Done")


End Sub
-----------------------------------------------------------------------------

I just need help with:

Rows("1:" & Cells.Find("2024 KW08").Row - 1).Delete

So I want to delete everything which is above "2024 CW08"

Can I use a cell reference to find "2024 CW 08"

In my macro I want to find text of cell B1 and then delete everything above it.

For example>

Rows("1:" & Cells.Find(Range("B2").Text).Row - 1).Delete

Thanks in advance!!

 

[Mohit53787]

@dermie_72

 

[dermie_72]

maybe use a Match function to identify where the new week starts and delete everything else above it?

 

[Alex Blakenburg]

Presumably B1 with the text to search for is not on the same sheet that you are trying to delete the rows from.
Is B1 in the newly opened workbook or the one that is running the code ?
What is the sheet name that contains B2 ?