0

I'm new to creating Macros in Excel and have been trying to understand VBA by taking apart Macros others have made.

What I'm trying to create right now is a Start/Stop timer of sorts to measure how much time is spent on different projects and on different tasks within projects.

I want my user to select the project (from a drop down I've created located in A2) and task type (also from a drop down, located in B2) and then click a button "Start" (all on Sheet 1). This will then run a macro to transfer those two inputs to the next available row on Sheet 2 (columns A & B) and then input the current date (column C) and current time (column D). When they're done working they then click another button "Stop" (also on sheet 1) to insert the current time onto Sheet 2 Column E (same row as data used for "start").

So it needs to:

Click on the Start Button

  • Copy the A2 and B2 Values to the next blank row in Sheet 2
  • In Column C of that same row input the current date
  • In column D of that same row input the current time

Click on the Stop Button

  • In (the same row as the other data just added) in column E input the current time

  • Or last D row that has a value input current time in E

I tried using this:

'Start Button

'Determine emptyRow

ActiveWorkbook.Sheets("Sheet2").Select

emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1


'Transfer information

 Cells(emptyRow, 1).Value = A2.Value

 Cells(emptyRow, 2).Value = B2.Value

 Cells(emptyRow, 3).Value = Today().Value

 Cells(emptyRow, 4).Value = Time().Value

(Different Macro)

'Stop Button

'Make Sheet2 active

 Sheet2.Activate

'Determine emptyRow

ActiveWorkbook.Sheets("Sheet").Select

emptyRow = WorksheetFunction.CountA(Range("E:E")) + 1


'Input Stop Time

 Cells(emptyRow, 5).Value = Time().Value

And using this:

'Start Button

Sheets(“Sheet1”).Select

Range(“A2”).Copy

Sheets(“Sheet2”).Select

emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

ActiveSheet.Paste

They didn't error but they didn't work either so I know I'm missing something.

  • Formatting would greatly help...i don't understand why you need a timer.what would happen if you didn't click stop when you do in your example? – Dave Aug 29 '16 at 20:20
  • 1
    "They didn't error but they didn't work either" - what they do? Your question is very vague now. Please debug your code and let us know where is behaves wrongly. – Máté Juhász Aug 29 '16 at 20:22
  • This set of code didn't turn red in the edit window and didn't result in the "error/debug" message. When I run it though (click the button it's attached to) it doesn't do what I expect it to do (copy/paste). As far as I can tell it doesn't do anything to any of the sheets. – Mel Aug 31 '16 at 12:55
  • The overall purpose is to log how much time is spent on different projects and within those projects how much time is spent on different tasks. By using the start and stop buttons a running log is created of start/stop times for projects/tasks. This data then feeds into another sheet (that is "traditional" formula based) that totals all the data points into a summary based on project/task. – Mel Aug 31 '16 at 13:09
  • If someone doesn't click the stop button at the end, that data point (row) becomes "useless" (or will have to be manually manipulated later).That's why E (stop time) should probably look to last row with a value in D (start time) as opposed to "next empty cell in E". If I can get the basic set-up to work I could always add something in later to pop-up a message if you try to close the workbook or hit the start button again without first hitting stop (this isn't as important though). – Mel Aug 31 '16 at 13:09
0

I did some more reading and tinkering and this may not be the best way but it works for what I need the Start Button to do.

Private Sub CommandButton1_Click()

Dim lst As Long
Sheets("Sheet1").Range("A2:B2").Copy
With Sheets("Sheet2")
    lst = .Range("A" & Rows.Count).End(xlUp).Row + 1
    .Range("A" & lst).PasteSpecial xlPasteColumnWidths
    .Range("A" & lst).PasteSpecial xlPasteValues
End With


With Sheets("Sheet2")
    lst = .Range("C" & Rows.Count).End(xlUp).Row + 1
    .Range("C" & lst).Value = Date
End With

With Sheets("Sheet2")
    lst = .Range("D" & Rows.Count).End(xlUp).Row + 1
    .Range("D" & lst).Value = Time()
End With


End Sub

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.