Loop through dates to copy specific columns

Question

I collated data to see my colleagues' shifts at work.

Sheet1 holds a table showing Shifts. These shift times are formulated. I am trying to copy and paste shift data as values based on today's date.

For example, if today is 31/12/2020 then copy range C3:F10 and paste as values (i.e. data preceding this date). When the date changes to the next day the range then changes from C3:F10 to C3:G10. This I am going to have continuous (a column for each day of the year)

This is what I have so far. I need some direction.

Private Sub test()
    For i = c1 To h1
        If Worksheets("Sheet1").Cells(i, 1).Value <= Date Then
            Worksheets("Sheet1").Column(i).Copy
            ActiveSheet.pastevalues
        End If
    Next
End Sub

user_1140579 · Answer

Is this what you are trying? I have commented the code so you should not have a problem understanding it. But if you do then simply ask.

Code:

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim i As Long
    Dim lCol As Long
    Dim lRow As Long
    Dim rng As Range
    
    '~~> Change this to the relevant sheet
    Set ws = Sheet1
    
    With ws
        '~~> Find last column which has data in row 1
        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        '~~> Find last row which has data in Col A
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        '~~> Loop through the dates
        For i = 3 To lCol
            '~~> Check if the date is less than today's date
            If .Cells(1, i).Value2 < Date Then
                '~~> identify your range
                If rng Is Nothing Then
                    '~~> Row 3 to last row
                    Set rng = .Range(.Cells(3, i), .Cells(lRow, i))
                Else
                    '~~> Joining ranges
                    Set rng = Union(rng, .Range(.Cells(3, i), .Cells(lRow, i)))
                End If
            Else
                Exit For
            End If
        Next i
        
        '~~> Convert to values in one go
        If Not rng Is Nothing Then rng.Value = rng.Value
    End With
End Sub

In Action:

I created a sample file to test the above code.

Loop through dates to copy specific columns

1 Answers