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

Sub ExportToCSV()
Dim wsOld As Worksheet
Dim wsNew As Worksheet
Dim vFile
Dim lr As Long, r As Long

Set wsOld = ActiveSheet
Set wsNew = Workbooks.Add(xlWorksheet).Sheets(1)
With wsOld
'Copy Headers
wsOld.Range("C1:D1").Copy wsNew.Range("A1:B1")
wsOld.Range("F1:G1").Copy wsNew.Range("C1:D1")
wsOld.Range("H1:H1").Copy wsNew.Range("E1:E1")
wsOld.Range("K1:K1").Copy wsNew.Range("F1:F1")
wsOld.Range("O1:P1").Copy wsNew.Range("G1:H1")
'Copy relevant rows and columns
    lr = .Cells(Rows.Count, "C").End(xlUp).Row
    For r = lr To 2 Step -1
        If IsDate(.Range("C" & r).Value) Then
            If Month(.Range("C" & r).Value) = Month(Now()) And Year(.Range("C" & r).Value) = Year(Now()) Then
                wsOld.Range("C" & r & ":D" & r).Copy wsNew.Range("A" & r & ":B" & r)
                wsOld.Range("F" & r & ":G" & r).Copy wsNew.Range("C" & r & ":D" & r)
                wsOld.Range("H" & r & ":H" & r).Copy wsNew.Range("E" & r & ":E" & r)
                wsOld.Range("K" & r & ":K" & r).Copy wsNew.Range("F" & r & ":F" & r)
                wsOld.Range("O" & r & ":P" & r).Copy wsNew.Range("G" & r & ":H" & r)
            End If
        End If
    Next r

'Save as CSV
vFile = Application.GetSaveAsFilename(FileFilter:="CSV files (*.csv),*.csv")
If TypeName(vFile) = "Boolean" Then Exit Sub 'cancelled

Application.DisplayAlerts = False
wsNew.SaveAs vFile, FileFormat:=xlCSV, Local:=True
Application.DisplayAlerts = True
wsNew.Parent.Close False
End With

End Sub

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

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?

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?

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

Have a look at this...

Thanks gijsmo. Very useful and well explained,