try
Private Sub UsageSheet()
Dim DailyPallets(17) As Long, DailyRejectBags(17) As Long, Ctr
dim Ctr as integer
For Ctr = 1 To 17
tgtcol = Weekday(Sheets("Sheet1").Range("N2")) * 2)
tgtrow = 7 +Ctr
Sheets("Usage").Cells(tgtrow,tgtcol) =
Sheets("Sheet1").Cells(61 + Ctr, 21)
Sheets("Usage").Cells(tgtrow,tgtcol + 1)
=Sheets("Sheet1").Cells(61 + Ctr,23)
Next Ctr
End Sub


"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