VBA - If date is today than

[KirovHC]

Hello,
I have to be honest: I am quite new to VBA and I copied a macro trying to adapt it to my needs.
My problem: in column A there is the whole year, in the next cells there are a series of data.
I need to copy the cells next to today's date to another sheet.

Sub selectdata()

Dim nextrow As Long
   For Row = 4 To 368
        If Worksheets("SHEET1").Cells(Row, 1).Value = Date Then
           Worksheets("SHEET1").Cells(Row, 1).Copy
            Worksheets("SHEET2").Activate
           nextrow = Range("A65536").End(xlUp).Row
           Cells(nextrow, 1).Select
            ActiveSheet.Paste
       End If
        
    Next Row
    
Worksheets("SHEET1").Select
End Sub

In this case I copy the specific cell, how can I set a range of cells instead?

 

[MARK858]

how can I set a range of cells instead?

What range?

 

[KirovHC]

What range?

I need to copy the 4 cells next to the date

 

[MARK858]

If you mean including the cell in column A then try the code below (untested)

Sub selectdata()
    Dim myRow As Long

    Application.ScreenUpdating = False

    For myRow = 4 To 368
        If Worksheets("SHEET1").Cells(myRow, 1).Value = Date Then _
             Worksheets("SHEET1").Cells(myRow, 1).Resize(, 5).Copy _
                 Worksheets("SHEET2").Cells(Rows.Count, "A").End(xlUp)(2)
    Next myRow
    
    Application.ScreenUpdating = True

End Sub

If the code is too slow for you then we can use the autofilter which will be quicker.

 

[KirovHC]

If you mean including the cell in column A then try the code below (untested)

Sub selectdata()
    Dim myRow As Long

    Application.ScreenUpdating = False

    For myRow = 4 To 368
        If Worksheets("SHEET1").Cells(myRow, 1).Value = Date Then _
             Worksheets("SHEET1").Cells(myRow, 1).Resize(, 5).Copy _
                 Worksheets("SHEET2").Cells(Rows.Count, "A").End(xlUp)(2)
    Next myRow
   
    Application.ScreenUpdating = True

End Sub

If the code is too slow for you then we can use the autofilter which will be quicker.

No, it works correctly. Thanks so much!
I take advantage of your kindness to ask you: what if I then later want to copy, for example, the sixth cell next to the date in a specific cell of another sheet?

 

[MARK858]

If you mean as a continuous range from column A to F then you would just change the 5 in the resize to a 6.
If you mean as a single cell then you would do

Worksheets("SHEET1").Cells(myRow, 1).Offset(, 5).Copy

 

[KirovHC]

If you mean as a continuous range from column A to F then you would just change the 5 in the resize to a 6.
If you mean as a single cell then you would do

Worksheets("SHEET1").Cells(myRow, 1).Offset(, 5).Copy

Now I have copied the use of resize!
I intend to copy a cell on another sheet but in a cell of my choice (example C3, not on the first row as in this case)

 

[MARK858]

Copying to a cell...

Worksheets("SHEET1").Cells(myRow, 1).Offset(, 5).Copy Worksheets("SHEET2").Range("C3")

or

Worksheets("SHEET1").Cells(myRow, 1).Offset(, 5).Copy Worksheets("SHEET2").Cells(3, "C")

or if you don't need the formatting copied

Worksheets("SHEET2").Range("C3").Value = Worksheets("SHEET1").Cells(myRow, 1).Offset(, 5).Value

 

[KirovHC]

Copying to a cell...

Worksheets("SHEET1").Cells(myRow, 1).Offset(, 5).Copy Worksheets("SHEET2").Range("C3")

or

Worksheets("SHEET1").Cells(myRow, 1).Offset(, 5).Copy Worksheets("SHEET2").Cells(3, "C")

or if you don't need the formatting copied

Worksheets("SHEET2").Range("C3").Value = Worksheets("SHEET1").Cells(myRow, 1).Offset(, 5).Value

Many thanks!!