benallen002
Board Regular
- Joined
- Feb 16, 2005
- Messages
- 65
Hey guys,
This thing has got me really stumped and I haven't been able to figure a way past it yet, so I thought I would ask the experts. I created this procedure that uses 3 or 4 loops to pull a bunch of information from different places on one worksheet and put them in order on another sheet. The goal then was to copy the second sheet out of the workbook where it started and into another workbook. What I want it to do is if it can't find the file that it is supposed to go to, then it creates the file right there and then pastes the sheet in. I got this to work great if I go one sheet at a time, but what I'm trying to do is create one final loop that would automate it for me. Like I said all the code works great the first time through, but when it goes back the second time (or 3rd or 4th), I get different errors all the time, depending on where I've put 1) the final Loop call and 2) the Error Handler that creates a new workbook if one is not there. Anyways, like I said, I can't seem to figure out the right order that the final loop code and Error code need to go in. Any help you guys could offer would be greatly appreciated. I'll paste my code below...
<font face=Courier New>
<SPAN style="color:#00007F">Sub</SPAN> ReportNamesAndClassesDailySheet()
<SPAN style="color:#007F00">' Declare the variables used in the routine</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> OutputRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> InputRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> OutputFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> strSaveName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> JobFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> ThisSheet <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> NewFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> ChargeInt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> CostInt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> LastRow2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> TaskRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> LastCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> TaskCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> LoopInt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> StartRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> StartCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> RepShift <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> RepDay <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> RepWkenMon <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> RepWkenDay <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> RepWkenYear <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> TaskName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> FilePath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> ReportPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#007F00">' Sets the folder that the Save As dialog box defaults to at the end of this routine</SPAN>
ChDir Application.ActiveWorkbook.Path + "\Task Reports"
<SPAN style="color:#007F00">' Sets the path for the folders used in this routine</SPAN>
FilePath = Application.ActiveWorkbook.Path
ReportPath = Application.ActiveWorkbook.Path + "\Task Reports"
<SPAN style="color:#007F00">' Sets the string for the workbook name and active sheet</SPAN>
JobFile = Application.ActiveWorkbook.Name
ThisSheet = Application.ActiveWorkbook.ActiveSheet.Name
<SPAN style="color:#007F00">' Doesn't really work that well with this routine</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#007F00">' Get report info. & name from user and store as string</SPAN>
RepShift = cboxShift.Text
RepDay = cboxDay.Text
RepWkenMon = calWkend.Month
RepWkenDay = calWkend.Day
RepWkenYear = calWkend.Year
strSaveName = RepShift + " WKEND " + RepWkenMon + "-" + RepWkenDay + "-" + RepWkenYear + " " + RepDay
<SPAN style="color:#007F00">' Adds the total hours from all employees & all tasks</SPAN>
<SPAN style="color:#007F00">' Call AddTotalsRow</SPAN>
<SPAN style="color:#007F00">' Compute rows and columnes in worksheet and assign to variables</SPAN>
LastRow = Workbooks(JobFile).Worksheets(ThisSheet).UsedRange.Rows.Count
LastCol = Workbooks(JobFile).Worksheets(ThisSheet).UsedRange.Columns.Count
<SPAN style="color:#007F00">' Set the start point for these variables used in the loops</SPAN>
OutputRow = 2
InputRow = 2
StartCol = 8
<SPAN style="color:#007F00">' Gives the first file it's name</SPAN>
TaskName = Workbooks(JobFile).Worksheets(ThisSheet).Cells(1, StartCol).Value
Start:
<SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> StartCol <= LastCol
<SPAN style="color:#007F00">' Add Names, Employee #s and Classes to blank timesheet</SPAN>
ActiveWorkbook.Worksheets("DailyTimeReport (Blank)").Activate
<SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> OutputRow <= LastRow
Range("A" & InputRow).Select
<SPAN style="color:#007F00">'Format each row</SPAN>
<SPAN style="color:#00007F">Call</SPAN> FormatRowDaily
Range("A" & InputRow).Value = Workbooks(JobFile).Worksheets(ThisSheet).Range("A" & OutputRow).Value
Range("B" & InputRow).Value = Workbooks(JobFile).Worksheets(ThisSheet).Range("D" & OutputRow).Value
Range("F" & InputRow).Value = Workbooks(JobFile).Worksheets(ThisSheet).Range("E" & OutputRow).Value
InputRow = InputRow + 3
OutputRow = OutputRow + 3
Range("B" & InputRow).Activate
<SPAN style="color:#00007F">Loop</SPAN>
<SPAN style="color:#007F00">' Reset for next loop</SPAN>
OutputRow = 2
InputRow = 2
<SPAN style="color:#007F00">'Add the days hours</SPAN>
<SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> OutputRow <= LastRow
Range("H" & InputRow).Value = Workbooks(JobFile).Worksheets(ThisSheet).Cells(OutputRow, StartCol).Value
InputRow = InputRow + 1
OutputRow = OutputRow + 1
Range("B" & InputRow).Activate
<SPAN style="color:#00007F">Loop</SPAN>
<SPAN style="color:#007F00">' Reset for next loop</SPAN>
OutputRow = 2
InputRow = 2
<SPAN style="color:#007F00">' Adds the rates; first charge, then cost</SPAN>
<SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> OutputRow <= LastRow
ChargeInt = 3
CostInt = 13
<SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> ChargeInt <= 5
Range("I" & InputRow).Value = "=vlookup(F" & OutputRow & ",ChargeRates," & ChargeInt & ",False)"
Range("L" & InputRow).Value = "=vlookup(B" & OutputRow & ",Employee_List," & CostInt & ",False)"
<SPAN style="color:#007F00">' Sets the format to "Accounting"</SPAN>
Range("I" & InputRow).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Range("L" & InputRow).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
<SPAN style="color:#007F00">' Formats the grey columns of the sheet</SPAN>
Range("K" & InputRow).Select
<SPAN style="color:#00007F">With</SPAN> Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
Range("N" & InputRow).Select
<SPAN style="color:#00007F">With</SPAN> Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
Range("Q" & InputRow).Select
<SPAN style="color:#00007F">With</SPAN> Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
Range("S" & InputRow).Select
<SPAN style="color:#00007F">With</SPAN> Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#007F00">' Moves down to next name</SPAN>
InputRow = InputRow + 1
ChargeInt = ChargeInt + 1
CostInt = CostInt + 1
Range("B" & InputRow).Activate
<SPAN style="color:#00007F">Loop</SPAN>
OutputRow = OutputRow + 3
<SPAN style="color:#00007F">Loop</SPAN>
<SPAN style="color:#007F00">' Reset for next loop</SPAN>
OutputRow = 2
InputRow = 2
<SPAN style="color:#007F00">' Adds the extensions; first charge, then cost</SPAN>
<SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> OutputRow <= LastRow
Range("J" & InputRow).Value = "=I" & OutputRow & "*H" & OutputRow & ""
Range("M" & InputRow).Value = "=L" & OutputRow & "*H" & OutputRow & ""
<SPAN style="color:#007F00">' Sets the format to "Accounting"</SPAN>
Range("J" & InputRow).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Range("M" & InputRow).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
InputRow = InputRow + 1
OutputRow = OutputRow + 1
Range("B" & InputRow).Activate
<SPAN style="color:#00007F">Loop</SPAN>
<SPAN style="color:#007F00">' Reset for next loop</SPAN>
OutputRow = 2
InputRow = 2
<SPAN style="color:#007F00">' Adds the totals for charge then cost rates</SPAN>
Range("O2").Value = "=sum(J:J)"
Range("O2").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Range("P2").Value = "=sum(M:M)"
Range("P2").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Range("R2").Value = "=O2-P2"
Range("R2").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Range("T2").Value = "=sum(H:H)"
<SPAN style="color:#007F00">' Removes the Total row from the copied sheet to avoid #N/A formulas on the sheet</SPAN>
<SPAN style="color:#007F00">' due too bad lookups</SPAN>
LoopInt = 1
<SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> LoopInt <= 4
Rows(LastRow).Select
Selection.Delete Shift:=xlUp
LoopInt = <SPAN style="color:#00007F">Loop</SPAN>Int + 1
LastRow = LastRow - 1
Loop
<SPAN style="color:#007F00">' Resets these two values for the next loop</SPAN>
<SPAN style="color:#00007F">Loop</SPAN>Int = 1
LastRow = Workbooks(JobFile).Worksheets(ThisSheet).UsedRange.Rows.Count
<SPAN style="color:#007F00">' This removes all the formulas and leaves just values to copy to the new sheet</SPAN>
<SPAN style="color:#00007F">Call</SPAN> RemoveFormulasWorksheet
<SPAN style="color:#007F00">' Opens the task's workbook (based on the TaskName value), if it can't find the file</SPAN>
<SPAN style="color:#007F00">' to open, the Error handling code creates it</SPAN>
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> HandleError
ChDir ReportPath
Workbooks.Open Filename:= _
ReportPath + "\" + TaskName + ".xls"
Windows(JobFile).Activate
Sheets("DailyTimeReport (Blank)").Select
Sheets("DailyTimeReport (Blank)").Copy After:=Workbooks( _
TaskName).Sheets(1)
<SPAN style="color:#007F00">' Changes the name of the new sheet to the user inputed text from beginning of routine</SPAN>
Application.ActiveWorkbook.ActiveSheet.Name = strSaveName
<SPAN style="color:#007F00">' Resets the "DailyTimeReport (Blank)" page by deleting all rows except for header</SPAN>
Application.Workbooks(JobFile).Worksheets("DailyTimeReport (Blank)").Activate
Rows("2:10000").Select
Range("G2").Activate
Selection.Delete Shift:=xlUp
Range("A1").Select
<SPAN style="color:#007F00">' Moves to the next task on the page for the next loop</SPAN>
StartCol = StartCol + 1
<SPAN style="color:#007F00">' Assigns that task's name to the TaskName string variable</SPAN>
TaskName = Workbooks(JobFile).Worksheets(ThisSheet).Cells(1, StartCol).Value
HandleError:
<SPAN style="color:#00007F">Set</SPAN> NewBook = Workbooks.Add(ReportPath + "\TaskReportTemplate.xls")
<SPAN style="color:#00007F">With</SPAN> NewBook
.SaveAs Filename:=ReportPath + "\" + TaskName + ".xls"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">GoTo</SPAN> Start
<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
Loop
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#007F00">' Resets the default path to the main folder where the application runs</SPAN>
ChDir Workbooks(JobFile).Path
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>
This thing has got me really stumped and I haven't been able to figure a way past it yet, so I thought I would ask the experts. I created this procedure that uses 3 or 4 loops to pull a bunch of information from different places on one worksheet and put them in order on another sheet. The goal then was to copy the second sheet out of the workbook where it started and into another workbook. What I want it to do is if it can't find the file that it is supposed to go to, then it creates the file right there and then pastes the sheet in. I got this to work great if I go one sheet at a time, but what I'm trying to do is create one final loop that would automate it for me. Like I said all the code works great the first time through, but when it goes back the second time (or 3rd or 4th), I get different errors all the time, depending on where I've put 1) the final Loop call and 2) the Error Handler that creates a new workbook if one is not there. Anyways, like I said, I can't seem to figure out the right order that the final loop code and Error code need to go in. Any help you guys could offer would be greatly appreciated. I'll paste my code below...
<font face=Courier New>
<SPAN style="color:#00007F">Sub</SPAN> ReportNamesAndClassesDailySheet()
<SPAN style="color:#007F00">' Declare the variables used in the routine</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> OutputRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> InputRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> OutputFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> strSaveName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> JobFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> ThisSheet <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> NewFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> ChargeInt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> CostInt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> LastRow2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> TaskRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> LastCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> TaskCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> LoopInt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> StartRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> StartCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> RepShift <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> RepDay <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> RepWkenMon <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> RepWkenDay <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> RepWkenYear <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> TaskName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> FilePath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> ReportPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#007F00">' Sets the folder that the Save As dialog box defaults to at the end of this routine</SPAN>
ChDir Application.ActiveWorkbook.Path + "\Task Reports"
<SPAN style="color:#007F00">' Sets the path for the folders used in this routine</SPAN>
FilePath = Application.ActiveWorkbook.Path
ReportPath = Application.ActiveWorkbook.Path + "\Task Reports"
<SPAN style="color:#007F00">' Sets the string for the workbook name and active sheet</SPAN>
JobFile = Application.ActiveWorkbook.Name
ThisSheet = Application.ActiveWorkbook.ActiveSheet.Name
<SPAN style="color:#007F00">' Doesn't really work that well with this routine</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#007F00">' Get report info. & name from user and store as string</SPAN>
RepShift = cboxShift.Text
RepDay = cboxDay.Text
RepWkenMon = calWkend.Month
RepWkenDay = calWkend.Day
RepWkenYear = calWkend.Year
strSaveName = RepShift + " WKEND " + RepWkenMon + "-" + RepWkenDay + "-" + RepWkenYear + " " + RepDay
<SPAN style="color:#007F00">' Adds the total hours from all employees & all tasks</SPAN>
<SPAN style="color:#007F00">' Call AddTotalsRow</SPAN>
<SPAN style="color:#007F00">' Compute rows and columnes in worksheet and assign to variables</SPAN>
LastRow = Workbooks(JobFile).Worksheets(ThisSheet).UsedRange.Rows.Count
LastCol = Workbooks(JobFile).Worksheets(ThisSheet).UsedRange.Columns.Count
<SPAN style="color:#007F00">' Set the start point for these variables used in the loops</SPAN>
OutputRow = 2
InputRow = 2
StartCol = 8
<SPAN style="color:#007F00">' Gives the first file it's name</SPAN>
TaskName = Workbooks(JobFile).Worksheets(ThisSheet).Cells(1, StartCol).Value
Start:
<SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> StartCol <= LastCol
<SPAN style="color:#007F00">' Add Names, Employee #s and Classes to blank timesheet</SPAN>
ActiveWorkbook.Worksheets("DailyTimeReport (Blank)").Activate
<SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> OutputRow <= LastRow
Range("A" & InputRow).Select
<SPAN style="color:#007F00">'Format each row</SPAN>
<SPAN style="color:#00007F">Call</SPAN> FormatRowDaily
Range("A" & InputRow).Value = Workbooks(JobFile).Worksheets(ThisSheet).Range("A" & OutputRow).Value
Range("B" & InputRow).Value = Workbooks(JobFile).Worksheets(ThisSheet).Range("D" & OutputRow).Value
Range("F" & InputRow).Value = Workbooks(JobFile).Worksheets(ThisSheet).Range("E" & OutputRow).Value
InputRow = InputRow + 3
OutputRow = OutputRow + 3
Range("B" & InputRow).Activate
<SPAN style="color:#00007F">Loop</SPAN>
<SPAN style="color:#007F00">' Reset for next loop</SPAN>
OutputRow = 2
InputRow = 2
<SPAN style="color:#007F00">'Add the days hours</SPAN>
<SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> OutputRow <= LastRow
Range("H" & InputRow).Value = Workbooks(JobFile).Worksheets(ThisSheet).Cells(OutputRow, StartCol).Value
InputRow = InputRow + 1
OutputRow = OutputRow + 1
Range("B" & InputRow).Activate
<SPAN style="color:#00007F">Loop</SPAN>
<SPAN style="color:#007F00">' Reset for next loop</SPAN>
OutputRow = 2
InputRow = 2
<SPAN style="color:#007F00">' Adds the rates; first charge, then cost</SPAN>
<SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> OutputRow <= LastRow
ChargeInt = 3
CostInt = 13
<SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> ChargeInt <= 5
Range("I" & InputRow).Value = "=vlookup(F" & OutputRow & ",ChargeRates," & ChargeInt & ",False)"
Range("L" & InputRow).Value = "=vlookup(B" & OutputRow & ",Employee_List," & CostInt & ",False)"
<SPAN style="color:#007F00">' Sets the format to "Accounting"</SPAN>
Range("I" & InputRow).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Range("L" & InputRow).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
<SPAN style="color:#007F00">' Formats the grey columns of the sheet</SPAN>
Range("K" & InputRow).Select
<SPAN style="color:#00007F">With</SPAN> Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
Range("N" & InputRow).Select
<SPAN style="color:#00007F">With</SPAN> Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
Range("Q" & InputRow).Select
<SPAN style="color:#00007F">With</SPAN> Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
Range("S" & InputRow).Select
<SPAN style="color:#00007F">With</SPAN> Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#007F00">' Moves down to next name</SPAN>
InputRow = InputRow + 1
ChargeInt = ChargeInt + 1
CostInt = CostInt + 1
Range("B" & InputRow).Activate
<SPAN style="color:#00007F">Loop</SPAN>
OutputRow = OutputRow + 3
<SPAN style="color:#00007F">Loop</SPAN>
<SPAN style="color:#007F00">' Reset for next loop</SPAN>
OutputRow = 2
InputRow = 2
<SPAN style="color:#007F00">' Adds the extensions; first charge, then cost</SPAN>
<SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> OutputRow <= LastRow
Range("J" & InputRow).Value = "=I" & OutputRow & "*H" & OutputRow & ""
Range("M" & InputRow).Value = "=L" & OutputRow & "*H" & OutputRow & ""
<SPAN style="color:#007F00">' Sets the format to "Accounting"</SPAN>
Range("J" & InputRow).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Range("M" & InputRow).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
InputRow = InputRow + 1
OutputRow = OutputRow + 1
Range("B" & InputRow).Activate
<SPAN style="color:#00007F">Loop</SPAN>
<SPAN style="color:#007F00">' Reset for next loop</SPAN>
OutputRow = 2
InputRow = 2
<SPAN style="color:#007F00">' Adds the totals for charge then cost rates</SPAN>
Range("O2").Value = "=sum(J:J)"
Range("O2").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Range("P2").Value = "=sum(M:M)"
Range("P2").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Range("R2").Value = "=O2-P2"
Range("R2").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Range("T2").Value = "=sum(H:H)"
<SPAN style="color:#007F00">' Removes the Total row from the copied sheet to avoid #N/A formulas on the sheet</SPAN>
<SPAN style="color:#007F00">' due too bad lookups</SPAN>
LoopInt = 1
<SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> LoopInt <= 4
Rows(LastRow).Select
Selection.Delete Shift:=xlUp
LoopInt = <SPAN style="color:#00007F">Loop</SPAN>Int + 1
LastRow = LastRow - 1
Loop
<SPAN style="color:#007F00">' Resets these two values for the next loop</SPAN>
<SPAN style="color:#00007F">Loop</SPAN>Int = 1
LastRow = Workbooks(JobFile).Worksheets(ThisSheet).UsedRange.Rows.Count
<SPAN style="color:#007F00">' This removes all the formulas and leaves just values to copy to the new sheet</SPAN>
<SPAN style="color:#00007F">Call</SPAN> RemoveFormulasWorksheet
<SPAN style="color:#007F00">' Opens the task's workbook (based on the TaskName value), if it can't find the file</SPAN>
<SPAN style="color:#007F00">' to open, the Error handling code creates it</SPAN>
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> HandleError
ChDir ReportPath
Workbooks.Open Filename:= _
ReportPath + "\" + TaskName + ".xls"
Windows(JobFile).Activate
Sheets("DailyTimeReport (Blank)").Select
Sheets("DailyTimeReport (Blank)").Copy After:=Workbooks( _
TaskName).Sheets(1)
<SPAN style="color:#007F00">' Changes the name of the new sheet to the user inputed text from beginning of routine</SPAN>
Application.ActiveWorkbook.ActiveSheet.Name = strSaveName
<SPAN style="color:#007F00">' Resets the "DailyTimeReport (Blank)" page by deleting all rows except for header</SPAN>
Application.Workbooks(JobFile).Worksheets("DailyTimeReport (Blank)").Activate
Rows("2:10000").Select
Range("G2").Activate
Selection.Delete Shift:=xlUp
Range("A1").Select
<SPAN style="color:#007F00">' Moves to the next task on the page for the next loop</SPAN>
StartCol = StartCol + 1
<SPAN style="color:#007F00">' Assigns that task's name to the TaskName string variable</SPAN>
TaskName = Workbooks(JobFile).Worksheets(ThisSheet).Cells(1, StartCol).Value
HandleError:
<SPAN style="color:#00007F">Set</SPAN> NewBook = Workbooks.Add(ReportPath + "\TaskReportTemplate.xls")
<SPAN style="color:#00007F">With</SPAN> NewBook
.SaveAs Filename:=ReportPath + "\" + TaskName + ".xls"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">GoTo</SPAN> Start
<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
Loop
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#007F00">' Resets the default path to the main folder where the application runs</SPAN>
ChDir Workbooks(JobFile).Path
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>