The file is too large to upload but here are the two relevant modules:
Public StoredCopyCell As Range
Public CopySpecificRange As Range
Public CopyWeekRange As Range
Sub CopyRangeMacro(StartCell As Range)
StartCell.Resize(DayRows, 16).Select
StartCell.Resize(DayRows, 16).Copy
Set StoredCopyCell = StartCell
End Sub
Sub CopyDay1()
Dim StartCell As Range
Set StartCell = Range("E5")
Call CopyRangeMacro(StartCell)
End Sub
Sub CopyDay2()
Dim StartCell As Range
Set StartCell = Range("E" & 1 * (DayRows + 2) + 5)
Call CopyRangeMacro(StartCell)
End Sub
Sub CopyDay3()
Dim StartCell As Range
Set StartCell = Range("E" & 2 * (DayRows + 2) + 5)
Call CopyRangeMacro(StartCell)
End Sub
Sub CopyDay4()
Dim StartCell As Range
Set StartCell = Range("E" & 3 * (DayRows + 2) + 5)
Call CopyRangeMacro(StartCell)
End Sub
Sub CopyDay5()
Dim StartCell As Range
Set StartCell = Range("E" & 4 * (DayRows + 2) + 5)
Call CopyRangeMacro(StartCell)
End Sub
Sub CopyDay6()
Dim StartCell As Range
Set StartCell = Range("E" & 5 * (DayRows + 2) + 5)
Call CopyRangeMacro(StartCell)
End Sub
Sub CopyDay7()
Dim StartCell As Range
Set StartCell = Range("E" & 6 * (DayRows + 2) + 5)
Call CopyRangeMacro(StartCell)
End Sub
Sub CopySpecificRows()
Dim NumRows As Integer
Dim StartCell As Range
On Error GoTo ErrorMessage
Set CopySpecificRange = Application.InputBox(Title:="Rows to Copy", Prompt:="Select range with all the rows you want to copy", Type:=8)
NumRows = CopySpecificRange.Rows.Count
Set StartCell = Cells(CopySpecificRange.Cells(1, 1).Row, 5)
StartCell.Resize(NumRows, 16).Select
StartCell.Resize(NumRows, 16).Copy
Set CopySpecificRange = StartCell.Resize(NumRows, 16)
Exit Sub
ErrorMessage:
MsgBox "Please select a range"
End Sub
Sub CopyWeek()
Dim NumRows As Integer
Dim StartCell As Range
Set StartCell = Range("E5")
NumRows = (7 * (DayRows + 2) - 2)
StartCell.Resize(NumRows, 16).Select
StartCell.Resize(NumRows, 16).Copy
Set CopyWeekRange = StartCell.Resize(NumRows, 16)
End Sub
Sub PasteRangeMacro(StartCell As Range)
Application.EnableEvents = False
If StoredCopyCell Is Nothing Then GoTo ErrorMessage
OldCalculationMode = Application.Calculation
Application.Calculation = xlCalculationManual
Dim CopyStart As Range
Set CopyStart = StoredCopyCell
Debug.Print "test0"
StartCell.Resize(DayRows, 7).Value = CopyStart.Resize(DayRows, 7).Value
Debug.Print StartCell.Offset(0, 9).Resize(DayRows, 1).Address
StartCell.Offset(0, 9).Resize(DayRows, 1).Value = CopyStart.Offset(0, 9).Resize(DayRows, 1).Value
StartCell.Offset(0, 12).Resize(DayRows, 1).Value = CopyStart.Offset(0, 12).Resize(DayRows, 1).Value
StartCell.Offset(0, 15).Resize(DayRows, 1).Value = CopyStart.Offset(0, 15).Resize(DayRows, 1).Value
Debug.Print "test"
Application.EnableEvents = True
Application.Calculation = OldCalculationMode
ActiveSheet.Calculate
Exit Sub
ErrorMessage:
Application.EnableEvents = True
MsgBox "Error - Please use a Copy button before pressing a Paste button."
End Sub
Sub PasteDay1()
Dim StartCell As Range
Set StartCell = Range("E5")
Call PasteRangeMacro(StartCell)
End Sub
Sub PasteDay2()
Dim StartCell As Range
Set StartCell = Range("E" & 1 * (DayRows + 2) + 5)
Call PasteRangeMacro(StartCell)
End Sub
Sub PasteDay3()
Dim StartCell As Range
Set StartCell = Range("E" & 2 * (DayRows + 2) + 5)
Call PasteRangeMacro(StartCell)
End Sub
Sub PasteDay4()
Dim StartCell As Range
Set StartCell = Range("E" & 3 * (DayRows + 2) + 5)
Call PasteRangeMacro(StartCell)
End Sub
Sub PasteDay5()
Dim StartCell As Range
Set StartCell = Range("E" & 4 * (DayRows + 2) + 5)
Call PasteRangeMacro(StartCell)
End Sub
Sub PasteDay6()
Dim StartCell As Range
Set StartCell = Range("E" & 5 * (DayRows + 2) + 5)
Call PasteRangeMacro(StartCell)
End Sub
Sub PasteDay7()
Dim StartCell As Range
Set StartCell = Range("E" & 6 * (DayRows + 2) + 5)
Call PasteRangeMacro(StartCell)
End Sub
Sub PasteSpecificRange()
Application.EnableEvents = False
If CopySpecificRange Is Nothing Then GoTo ErrorMessage
Dim CopyStart As Range
Dim NumRows As Integer
Set CopyStart = CopySpecificRange.Cells(1, 1)
NumRows = CopySpecificRange.Rows.Count
On Error GoTo ErrorMessage
Set StartCell = Cells(Selection.Cells(1, 1).Row, 5)
On Error GoTo 0
StartCell.Resize(NumRows, 7).Value = CopyStart.Resize(NumRows, 7).Value
StartCell.Offset(0, 9).Resize(NumRows, 1).Value = CopyStart.Offset(0, 9).Resize(NumRows, 1).Value
StartCell.Offset(0, 12).Resize(NumRows, 1).Value = CopyStart.Offset(0, 12).Resize(NumRows, 1).Value
StartCell.Offset(0, 15).Resize(NumRows, 1).Value = CopyStart.Offset(0, 15).Resize(NumRows, 1).Value
Application.EnableEvents = True
ActiveSheet.Calculate
Exit Sub
ErrorMessage:
Application.EnableEvents = True
MsgBox "Error - Please use the Copy Specific Range button before pressing a Paste button. Please Select 1 cell before Clicking Paste"
End Sub
Sub PasteWeek()
Application.EnableEvents = False
If CopyWeekRange Is Nothing Then GoTo ErrorMessage
Dim CopyStart As Range
Dim NumRows As Integer
Set CopyStart = CopyWeekRange.Cells(1, 1)
NumRows = CopyWeekRange.Rows.Count
On Error GoTo ErrorMessage
Set StartCell = Range("E5")
On Error GoTo 0
StartCell.Resize(NumRows, 7).Value = CopyStart.Resize(NumRows, 7).Value
Debug.Print "TEST1"
StartCell.Offset(0, 9).Resize(NumRows).Value = CopyStart.Offset(0, 9).Resize(NumRows).Value
Debug.Print "TEST2"
StartCell.Offset(0, 12).Resize(NumRows).Value = CopyStart.Offset(0, 12).Resize(NumRows).Value
Debug.Print "TEST3"
StartCell.Offset(0, 15).Resize(NumRows).Value = CopyStart.Offset(0, 15).Resize(NumRows).Value
Debug.Print "TEST"
Application.EnableEvents = True
ActiveSheet.Calculate
Exit Sub
ErrorMessage:
Application.EnableEvents = True
MsgBox "Error - Please use the Copy Week button before pressing a Paste button."
End Sub
Public Const DayRows As Integer = 10
Bookmarks