Advertisement
Guest User

Untitled

a guest
Apr 18th, 2014
54
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.63 KB | None | 0 0
  1. Sub getFirstDayofWeek()
  2. Application.ScreenUpdating = False
  3. Dim ws As Worksheet
  4. Dim summWS As Worksheet
  5. Dim loopSht As Worksheet
  6. Dim thisWeek As String, lastWeek As String
  7. Dim dateExists As Boolean
  8. dateExists = False
  9. Set summWS = ThisWorkbook.Sheets("Summary")
  10. thisWeek = Format(Now() - Weekday(Now(), vbSaturday) + 1, "ddmmyy")
  11. lastWeek = Format(Now() - Weekday(Now(), vbSaturday) - 6, "ddmmyy")
  12. For Each loopSht In ThisWorkbook.Worksheets
  13. If loopSht.Name = thisWeek Then
  14. dateExists = True
  15. Exit For
  16. End If
  17. Next
  18. If dateExists Then
  19. Debug.Print "Do nothing"
  20. Else
  21. Debug.Print "Do something"
  22.  
  23. runReport ("\savereporthere")
  24.  
  25. Sheets("Template").Copy After:=Sheets("Summary %")
  26. Set ws = ActiveSheet
  27. ws.Name = thisWeek
  28. ws.Range("A1").Value = Now() - Weekday(Now(), vbSaturday) + 1
  29. summWS.Rows("25:26").Copy
  30. summWS.Rows("25:25").Insert Shift:=xlDown
  31. Application.CutCopyMode = False
  32. summWS.Rows("5:26").Replace What:=lastWeek, Replacement:=thisWeek, LookAt:=xlPart
  33. End If
  34. Sheets(thisWeek).Activate
  35. Application.ScreenUpdating = True
  36. End Sub
  37.  
  38. Sub runReport(Optional fileString As String = "C:Temp")
  39. Dim reportWeek As String, filePath As String
  40.  
  41. If Right(fileString, 1) <> "" Then
  42. fileString = fileString & ""
  43. End If
  44.  
  45. reportWeek = Format(Now() - Weekday(Now(), vbSaturday) - 6, "ddmmyy")
  46.  
  47. If Dir(fileString, vbDirectory) = vbNullString Then
  48. fileString = "\anotherbackupfailsafepath"
  49. MsgBox "Filepath not found. Will be saved as " & fileString
  50. End If
  51.  
  52. filePath = fileString & "Times PDF - " & reportWeek & ".pdf"
  53. Sheets(Array("Summary", "Summary %", reportWeek)).Select
  54. Application.DisplayAlerts = False
  55. ThisWorkbook.SaveAs filePath, 57
  56. Application.DisplayAlerts = True
  57.  
  58. End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement