Can macros be set to work before you have pasted in data?

[Kevin222]

Hello all, I am currently working on a sheet to help me see whether runners improve their running time over a number of races.

Thanks to a friend I now have a macro which I can use to change the pasted text into a numeric value - at present I have to copy and paste the data into my new master sheet and then select the column and then run the macro in order to convert it to a numeric value.....

So is it possible to sort of embed the macro into an empty column so that when you paste the data in it automatically changes as you paste it in from the other document?

Any help would be most gratefully received.

(ps. my excel knowledge is very limited!)

Thanks

 

[RoryA]

Welcome to the forum.

You can use a Worksheet_Change event which will run automatically when you change data on the sheet. It can be restricted to only apply when you change particular columns.

If you can post the macro you have now and indicate which columns should be changed, I'm sure we can amend it for you.

 

[Kevin222]

Thanks Rory,

The Macro is;

Sub FixFormat()
Dim sh As Worksheet, rng As Range, col As String
Set sh = Application.ActiveSheet
Set rng = Application.ActiveCell
col = Split(rng.Address, "$")(1)
sh.Range(col & "2:" & col & sh.UsedRange.Rows.Count).NumberFormat = "mm:ss.00"
sh.Range(col & "2:" & col & sh.UsedRange.Rows.Count).TextToColumns Destination:=sh.Range(col & "2")
End Sub

Column wise I would like it to work in columns C to L

Much thanks in advance!

 

[RoryA]

OK, it would be something like this:

private sub worksheet_change(byval target as range)
dim cell as range
if not intersect(target, range("C:L")) is nothing then
on error goto clean_up
with application
.screenupdating = false
.enableevents = false
end with
Range("C:L").NumberFormat = "mm:ss.00"
for each cell in intersect(target, range("C:L")).cells
if len(cell.value2) <> 0 then cell.value = cell.value
next cell

with application
.enableevents = true
.screenupdating = true
end with
end if
exit sub

clean_up:
application.enableevents = true
end sub

Note this code needs to go into the code module for the relevant sheet, so right-click its tab, choose View Code and then paste the code in there.

 

[Kevin222]

That is amazing!! Thankyou so much Rory - works a dream!

 

[RoryA]

Glad to help.