Macro to paste values and the divide by 86400

[jontyoc]

Hi All
Using Excel 2007 and not wonderful with VBA (but hoping to get better!).

I am creating a spreadsheet to analyse data for AHT times and need the data in [h]:mm:ss format.
I have formatted the cells to this but when I paste the data in it is in seconds only. I know I have to divide by 86400 to fix this.
The data I am posting in would post into cell A4 and cover two rows a4:q4 & a5:q5.
I am wanting the macro to paste the data, and then divide only cells b5:m5 by 86400 - (in cell a1)

I have tried to do this myself, after a little digging on here, and have the following:
Sub Macro7()
'
' Macro7 Macro
'
'
Sheets("Raw Data").Select
Range("A4").Select
ActiveSheet.Paste
Range("A1").Select
Selection.Copy
Range("B4:M5").Select
Range("M5").Activate
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlDivide, SkipBlanks _
:=False, Transpose:=False
End Sub

But I get error 1004 - worksheet class failed.

I am hoping once I get this one sorted I can then repeat, with new data and new macro, to post in a7, a10, a13 etc down to a49.

Thanks

 

[pgc01]

Hi

You don't usually use Select in vba, it's inefficiet and makes the code difficult to read.

Anyway, answering to:

... and then divide only cells b5:m5 by 86400 - (in cell a1)

You can use:

With Sheets("Raw Data")
    .Range("A1").Copy
    .Range("B5:M5").PasteSpecial Paste:=xlPasteAll, Operation:=xlDivide
End With

 

[jontyoc]

Thanks very much. Simples!
Problem solved.

Trying to come at the macro as I would do it, that why select and copy etc in.

 

[jontyoc]

Just another little question....

What am I missing to make it stay on the input screen and not move to Raw Data worksheet?

 

[pgc01]

What am I missing to make it stay on the input screen and not move to Raw Data worksheet?

If I understand correctly, the only thing you do in the macro before the division is to paste whatever is in the clipboard into 'Raw Data'!A4.

This is equivalent to your macro without the select's:

Sub Macro7()

With Sheets("Raw Data")
    .Range("A4").PasteSpecial xlPasteAll
    .Range("A1").Copy
    .Range("B5:M5").PasteSpecial Paste:=xlPasteAll, Operation:=xlDivide
End With

End Sub

 

[jontyoc]

Now have all that sorted. But this keeps getting bigger and more wanted. Wish I had not been at my desk when this was handed out!

What I have now been asked to do is collate a running monthly total for the stats.
So my thoughts were that each macro would (if possible) paste the data into the agents sheet.
The raw data is two rows wide but I only need one row pasting into the agent sheet - the raw data is a total sheet and a daily figure.

So....
what I am wondering is if I can change the macro given to suit purposes.
These are my steps:
1 - paste data as per initial macro into A4 (takes up A4:Q5)
2 - copy A1 and divide B5:M5 by A1
New bits:
3 - copy b5:q5 and paste into correct date (a5 on raw data sheet shows date), or next empty row if not able.
4 - save the data so that next day I can repeat and add daily.

I had thought about vlookup but as I will be pasting fresh data into A4,A7 etc daily I didnt think this would work.
Any help greatly appreciated....

 

[jontyoc]

Hi All

Sorry to bump this back up but thought better to do this than create new post.
I am using the following macro - amended from earlier posts - to paste data in and I want to divide H2:k23 by 86400 (Cell M1)

Sub Macro1()
With Sheets("Sheet1")
.Range("A2").PasteSpecial xlPasteAll
.Range("m1").Copy
.Range("h2:k23").PasteSpecial Paste:=xlPasteAll, Operation:=xlDivide
End With
End Sub

My problem is I need the original data to paste in A2 and it doesnt - it pastes in H2.
The data is 21 columns wide.

Thanks for any help?