Auto-copying Data between worksheets with code not formula?

[zoso]

I'd be really grateful if someone can help me here, as my spreadsheet is my inspiration for my running!

I have a worksheet (Sheet 1) called Training Log and another worksheet (sheet 8) in the same book called 90R Data. I enter a new row of data in the first empty row in Training Log after I've been out running every day (I'm a very keen runner!!!).

What I would really appreciate are the following 2 things please:

1. The data in Columns A, C & E of Training Log needs to be copied automatically into the first empty rows in Columns A, B & C respectively in 90R Data as soon as I've entered the data in Training Log.

2. The data being copied from Column E into Column C needs to be converted slightly, as I need the format in Training Log to stay as m:ss but when appearing in 90R Data, the ':ss' part needs to be converted to a decimal.

You might be wondering why I've not simply used a formula in rows A,B & C in 90R Data to do this. The reason is that cells for future entries need to be empty because they're linked to a chart showing my last 90 days runs and zero values would upset this - unless you know of a way round it.

3. I have minor niggle with the format of Column E in Training Log. This is conditionally formatted so that if I run below a certain pace the cell changes colour. Only trouble is, all the rows that are empty are showing this formatting as if the pace is zero. Can you tell me how to show the empty cells as normal but still retaining the conditional formatting when they are filled?

I'd be really grateful if there's anything you can do to help!

Thanks!

 

[zilpher]

Re: Auto-copying Data between worksheets with code not formu

Here's a starter for ten on the first point:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strAddress As String
If Len(Target.Value) > 0 Then

Select Case Target.Column
Case 1 'we are in column a
strAddress = "a65536"
Case 3 'we are in column c
strAddress = "b65536"
Case 5 'we are in column e
strAddress = "c65536"
Case Else 'we are in any other column, do nothing
Exit Sub
End Select

Target.Copy
Sheet8.Range(strAddress).End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
Application.CutCopyMode = False

End If
End Sub

For the conditional formatting, you could try adding the format when the cell is filled.

Z

 

[zoso]

Re: Auto-copying Data between worksheets with code not formu

Thanks for your help, Z!

I pasted your code into the Sheet 1 module but got the following error msg:

Error 13 - Type Mismatch

Clicked Debug and the following line was highlighted:

If Len(Target.Value) > 0 Then

Any ideas?

TIA!

 

[zilpher]

Re: Auto-copying Data between worksheets with code not formu

I'm confused by this, assuming Target is a range object (it has to be, it's the worksheet_selectionchange event).

What version of Excel are you using?
What's on the worksheet?
What did you select when it did this?

Sorry for the questions, I cannot replaicate the behaviour you describe

Z

 

[zilpher]

Re: Auto-copying Data between worksheets with code not formu

And then I found out by mistake! Did you select more than one cell?

If so, this'll help:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strAddress As String
If Target.Cells.Count > 1 Then Exit Sub
If Len(Target.Value) > 0 Then

Select Case Target.Column
Case 1 'we are in column a
strAddress = "a65536"
Case 3 'we are in column c
strAddress = "b65536"
Case 5 'we are in column e
strAddress = "c65536"
Case Else 'we are in any other column, do nothing
Exit Sub
End Select

Target.Copy
Sheet8.Range(strAddress).End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
Application.CutCopyMode = False

End If
End Sub

I added a condition on the size of the selection, if it's more than one cell, don't bother.

Hope this is it

Z