Hello,

I am given the task of expanding an already existing macro, however
I'm a bit novice I suppose and I am stuck with the program not working
with the added changes, so I'm doing it wrong. I have simplified the
code and functionality to better enable me to explain my problem and
hopefully get some helpful feedback that won't confuse the reader.

The macro's purpose is to read production turns for a unit and write
them out into a comma delimited file to be used by production
programs. It goes through a 3-row multiple column range where each row
indicates a shift(morning/mid/night) and each column indicates a date.
The macro's purpose is to detect changes from " " to "X" or from "X"
to " " to indicate time frames when the machine is running as to when
it's "down" and not. Once it goes through this range, it moves on to
the next unit which has its turns listed below it, and so on. These
planned up and down instructions are then fed into the machines
themselves to instruct when they are on or off.

Anyhow, currently the macro and spreadsheet is one single spreadsheet.
I need to expand the workbook to 3 spreadsheets to extend the time
frame further. So for each unit, after it's done with the first
worksheet, it moves on to the second worksheet which is just a
continuation of the first worksheet and basically a twin, with only
the dates and turns different, and then a third.

I just put in a change sheet command and copied and pasted the code
three times to fufill the effect but it is not moving on to the second
and third pages. The dates change forward accordingly, but it just
copies the turns from the first spreadsheet twice instead of reading
in from the second and then third. Please, I know this is a simple
solution to a program that already exists...it's just I'm no expert
and don't want to start this all over just because I don't understand
the existing logic. Your help will make my day! I spent all day
yesterday fretting over this...


Sub ProcessRanges()
On Error GoTo ExitSub
Dim StartingDateRange As Range, FileName As String
Dim FileNumber As Integer

Set StartingDateRange = Sheet1.[c3]
If Not IsDate(StartingDateRange) Then
MsgBox "Invalid starting date in range " &
StartingDateRange.Address(0, 0)
Exit Sub
End If

Debug.Print ThisWorkbook.Path
FileName = "\\broner\data$\FCDM.dat"

FileNumber = FreeFile()
Open FileName For Output As #FileNumber

If CreateCVS(Sheets("FC1"), StartingDateRange, FileNumber) Then
'all is well
Debug.Print "Success..."
Else
'problem
Debug.Print "Failure..."
End If

If CreateCVS(Sheets("FC2"), StartingDateRange, FileNumber) Then
'all is well
Debug.Print "Success..."
Else
'problem
Debug.Print "Failure..."
End If

If CreateCVS(Sheets("FC3"), StartingDateRange, FileNumber) Then
'all is well
Debug.Print "Success..."
Else
'problem
Debug.Print "Failure..."
End If


ExitSub:
Close #FileNumber


End Sub

Private Function CreateCVS( _
sh As Worksheet, _
StartingDateRange As Range, _
FileNumber As Integer) As Boolean

On Error GoTo Err_CreateCVS

Dim UnitNumber As String, CurrentDate As Date
Dim DataRange As Range

Dim FirstColumn As Integer, LastColumn As Integer, CurrentColumn
As Integer
Dim FirstColumn1 As Integer, LastColumn1 As Integer,
CurrentColumn1 As Integer

Dim ShiftRow As Long, ShiftStatus(1 To 3) As String
Dim ShiftItem As Integer
Dim PreviousShiftStatus As String, CurrentShiftStatus As String
Dim ConservationShutdown As Boolean
Dim HalfDay As Boolean

Dim i As Integer

'Data Range starts with first schedule box. Everything else is
offset according to this cell


Set DataRange = sh.Range(StartingDateRange.Offset(1), _
StartingDateRange.End(xlToRight).Offset(3))

Debug.Print DataRange(1).Address

FirstColumn = DataRange(1).Column
LastColumn = FirstColumn + DataRange.Columns.Count - 1
ShiftRow = DataRange(1).Row
UnitNumber = DataRange(1).Offset(, -2)
CurrentDate = DateValue(StartingDateRange)

Do

PreviousShiftStatus = "No Previous Status"

If UnitNumber < "0" Then
For CurrentColumn = FirstColumn To LastColumn

ShiftStatus(1) = sh.Cells(ShiftRow, CurrentColumn)
ShiftStatus(2) = sh.Cells(ShiftRow + 1, CurrentColumn)
ShiftStatus(3) = sh.Cells(ShiftRow + 2, CurrentColumn)



For ShiftItem = 1 To 3

ConservationShutdown = False



Select Case Trim(UCase(ShiftStatus(ShiftItem)))
Case "X", "O"
CurrentShiftStatus = "U"
Case "", "H"
CurrentShiftStatus = "D"
Case "E"
CurrentShiftStatus = "D"
ConservationShutdown = True


End Select


If PreviousShiftStatus < CurrentShiftStatus
Then

Print #FileNumber, UnitNumber &
"," & CurrentShiftStatus & "," & _
Format(CurrentDate +
Choose(ShiftItem, #12:00:00 AM#, #8:00:00 AM#, #4:00:00 PM#), _
"mm/dd/yyyy hh:mm")




End If

PreviousShiftStatus = CurrentShiftStatus
Next

CurrentDate = CurrentDate + 1


Next


'SECOND TAB STARTS HERE

Sheets("FC2").Select

For CurrentColumn = FirstColumn To LastColumn

ShiftStatus(1) = sh.Cells(ShiftRow, CurrentColumn)
ShiftStatus(2) = sh.Cells(ShiftRow + 1, CurrentColumn)
ShiftStatus(3) = sh.Cells(ShiftRow + 2, CurrentColumn)



For ShiftItem = 1 To 3

ConservationShutdown = False



Select Case Trim(UCase(ShiftStatus(ShiftItem)))
Case "X", "O"
CurrentShiftStatus = "U"
Case "", "H"
CurrentShiftStatus = "D"
Case "E"
CurrentShiftStatus = "D"
ConservationShutdown = True


End Select


If PreviousShiftStatus < CurrentShiftStatus
Then

Print #FileNumber, UnitNumber &
"," & CurrentShiftStatus & "," & _
Format(CurrentDate +
Choose(ShiftItem, #12:00:00 AM#, #8:00:00 AM#, #4:00:00 PM#), _
"mm/dd/yyyy hh:mm")




End If

PreviousShiftStatus = CurrentShiftStatus
Next

CurrentDate = CurrentDate + 1


Next

'THIRD TAB STARTS HERE

Sheets("FC3").Select

For CurrentColumn = FirstColumn To LastColumn

ShiftStatus(1) = sh.Cells(ShiftRow, CurrentColumn)
ShiftStatus(2) = sh.Cells(ShiftRow + 1, CurrentColumn)
ShiftStatus(3) = sh.Cells(ShiftRow + 2, CurrentColumn)



For ShiftItem = 1 To 3

ConservationShutdown = False



Select Case Trim(UCase(ShiftStatus(ShiftItem)))
Case "X", "O"
CurrentShiftStatus = "U"
Case "", "H"
CurrentShiftStatus = "D"
Case "E"
CurrentShiftStatus = "D"
ConservationShutdown = True


End Select


If PreviousShiftStatus < CurrentShiftStatus
Then

Print #FileNumber, UnitNumber &
"," & CurrentShiftStatus & "," & _
Format(CurrentDate +
Choose(ShiftItem, #12:00:00 AM#, #8:00:00 AM#, #4:00:00 PM#), _
"mm/dd/yyyy hh:mm")




End If

PreviousShiftStatus = CurrentShiftStatus
Next

CurrentDate = CurrentDate + 1


Next

End If

Set DataRange = DataRange.Offset(6)
UnitNumber = DataRange(1).Offset(, -2)
ShiftRow = DataRange(1).Row
CurrentDate = StartingDateRange


Loop Until Trim(UnitNumber) = ""

CreateCVS = True
Exit Function

Err_CreateCVS:

End Function