How to copy and paste the last row until a certain date?

I am fairly newer to VBA. I took a class in college and that has been years now since using it. I am trying to copy the last row in a spreadsheet within columns A through S multiple times. I am needing to do this from the date specified in the last cell in column A until the date I specify in cell A1. This is going to be a report I update weekly is why I am trying to do this. I am needing to copy all of the formulas in the last row.

Public Sub CopyLastRow()
    
    ' Define the source sheet (no need to activate it)
    Dim sourceSheet As Worksheet
    Set sourceSheet = ThisWorkbook.Worksheets("Steam Data")
    
    ' Find the last row in an specific column
    Dim lastRow As Long
    lastRow = sourceSheet.Range("A" & sourceSheet.Rows.Count).End(xlUp).Row
    
    ' Set the source range according to columns and last row
    Dim sourceRange As Range
    Set sourceRange = sourceSheet.Range("A" & lastRow & ":S" & lastRow)
   
   Dim x As Integer
   Dim lcell As Long
   
   lcell = Cells(Rows.Count, 1).End(xlUp).Row

   For x = lcell To x = Range("A1")
   
   If x < Range("A1") Then
    sourceRange.Copy
    sourceRange.Offset(1).PasteSpecial
    Else
    
    End If
   
  Next x
End Sub

I got this to work as a simple copy and paste. But once I did the if statement, it just does nothing with no error. Any idea what I'm doing wrong?

If I understood your last comment, the code below will do what you need. It can go into a module or the code section of the "Steam Data" worksheet. The "Steam Data" worksheet does not need to be active to run the code:

Public Sub CopyLastRow()

  Dim ws             As Worksheet, _
      rngSourceRange As Range, _
      rngTargetRange As Range, _
      lngLastRow     As Long, _
      lngRowsToAdd   As Long, _
      dteTargetDate  As Date, _
      dteLastDate    As Date

  ' Define the sheet we want to work with
  Set ws = ThisWorkbook.Worksheets("Steam Data")

  ' Establish the target date
  ' This will be your 'specified date' and can be a cell reference, a parameter
  ' passed into this procedure, or any other possible source. I have just hardcoded
  ' a date here as an example.
  dteTargetDate = #6/24/2023#

  With ws
    ' Determine the last row in Column A
    lngLastRow = .Range("A" & .Rows.Count).End(xlUp).Row

    ' Make sure we have found a valid date. If not then show a message and get out.
    If IsDate(.Cells(lngLastRow, 1)) = False Then
      MsgBox "The last row does not contain a valid date. Process aborted."
      Exit Sub
    End If

    ' Calculate the number of rows (days) we need to add
    lngRowsToAdd = dteTargetDate - .Cells(lngLastRow, 1)

    ' Define the source and target ranges
    Set rngSourceRange = .Range("A" & lngLastRow)
    Set rngTargetRange = .Range("A" & lngLastRow & ":A" & lngLastRow + lngRowsToAdd)

    ' Copy the selected date to the target range with the 'Fill Series' option
    rngSourceRange.AutoFill Destination:=rngTargetRange

    ' Select and copy the columns from the last row that we want to repeat
    .Range("B" & lngLastRow & ":S" & lngLastRow).Copy

    ' Paste the rows
    .Paste Destination:=.Range("B" & lngLastRow + 1 & ":B" & lngLastRow + lngRowsToAdd)

  End With

  Set ws = Nothing

End Sub

For further info, I am not sure if you are familiar with the Record Macro option under the Developer Tab. This is a very useful tool for generating bare-bones code based on your keystrokes. The resulting code can be somewhat verbose with steps that may not be necessary but this can be cleaned up.

Copy (Append) Last Row

A Quick Fix (Preserving the Idea)

<!-- language: lang-vb -->

Sub CopyLastRowQF()
    
    ' Define the source sheet (no need to activate it)
    Dim sourceSheet As Worksheet
    Set sourceSheet = ThisWorkbook.Worksheets("Steam Data")
    
    ' Find the last row in a specific column
    Dim lastRow As Long
    lastRow = sourceSheet.Range("A" & sourceSheet.Rows.Count).End(xlUp).Row
    
    ' Set the source range according to columns and last row
    Dim sourceRange As Range
    Set sourceRange = sourceSheet.Range("A" & lastRow & ":S" & lastRow)
   
    Dim lCell As Range
    Set lCell = sourceRange.Cells(1)
    
    Dim destRange As Range
    Set destRange = sourceRange.Offset(1)
    
    Application.ScreenUpdating = False

    Dim x As Long
    
    For x = lCell.Value + 1 To sourceSheet.Range("A1").Value
        sourceRange.Copy destRange
        Set destRange = destRange.Offset(1)
    Next x

    Application.ScreenUpdating = True

End Sub

<!-- language: lang-vb -->

An Improvement

Sub CopyLastRow()
    
    Const DATE_COLUMN As Long = 1
    
    With ThisWorkbook.Worksheets("Steam Data").Columns("A:S")
        Dim lCell As Range: Set lCell = .Columns(DATE_COLUMN) _
            .Find("*", , xlFormulas, , , xlPrevious)
        If lCell Is Nothing Then Exit Sub ' no data in column
        Dim rCount As Long: rCount = .Cells(DATE_COLUMN).Value - lCell.Value
        If rCount < 1 Then
            MsgBox "Rows already copied.", vbExclamation
            Exit Sub
        End If
        Dim srg As Range: Set srg = Intersect(lCell.EntireRow, .Cells)
        Dim drg As Range: Set drg = srg.Offset(1).Resize(rCount)
        srg.Copy drg
    End With

End Sub

The last part of your code needs to be adjusted by removing the For ... Next loop (which is improperly constructed to begin with) and removing the If ... Else ... End If. See my code snippet below:

   Dim x As Integer
   Dim lcell As Long
   
   lcell = Cells(Rows.Count, 1).End(xlUp).Row

'   For x = lcell To 14   'x = Range("A1")
   
'   If x < Range("A1") Then
    sourceRange.Copy
    sourceRange.Offset(1).PasteSpecial
'    Else
    
'    End If
   
'  Next x