Hi all,
I have used answers on this site to help my Excel sheets for about 10 years now but finally need to post my specific problem as I cannot seem to tweak the things I see to work for me.
What I have is an workbased activity tracker eg. Calls made, calls answered, emails etc.
At the end of the day I have a macro that I click and this data is pasted in to a table on another sheet, I also have a failsafe to run the macro when I close excel in the event that I haven't copied the data over.
Currently the code is as follows (which I think I got from here);
This works great to look for the first empty cell at the bottom of the date column and insert the data.
Here is what I would like it to do:
1. IF there is already an entry for [date in cell F2 in sheet1] in Column1 (in sheet2) THEN pop up msgbox to ask if you would like to Overwrite the Data, [YES] - overwrites row with same date then exit the sub, [No] - writes data in new row, [Cancel] - Do nothing and Exit Sub.
2. ELSE Continue to have the macro insert the data in the first blank row.
Ideally the buttons on the msgbox would be "Overwrite Data" - "New entry" - "Cancel" but I think that would need a lot more code which would make it more complicated for my little brain to understand!
Thanks all.
I have used answers on this site to help my Excel sheets for about 10 years now but finally need to post my specific problem as I cannot seem to tweak the things I see to work for me.
What I have is an workbased activity tracker eg. Calls made, calls answered, emails etc.
At the end of the day I have a macro that I click and this data is pasted in to a table on another sheet, I also have a failsafe to run the macro when I close excel in the event that I haven't copied the data over.
Currently the code is as follows (which I think I got from here);
VBA Code:
Sub CopyData()
'
' CopyData Macro
'
'
Sheets("Sheet1").Select
Range("C21:L21").Select
Selection.Copy
Sheets("Sheet2").Select
Dim ws As Worksheet
Set ws = ActiveSheet
For Each cell In ws.Columns(1).Cells
If IsEmpty(cell) = True Then cell.Select: Exit For
Next cell
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
This works great to look for the first empty cell at the bottom of the date column and insert the data.
Here is what I would like it to do:
1. IF there is already an entry for [date in cell F2 in sheet1] in Column1 (in sheet2) THEN pop up msgbox to ask if you would like to Overwrite the Data, [YES] - overwrites row with same date then exit the sub, [No] - writes data in new row, [Cancel] - Do nothing and Exit Sub.
2. ELSE Continue to have the macro insert the data in the first blank row.
Ideally the buttons on the msgbox would be "Overwrite Data" - "New entry" - "Cancel" but I think that would need a lot more code which would make it more complicated for my little brain to understand!
Thanks all.