Any value greater than zero, copy , paste values only

[Bdbd55]

Hello,
I am new to VBA. I thought this macro made sense but there are errors. How do I right an IF in VBA that takes any value in the sheet above 0 and only copies and pastes it's values. Please help!


Sub Sample()

Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("US Averages")

If score > 0 Then

With ws.UsedRange
.Copy
.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

End With
End If
End Sub

 

[Joe4]

Welcome to the Board!

Copies and pastes the value to where?
Over top of itself, or paste it somewhere else (if somewhere else, where)?

 

[Joe4]

If you just want to paste the value over top of itself, then you can just do this:

Sub Sample()

Dim ws As Worksheet
Dim cell As Range

Set ws = ThisWorkbook.Sheets("US Averages")

For Each cell In ws.UsedRange
    If cell.Value > 0 Then cell.Value = cell.Value
Next cell

End Sub

]

 

[Bdbd55]

Hello,

Thank you for the fast response. And glad to be here

Sorry for not being clear. I would like any value greater than zero to copy and paste itself (over itself) as a value without the formula.

Once my other files feed into this and reports the averages for a day in the week it will just give me the numeric values above zero and I do not want the formula to follow.

I hope this makes sense

 

[Joe4]

The code I pasted above should do what you want then.

 

[Bdbd55]

You are right, thank you so much!
Is there a way to make this run automatically or when the file is opened. Sorry if these are novice questions.

 

[Bdbd55]

Preferably refreshed every weekday for 2020 at 6pm eastern time automatically. Is that possible?,

 

[Joe4]

Preferably refreshed every weekday for 2020 at 6pm eastern time automatically. Is that possible?,

Only if Excel and the file was always guaranteed to be open at this time (or you could schedule a job on a server to fire it off and run at this day/time).

Otherwise, you could make Excel run it every time the file was opened (it wouldn't hurt to run it more often than needed). If you don't want it to run more than once a week, you can have Excel log when it last ran (in some unused cell), and then have the job first check to see when it last ran before deciding whether or not to run again when the file opens.

Please let us know which option you would like to pursue.

 

[Bdbd55]

I would say it would be best for it to run every weekday during 2020 at 6pm est. If it is not too much the job on the server would make the most sense. Thanks!!

 

[Joe4]

Have a look here: How to run an Excel macro every Monday using Windows Task Scheduler