This will be a bit more efficient:


Private Sub UsageSheet()
Dim rDest As Range
With Sheets("Sheet1")
Set rDest = Sheets("Usage").Cells(8, _
WeekDay(.Range("N2").Value) * 2).Resize(17, 1)
With .Cells(62, 21).Resize(17, 1)
rDest.Value = .Value
rDest.Offset(0, 1).Value = .Offset(0, 2).Value
End With
End With
End Sub


In article . com,
"Pete" wrote:

Can anyone tidy up the Sub below for me please, all it does is takes
one value from formula in "Sheet1", and stores it as a value in
"Usage". As you can see the Weekday command is used to decide which
column is used to store the values.

This Sub works, but my lack of VBA experience makes it very untidy.

Thanks

Pete

Private Sub UsageSheet()
Dim DailyPallets(17) As Long, DailyRejectBags(17) As Long, Ctr
Ctr = 0

For Ctr = 1 To 17
DailyPallets(Ctr) = Sheets("Sheet1").Cells(61 + Ctr, 21).Value
Sheets("Usage").Cells(7 + Ctr,
Weekday(Sheets("Sheet1").Range("N2")) * 2).Value = DailyPallets(Ctr)
Next Ctr

Ctr = 0
For Ctr = 1 To 17
DailyRejectBags(Ctr) = Sheets("Sheet1").Cells(61 + Ctr,
23).Value
Sheets("Usage").Cells(7 + Ctr,
(Weekday(Sheets("Sheet1").Range("N2")) * 2) + 1).Value =
DailyRejectBags(Ctr)
Next Ctr

End Sub