Select Current Date + 10 and export this data as a new csv

December 10, 2021

I am trying to create VBA code that will select the Date, Day and Gas Usage columns (as depicted below) for the current day and the following ten days (TODAY()+10) (as highlighted in red by the below picture). Where this range can be copied and exported into a new csv file.

The code below performs this action of extracting a range and exporting it as a new csv, however, it only selects a static range (A1:C10). How can I edit the code so that when the VBA is run it will select these columns for the next ten days on any given day that this code is run.

Sub CreateCSV()
 
Range("A1:C10").Select

Selection.Copy

Workbooks.Add

ActiveSheet.Paste

Application.CutCopyMode = False

ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\lach\Desktop\Test\data" & Format$(Now, "yyyyMMddhhmmss") & ".csv", FileFormat:= _
        xlCSV, CreateBackup:=False


End Sub

>Solution :

Assuming your days are sequential, without gaps, you can use Application.Match to find the date and Resize to select that cell plus the next 10 rows.

Dim r As Variant
' Find the date in column A
r = Application.Match(CLng(Date), Range("A:A"), 0)

' Only proceed if date has been found
If Not IsError(r) Then
   Dim wb As Workbook
   Set wb = Workbooks.Add()
 
   ThisWorkbook.Worksheets("SheetName").Cells(r, 1).Resize(11, 3).Copy _
       Destination:=wb.Worksheets(1).Range("A1")

   wb.SaveAs _
       Filename:= "C:\Users\lach\Desktop\Test\data" & Format$(Now, "yyyyMMddhhmmss") & ".csv", _
       FileFormat:= xlCSV, _
       CreateBackup:=False
End If