Copy/Paste VBA Script Recorder Logs Wrong-CodePudding

A few issues: Script is linked to a form ctrl button that runs Update Data every minute. This runs Copy Data and copies row A39:T39 and pastes that row in the other sheet. That is the intent. But it doesn't paste right. Need to past in a row not a column starting w/ a time stamp on the other sheet in A2. Stop Recording Data is linked to a form ctrl button to cancel Update Data but that doesn't work either.

    Sub UpdateData()
     Application.OnTime Now   TimeValue("00:01:00"), "UpdateData"
     CopyData
    End Sub
    
    Sub CopyData()
     Dim sht1 As Worksheet, sht2 As Worksheet, cpyRng As Range, logRng As Long
    
     Application.StatusBar = "Recording Dashboard Started"
     Set sht1 = ThisWorkbook.Sheets("Dashboard")
     Set sht2 = ThisWorkbook.Sheets("Log")
     Set cpyRng = sht1.Range("A39:T39")
     logRng = sht2.Cells(2, Columns.Count).End(xlToLeft).Column   1
     sht2.Range("A2") = Now
     cpyRng.Copy sht2.Cells(2, logRng)
    End Sub
    
    Sub StopRecordingData()
     Application.StatusBar = "Recording Dashboard Stopped"
     Application.OnTime Now   TimeValue("00:01:00"), "UpdateData", False
    End Sub

CodePudding user response：

Put this code into an own module. To start logging, call StartRecordingData() and for stopping call StopRecordingData()

Option Explicit

Dim boolLoggingActive As Boolean
    
    Public Sub StartRecordingData()
     Application.StatusBar = "Recording Dashboard Started"
     boolLoggingActive = True
     UpdateData
    End Sub
    Public Sub StopRecordingData()
     Application.StatusBar = "Recording Dashboard Stopped"
     boolLoggingActive = False
    End Sub

    Private Sub UpdateData()
        If boolLoggingActive = True Then
            Application.OnTime Now   TimeValue("00:01:00"), "UpdateData"
            CopyData
        End If
    End Sub
    
    Private Sub CopyData()
     Dim sht1 As Worksheet, sht2 As Worksheet, cpyRng As Range, logRng As Long
    
     Application.StatusBar = "Recording Dashboard Started"
     Set sht1 = ThisWorkbook.Sheets("Dashboard")
     Set sht2 = ThisWorkbook.Sheets("Log")
     Set cpyRng = sht1.Range("A39:T39")
     
     Dim rngLogTargetBeginningCell As Range
     Set rngLogTargetBeginningCell = sht2.Rows(sht2.Rows.Count).Columns(1).End(xlUp).Offset(1, 0)
     
     rngLogTargetBeginningCell = Now
     Dim rngLastCellSelection As Range
     
     Application.ScreenUpdating = False ' Stop Updating Graphic during data copy
     Set rngLastCellSelection = Selection ' remember the last selection because pasting will change the active cell
     cpyRng.Copy
     rngLogTargetBeginningCell.Offset(0, 1).PasteSpecial xlPasteValues
     Application.CutCopyMode = False ' Remove the copy area marker
     rngLastCellSelection.Select    ' reselect the old cell
     Application.ScreenUpdating = True  ' update graphics again
    End Sub