Any suggestions to streamline the codes? My work after two weeks on this forum^^

[ironny90]

Hi there! Just started to learn vba and created the macro below with the help of a lot of the senior members here. The macro is pieced together as you can probably tell, but it runs so far. Just want to see if there is any suggestion to streamline it or any good practices I can learn. I removed select as some have recommended earlier. Any comment is welcomed. Thank you!!!

Sub MLA()
Dim folderpath As Variant
folderpath = Range("H11").Value
Set fso = CreateObject("scripting.filesystemobject")
Set ff = fso.getfolder(folderpath)
For Each file In ff.Files
Workbooks.Open file
    Sheets("03").Copy Before:=Sheets("03")
    Sheets("03").Name = "04"
    Cells.Replace What:="2-22", Replacement:="3-22", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Cells.Replace What:="3/31/2022", Replacement:="4/30/2022", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
On Error Resume Next
Sheets("Monthly_03").Copy Before:=Sheets("Monthly_03")
                Sheets("Monthly_03(2)").Name = "Monthly_04"
                Sheets("Monthly_04").Cells.ClearContents
                Sheets("Monthly_04").Range("A1").Select
               
Sheets("Dashboard").Activate
Cells.Find(What:="2022-03", After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Resize(3).EntireRow.Copy
    Cells.Find(What:="2022-03", After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Selection.Insert Shift:=xlDown
    Range("A1").Select
    Cells.Find(What:="2022-03", After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        ActiveCell.FormulaR1C1 = "'2022-04 Notes"
    ActiveCell.Offset(1, 0).EntireRow.Resize(2).ClearContents

    ActiveSheet.Outline.ShowLevels RowLevels:=8
   
Dim res As Variant
    res = Application.Match("44681", ActiveSheet.Range("A10:A100"), 0)

If IsError(res) Then
  Dim tFound As Range
Set tFound = Range("A10:A100").Find(Format(Application.WorksheetFunction.EoMonth(Date, -1), "mmm-yy"), , xlValues, xlPart, xlByRows, xlNext, False, False, False)
tFound.EntireRow.Copy
tFound.Offset(1, 0).Insert Shift:=xlDown
tFound.Offset(1, 3).ClearContents
tFound.Offset(1, 6).ClearContents
tFound.Offset(1, 11).ClearContents
tFound.Offset(1, 16).ClearContents
Else
Dim rFound As Range
Set rFound = Range("A10:A100").Find(Format(Application.WorksheetFunction.EoMonth(Date, 0), "mmm-yy"), , xlValues, xlPart, xlByRows, xlNext, False, False, False)
rFound.EntireRow.Ungroup
End If
ActiveSheet.Outline.ShowLevels RowLevels:=1
Range("A1").Select

ActiveWorkbook.Save
ActiveWorkbook.Close

Next
End Sub

 

[ironny90]

essentially it's trying to make copies of one or two tabs, change the date in the tabs and update the dashboard.

 

[ironny90]

anyone?