0

I have written a VBA code which copies data from a pivottable into another worksheet in order to store this data. The data is monthly and counts the amount of devices, this is needed to create a historical database.

Sub DataTransferv1()

 Dim lastrow As Long, erow As Long

'Stamp from when the data set is (in months) Worksheets("Database").Select
    Worksheets("Database").Range("A3").Select

        If Worksheets("Database").Range("A3").Offset(1, 1) <> "" Then
            Worksheets("Database").Range("A3").End(xlDown).Select
             ActiveCell.FormulaR1C1 = "=NOW()"
        End If


'To check the last filled line on sheet 'Database_Input' lastrow = Sheet12.Cells(Rows.Count, 1).End(xlUp).Row

'Copy Paste section For i = 2 To lastrow
    Sheet12.Cells(i, 1).Copy
        erow = Sheet14.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            Sheet12.Paste Destination:=Worksheets("Database").Cells(erow, 1)
        Sheet12.Cells(i, 2).Copy
    Sheet12.Paste Destination:=Worksheets("Database").Cells(erow, 2) Next i


End Sub

The problem with this code, it keeps paste a date stamp in the last used cell (So it overwrites the last row of copied data). How can I fix this as I don't see it anymore. Also, what VBA formula can be used for a static date stamp, in the regular excel its Shift + ; but I cant find the formula for this.

Thank you very much!

BigBen
  • 46,229
  • 7
  • 24
  • 40
ThisMatthijs
  • 3
  • 5
  • 1
    Instead of the formula `=NOW()`, use the VBA `Now`. Then use `Offset` perhaps to write into the next row, instead of the last used row. – BigBen Oct 22 '19 at 13:51
  • Hi BigBen, Thank you, your input did the job :), so simple, yet I lost overview as I am very new to VBA coding (Trying to understand it) – ThisMatthijs Oct 22 '19 at 14:00
  • Side note: in general, you want to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code – cybernetic.nomad Oct 22 '19 at 14:43

0 Answers0