dde data copied from sheet1 to sheet2

[chuckb74]

I am enlcosing a file that I partially acquired (the macro portion) and I am trying to add to that.

The workbook has a dde link in sheet 1, the macro will write the data to sheet2 at set times. I am creating a report of the data in sheet 2 in sheet3. at this point it is very rudimentary. I need some assistance to clean up the macro and the post of the dat in sheet2. I have created a detailed (probably over detailed) explanantion in sheet 1 in a text box.

Thanks for any and all assistance given as my programming/excel skills are very limited.

Thanks
cb

[Leith Ross]

Hello Chuckb74,

Welcome to the Forum!

I revised your macro The_Sub. It nows checks if cell "A1" on "Sheet2" has any data. If not, this is first cell the data is pasted into. Otherwise it will use the next empty cell in column "A".

Sub The_Sub()
'MsgBox ""
'
' your code here
'
Dim LastRow As Long

  If Time < TimeValue("09:30:00 AM") Then Exit Sub
  If Time > TimeValue("03:30:00 PM") Then Exit Sub

  With Worksheets("sheet1")
   '.Range("a1:d1").Copy
    .Range("A1").Copy

   'Start in cell A1
    With Worksheets("Sheet2")
      LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
      If LastRow = 1 And .Cells(1, "A") = "" Then
         .Cells(1, "A").PasteSpecial Paste:=xlPasteValues
      Else
         .Cells(LastRow + 1, "A").PasteSpecial Paste:=xlPasteValues
      End If
    End With
    
  End With
  
  Application.CutCopyMode = False

  StartTimer

End Sub

[chuckb74]

Thanks so much for taking the time out to fix that.

Will that also fix the overwriting of the entire line instead of just the cell or can that be stopped?

Thanks
cb

[Leith Ross]

Hello CB,

It only copies cell "A1" from "Sheet1" over to "Sheet2". The headers in column "B" on are safe.

[chuckb74]

Leith,
Thanks so much for that. How difficult would it be to write a date/time stamp in the corresponding cell in column b on the data that is being written into sheet2? That would get rid of my labels and it would allow me to double check the time the data is being written into the file.


Thanks Very much again.

Sincerely,
cb

[shg]

Maybe like this:

Sub The_Sub()
    '
    ' your code here
    '
    Dim LastRow     As Long

    If Time < #9:30:00 AM# Or Time > #3:30:00 PM# Then Exit Sub

    With Worksheets("Sheet2")
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        If LastRow <> 1 Or .Cells(1, "A") <> Empty Then LastRow = LastRow + 1
        .Cells(LastRow, "A").Resize(, 2).Value = Array(Worksheets("Sheet1").Range("A1").Value, Now)
    End With

    StartTimer
End Sub