How to keep a log of usage of a macro

Question

I have a rather silly problem. I have a macro (linked to a button) which copies cells A1:A2 from one worksheet (namedFP) to another worksheet (Log). I intend to copy these 2 cells on the log sheet every time I hit the macro button. The problem I am facing right now is that when I use the button multiple times, these cells are getting copied over each other instead of using the next available row to paste the cells.

This is what I have now, and I tried changing the 'Rowcount+1' to 'RowCount+2' but that did not work. Any help is appreciated.

DHRSheet.Select
 Range("A1:A2").Select
 Selection.Copy

 LogSheet.Select
 RowCount = LogSheet.UsedRange.Rows.Count
 Dim r As Integer
 r = RowCount + 1
 Dim infocell As Range
 Set infocell = Cells(r, 1)
 infocell.Select
 ActiveSheet.Paste
 infocell.Value = DHRSheet.Name & "$" & infocell.Value

 DHRSheet.Select
 ActiveWorkbook.Save

To get the last row, see this link: http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba Can you also show how have you declared LogSheet — Siddharth Rout, Jul 09 '12 at 14:28
This is a minor point, but you should wrap the sheet name in single quotes `'`. If it has spaces, the sheet name will be wrapped accordingly. Otherwise, the result is unpredictable. If there are no spaces in the sheet name, Excel will happily drop the single quotes. — JimmyPena, Jul 09 '12 at 18:28

danielpiestrak · Answer 1 · 2012-07-09T15:02:46.653

Here's a function I use that is very reliable and always returns the last row of a sheet without fail: (possibly excessive for your simple use, but I always recommend it)

Public Function LastRowOfSheet(ByVal TestSheetNumber As Variant)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'   Input: Sheet index # or Sheet name
'   Output: Last row of sheet.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim intNumberOfRowsInWorksheet As Long

    intNumberOfRowsInWorksheet = Sheets(TestSheetNumber).UsedRange.Rows.Count
    intNumberOfRowsInWorksheet = intNumberOfRowsInWorksheet +     Sheets(TestSheetNumber).UsedRange.Row - 1

    LastRowOfSheet = intNumberOfRowsInWorksheet
End Function

And I'd clean up your above code and use something like this:

Sub Move2RowsToEnd()

    Dim iNextRowOfOutput As Long
    Dim iRowNumber As Long
    '- use the function to find the last row of the output sheet. we'll be pasting to the first row after.
    iNextRowOfOutput = (LastRowOfSheet("Log") + 1)

    '- you can adjust this for loop to loop through additional cells if you need to paste more than 2 rows in the future.
    For iRowNumber = 1 To 2
        '- for each row of input (2 total) set the value of the output sheet equal to it.
        Sheets("Log").Range("A" & iNextRowOfOutput).Value = Sheets("namedFP").Range("A"     & iRowNumber).Value
        iNextRowOfOutput = iNextRowOfOutput + 1
    Next iRowNumber

    '- not sure which of these you want to save (one or both)
    Sheets("namedFP").Save
    Sheets("Log").Save

End Sub

Just paste the function above or below the Subroutine and let me know if you have any issues or questions regarding the 'Move2RowsToEnd' code.

score 0 · Answer 2 · answered Jul 09 '12 at 14:36

Is this what you are trying?

Sub Sample()
    Dim LogSheet As Worksheet, DHRSheet As Worksheet
    Dim lrow As Long

    '~~> Change this as applicable
    Set LogSheet = Sheets("Sheet1")
    Set DHRSheet = Sheets("Sheet2")

    With LogSheet
        lrow = LogSheet.Range("A" & .Rows.Count).End(xlUp).Row + 1

        DHRSheet.Range("A1:A2").Copy .Range("A" & lrow)
    End With
End Sub

How to keep a log of usage of a macro

2 Answers2