How to keep a log of "Usage of a macro"

[rbysetty]

Hello, 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 + 2
Dim infocell As Range
Set infocell = Cells(r, 1)
infocell.Select
ActiveSheet.Paste
infocell.Value = DHRSheet.Name & "$" & infocell.Value

DHRSheet.Select
ActiveWorkbook.Save

 

[mikerickson]

Instead of all this selecting, you could do something like:

Dim SourceRange as Range
Dim DestinationRange as Range

Set SourceRange = DHRSheet.Range("A1:A2")
Set DestinationRange = LogSheet.Cells(Rows.Count, "A").End(xlup).Offset(1,0)

With DestinationRange
    .Resize(2,1).Value = SourceRange.Value
    .Cells(1,1).Value = DHRSheet.Name & "$" & .Cells(1,1).Value
End With

The .End(xlUp) will get the last filled cell in LogSheet!A:A and .Offset(1,0) is the cell below that.

 

[wigi]

For example:

Sub test()

    With LogSheet
        With .Range("A" & .UsedRange.Rows.Count + 1)
            .Value = DHRSheet.Name & "$" & DHRSheet.Range("A1").Value
            .Offset(1).Value = DHRSheet.Range("A2").Value
        End With
        .Parent.Save
    End With


End Sub

 

[andrewkard]

Hello, try:

Sub test()
    Dim lLr&
    lLr = Worksheets("LogSheet").Cells(Rows.Count, "A").End(xlUp).Row
    Worksheets("LogSheet").Range("A" & lLr + 1 & ":A" & lLr + 2 & "") = _
    Worksheets("DHRSheet").Range("A1:A2").Value
End Sub

 

[JLGWhiz]

Hello, 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 + 2
Dim infocell As Range
Set infocell = Cells(r, 1)
infocell.Select
ActiveSheet.Paste
infocell.Value = DHRSheet.Name & "$" & infocell.Value

DHRSheet.Select
ActiveWorkbook.Save

I would replace what you have there with the code below:

Code:
Dim RowCount As Long
RowCount = LogSheet.Cells(Rows.Count, 1).End(xlUp).Row
DHRSheet.Range("A1:A2").Copy LogSheet.Range("A" & RowCount + 1)
Range("A" & RowCount + 1) = DHRSheet.Name & "$" & Range("A" & RowCount + 1).Value
ActiveWorkbook.Save
Code:

 

[rbysetty]

worked great, thanks !