Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

modifying code create lists of files in multiple sheets based on month

0

hi experts  

 I  hope  to  find  help  in  this  forum

actually  I  have  no  experience to  how  mod  this  code so   ,  I  have  this  macro   it  loops  the  files  throught  folders and  sub  folders  and  add hyperlink   to  open  the  file  if  I  need  it     , the  problem   is showing   all   the  files  in  one  sheet    but   I want  arranging    the files   based  on  month  in  each  sheet  alone   .  

 I   have  12   sheets   and  its  names  JAN ,FEB  .. up to  DEC   so  it  supposss  brings  the  files for  each  sheet  alone  based  on  month   for  instance   sheet JAN    should  brings  all   files  in  this  month  and   FEB  bring  all files  in  FEB month and  so  on  the  rest  of  sheets. 

Sub LISTFILES()
Dim fPath As String
Dim fType As String
Dim fname As String
Dim NR As Long
Dim AddLinks As Boolean
      fPath = "C:\Users\PC WW\Documents\"
     fType = "*"
  If fType = "False" Then Exit Sub
    AddLinks = vbYes
    Application.ScreenUpdating = False
    NR = 4
    With ActiveSheet
        .[A2] = "LIST OF FILES"
        .[B2] = "Modified Date"
            Call FindFilesAndAddLinks(fPath, fType, NR, AddLinks)
        End With
        With ActiveSheet
          .Range("A:B").Columns.AutoFit
          .Range("B:B").HorizontalAlignment = xlCenter
        Range("B:B").Select
        Selection.NumberFormat = "d-mmm-yy  h:mm AM/pm"
        End With
        With ActiveSheet
        Range("A2").Select
        Selection.Font.Bold = True
        Range("B2").Select
        Selection.Font.Bold = True
        Columns("A:A").Select
        Selection.Font.Underline = xlUnderlineStyleNone
    End With
    Application.ScreenUpdating = True
End Sub
Private Sub FindFilesAndAddLinks(fPath As String, fType As String, ByRef NR As Long, AddLinks As Boolean)
Dim fname As String
Dim oFS As New FileSystemObject
Dim oDir
On Error Resume Next
fname = Dir(fPath & "*." & fType)
With ActiveSheet
    .Range("A" & NR) = fPath
    .Range("A" & NR).Select
    If AddLinks Then .Hyperlinks.Add Anchor:=Selection, _
           Address:=fPath, _
            TextToDisplay:="FOLDER NAME:  " & "  " & UCase(Split(fPath, "\")(UBound(Split(fPath, "\")) - 1))
                   Selection.Font.Bold = True
             Selection.Font.Size = 10
             Selection.Font.Name = "Arial"
             Selection.Font.Underline = xlUnderlineStyleNone
             With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
    End With
    NR = NR + 1
    Do While Len(fname) > 0
        If .Range("A" & NR) <> "" Then Debug.Print "Overwriting " & NR
        .Range("A" & NR) = fname
        .Range("B" & NR) = FileDateTime(fPath & fname)
        .Range("A" & NR).Select
        If AddLinks Then .Hyperlinks.Add Anchor:=Selection, _
            Address:=fPath & fname, _
            TextToDisplay:=fname
        NR = NR + 1
        fname = Dir
    Loop 
    Set oDir = oFS.GetFolder(fPath)
    For Each oSub In oDir.SubFolders
        NR = NR + 1
        Call FindFilesAndAddLinks(oSub.Path & "\", fType, NR, AddLinks)
    Next oSub
End With
End Sub

Answer
Discuss

Answers

0

The fastest way to get what you want is to take the list your code prepares, filter it on each month in turn, and save the filtered extracts to your 12 monthly sheets. This can't be faster than just pressing a button but it's a lot faster than writing the code.

By its nature, the task would appear to be an annual one, meaning that you can do it within 10 minutes once a year. If it takes 120 minutes to write the code you can recover your investment after 12 years. If you think that is a worth while ROI the approach you should take is still the one I outlined above: filter and save, starting with the list prepared by the existing code.

You are right in that this is not necessarily the best way in absolute terms, which might well be to modify the existing code, as you suggest. However, since you have no plan of writing the code yourself, you should be concerned about the fastest way to get it done. Toward that end "filter & save" beats all other ideas.

Discuss

Discussion

thanks !
the  filter  is  excellant choice   but  then   it  should  create  a  new  macro  about  filter   and  I  don't  prefer  to  be  more than  macro  in  file  maybe  occurs  some  problems  , result  of  that  it  overlabs   some  lines  with  two  macros that's  why  I search   moding  in  the  same  code  instead of  create  a new  macro about  filter.
Alaa (rep: 28) May 24, '21 at 3:45 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login