reusing code

[MJKelly]

Hi, The code below needs to be used on seven worksheets (Mon - Sun).

How can I resuse this code for each day of the week? I thought I
could just set the wksdump to Tuesday etc. I also need to change the
column I select in the array for each day (+1 column) - this is the
last few lines of code where 7 will need to become 8 for Tuesday, 9
for Wednesday etc.
I have only included here the code which needs to be reused.



Dim i As Integer
Dim j As Integer

Set wksDump = ThisWorkbook.Sheets("Staff Monday")

wksDump.Cells.ClearContents

'input header column
wksDump.Cells(1, 1).Value = "Name"
wksDump.Cells(1, 2).Value = "Status"
wksDump.Cells(1, 3).Value = "Start"
wksDump.Cells(1, 4).Value = "End"
wksDump.Cells(1, 5).Value = "Shift"
wksDump.Cells(1, 6).Value = "Description"
wksDump.Cells(1, 7).Value = "06:00"

'input times in header column
For i = 7 To 114
wksDump.Cells(1, i).Value = TimeofDay
TimeofDay = TimeofDay + 0.006944444444
Next i

'input times in header column (past midnight)
For i = 115 To 150
wksDump.Cells(1, i).Value = Midnight
Midnight = Midnight + 0.006944444444
Next i


For i = 2 To LastRow
For j = 1 To 1 'j = columns
wksDump.Cells(i, j) = AWD_Array(i, 2)
Next j
Next i

For i = 2 To LastRow
For j = 2 To 2
wksDump.Cells(i, j) = AWD_Array(i, 7)
Next j
Next i

Thanks,
Matt

 

[Bob Phillips]

Call DayUpdate("Monday", 7)
Call DayUpdate("Tuesday", 8)
'etc.

Public Sub DayUpdate(DayName As String, DayIndex As Long)
Dim i As Integer
Dim j As Integer

Set wksDump = ThisWorkbook.Sheets("Staff " & DayName)

wksDump.Cells.ClearContents

'input header column
wksDump.Cells(1, 1).Value = "Name"
wksDump.Cells(1, 2).Value = "Status"
wksDump.Cells(1, 3).Value = "Start"
wksDump.Cells(1, 4).Value = "End"
wksDump.Cells(1, 5).Value = "Shift"
wksDump.Cells(1, 6).Value = "Description"
wksDump.Cells(1, 7).Value = "06:00"

'input times in header column
For i = 7 To 114
wksDump.Cells(1, i).Value = TimeofDay
TimeofDay = TimeofDay + 0.006944444444
Next i

'input times in header column (past midnight)
For i = 115 To 150
wksDump.Cells(1, i).Value = Midnight
Midnight = Midnight + 0.006944444444
Next i


For i = 2 To LastRow
For j = 1 To 1 'j = columns
wksDump.Cells(i, j) = AWD_Array(i, 2)
Next j
Next i

For i = 2 To LastRow
For j = 2 To 2
wksDump.Cells(i, j) = AWD_Array(i, DayIndex)
Next j
Next i
End Sub

 

[MJKelly]

Thanks Bob,

I've used this method in the past. I thought I could loop through the
worksheets, but if this is the more efficicent method then thanks, I
was on the right path before!

kind regards,
Matt

 

[Bob Phillips]

I suppose that you could use

For i = 1 to 7

Call DayUpdate(Format(i + 1, "dddd"), i+6)
Next i

but a loop is always less efficient, evn tough it may be slight.