VBA to select rows that contain a selected month?

  • Hello people


    As a continuation of my previous road to enlightenment project ie learn some useful VBA, I requested help to select rows of data that contained the current month and export those rows and selected columns into a CSV ready to be exported into another program. Thanks to everyone who provided advice or updated my code.


    My issue now is how to select any month within a calendar year rather than just the current month. I was thinking of a drop down menu where you can select a month but unfortunately I have no idea how to implement this into my current code. ( I saw something similar elsewhere but unfortunately I couldn't make it work for me so binned it).


    Anyone out there who can set me in the right direction?


    Many thanks once again.


    Also, on another note (apologies to admin if 2nd question is not allowed under the same heading), I export the headers separately as I believe that they are not copied over due to to not being recognised as a date in the error capture routine.

    When the relevant date rows are then copied to the new sheet, I end up with a blank rows between the headers and the data.

    I know I could just delete the blank rows but was wondering if there was a more elegant way to move the data up so it sits below the the headers?


    TVM


    Smudge


  • I've added an ExportToCSV macro to the attached example.


    This uses a prompt for the month number, expected input is 1 to 12 (default = current month number). It assumes the current year is to be used.


    This locates the data to be exported using an autofilter method which should be more efficient than looping.


    ExportToCSV.xlsm

  • Thankyou once again gijsmo for your help. This is great and works as advertised. Thankyou also for including the comments which really help my understanding of what the code is doing, especially as it is somewhat removed from my original clunky efforts.

  • Apologies for revisiting a thread which I had marked as resolved - but I have found an issue which I think I need some help getting my head around with regards to selecting the month to be exported.

    Currently the VBA appears to rely on the selected month being in the current year, so each of the 12 months in this year are available to choose if the report is run in the current year.

    However, in practice, the selected month should always be a month behind when the report is run.

    Eg the month end report for Jan will be run in Feb, June's report will be run in July etc..

    This process falls down when Decembers data is required, as the report will be run in Jan and therefore previous December will no longer be in the same year as the report is run.

    I've been fiddling around with IF statements trying to make it work but unfortunately I can't seem to find the right terminology, especially as we're not actually at a point to test it (ie it's not January yet and I'm trying to get the previous months data).


    Many thanks


    Smudge

  • Try this, to test it you will need to change your system clock It should work because if the Month is 12, then the code calculates the last day of the month before.


    Code
    lFrom = CLng(DateValue(Format("1/" & lMth & "/" & Year(Now()), "dd/mm/yyyy")))
        If lMth < 12 Then
          lTo = CLng(DateValue(Format("1/" & lMth + 1 & "/" & Year(Now()), "dd/mm/yyyy")))
        Else
          lTo = CLng(Application.WorksheetFunction.EoMonth(Date, -1))
        End If
  • Hi RoyUK

    Thanks very much for your prompt assistance. I hadn't heard of EOMONTH before but looks as though it should work based on what I've read.

    I added three extra rows with December dates, unfortunately the code produce a message that it was unable to find the required rows when 12 (December)

    I think there is something missing from your amended code that doesn't take into account the start date of Dec being in the previous month?


    Code
    lFrom = CLng(DateValue(Format("1/" & lMth & "/" & Year(Now()), "dd/mm/yyyy")))

    Wouldn't this assume that the variable lFrom is in 2022 (as I set the clock forward to test?)based on the Year(Now) function?



    Code
    lFrom = CLng(DateValue(Format("1/" & lMth & "/" & Year(Now()), "dd/mm/yyyy")))
    If lMth < 12 Then
    lTo = CLng(DateValue(Format("1/" & lMth + 1 & "/" & Year(Now()), "dd/mm/yyyy")))
    Else
    'lTo = CLng(DateValue(Format("1/01/" & Year(Now()) + 1, "dd/mm/yyyy")))
    lTo = CLng(Application.WorksheetFunction.EoMonth(Date, -1))
    End If


    Many thanks and best wishes


    Chris

  • This works. I hadn't noticed how the dates were created for the first day of the month.

    I'll have a look tomorrow to see if I can shorten the code.

  • I went back to basics and just modified my original code to also prompt for the Year as input, defaulting to the current year.

    This allows the user to select a different year as required and will also just add '2000' to the year value entered for the current century eg '22' entered will be interpreted as '2022'. Otherwise they can simply press enter and accept the default year value.


    ExportToCSV v2.xlsm

  • Hi Gijsmo,


    My apologies for opening up an resolved thread. I am trying to use your workbook to create my own macro and I noticed that exporting data for the Dec 2022 does not work.


    I have tried to add data in Col C like so “12/12/2022” with data populated in the other columns as well. However, it still returns me “no value match the selected month”


    Could I trouble you to try and see whether it is indeed an error ?


    Thank you !

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!