Extracting colomn values from a file that is on the last day of the each month

[wdgpw]

Hi all,

I'm currently doing a course on Macro's by Leila and can currently only do very basic macros.

I have a monthly task where at the beginning of each month I need to open an easily located file which has been saved down as the last business day of the previous month.
Within this file I have to look in Column Z to see if any of the values are >0, and if there are, I require all of the values on this row.
Essentially I want to copy over any Rows where there are values in Column Z.

I am trying to create a macro that I can run during the first week of every month that will collate this information (from the previous month only) and add to a single spreadsheet hub.
Can somebody please assist me or point me in the right direction as to create such a macro.

Things I am really struggling to find commands for are..
1) I need the macro to search for a file stored in folder named after the previous month, with in each monthly folder it needs to look for the last date saved down in the folder - is this possible?

2) Do the files have to be open to run the macro and if not, how does the macro navigate the monthly evolving file path where the data is stored?

Any assistance with the above would be greatly appreciated.

Thanks.
Whys

 

[braindiesel]

Since you have constants... easily findable folder and target column Z

This should do it.

Sub goGetIt()
    'Build path to file
    'there is likely a better way to do this but this works
    one = "='C:\Users\munro\Documents\namedFolder\["
    Range("A1").Formula = "=YEAR(EOMONTH(TODAY(),-1))&TEXT(MONTH(EOMONTH(TODAY(),-1)),""00"")&TEXT(DAY(EOMONTH(TODAY(),-1)),""00"")" 'assumes you are looking for the last day of the prior month
    two = Range("A1").Value
    Range("A1").ClearContents
    three = ".xlsx]Sheet1'!"
    
    'make sure file is named correctly
    Dim targetFileName As String
    Dim targetFileExists As String
 
    targetFileName = "C:\Users\munro\Documents\namedFolder\" & two & ".xlsx"
    targetFileExists = Dir(targetFileName)
 
    If targetFileExists = "" Then
        MsgBox "Please save a file in folder as " & two & ".xlsx in order to continue"
        Exit Sub
    Else
        'write formula to pull data
        Range("A1").Value = two & " results" 'change part in quotes to your desired header label
        Range("A2").Select 'choose desired column
        Range("A2").Formula = one & two & three & "A2" 'change A2 to your starting cell in column Z
        Selection.Resize(8, 1).Select 'resize to proper size
        Selection.FillDown
    End If
    
End Sub

 

[wdgpw]

Since you have constants... easily findable folder and target column Z

This should do it.

Sub goGetIt()
    'Build path to file
    'there is likely a better way to do this but this works
    one = "='C:\Users\munro\Documents\namedFolder\["
    Range("A1").Formula = "=YEAR(EOMONTH(TODAY(),-1))&TEXT(MONTH(EOMONTH(TODAY(),-1)),""00"")&TEXT(DAY(EOMONTH(TODAY(),-1)),""00"")" 'assumes you are looking for the last day of the prior month
    two = Range("A1").Value
    Range("A1").ClearContents
    three = ".xlsx]Sheet1'!"
   
    'make sure file is named correctly
    Dim targetFileName As String
    Dim targetFileExists As String

    targetFileName = "C:\Users\munro\Documents\namedFolder\" & two & ".xlsx"
    targetFileExists = Dir(targetFileName)

    If targetFileExists = "" Then
        MsgBox "Please save a file in folder as " & two & ".xlsx in order to continue"
        Exit Sub
    Else
        'write formula to pull data
        Range("A1").Value = two & " results" 'change part in quotes to your desired header label
        Range("A2").Select 'choose desired column
        Range("A2").Formula = one & two & three & "A2" 'change A2 to your starting cell in column Z
        Selection.Resize(8, 1).Select 'resize to proper size
        Selection.FillDown
    End If
   
End Sub

Hi BrainDiesel,

Thank you for being so generous with your time, I really appreciate your help with this!
I shall give this a go on Monday and will let you know how I get on.

I'm guessing this specifically looks for the last day of the previous month but can this be fine-tuned to the look for the last business day of the month (Monday-Friday, discounting bank holidays) or alternatively seek out the last date saved down in the Monthly folder?

Thanks and regards

Whys

 

[braindiesel]

As you wanted it fully automated I built the date.
If you are going to have variable file names then you could put the file name in a cell and reference that instead

Good luck with it

 

[wdgpw]

As you wanted it fully automated I built the date.
If you are going to have variable file names then you could put the file name in a cell and reference that instead

Good luck with it

Thanks for that tip BrainDiesel and thanks again for writing out that Code I'm sure I'll find it invaluable!