Auto-copying Data between worksheets with code not formula?

zoso

Well-known Member
Joined
Oct 23, 2003
Messages
725
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!

:rolleyes:
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

zilpher

Active Member
Joined
Mar 27, 2003
Messages
442
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

Well-known Member
Joined
Oct 23, 2003
Messages
725
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

Active Member
Joined
Mar 27, 2003
Messages
442
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

Active Member
Joined
Mar 27, 2003
Messages
442
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,820
Messages
5,772,462
Members
425,760
Latest member
paphon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top