Results 1 to 7 of 7

Macro Ending Early - When Using Range.Value on some macros?

Threaded View

  1. #6
    Registered User
    Join Date
    06-05-2013
    Location
    USA
    MS-Off Ver
    2003, 2010, 2013, 2016
    Posts
    12

    Re: Macro Ending Early - When Using Range.Value on some macros?

    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
    Last edited by bkm2016; 05-28-2017 at 02:52 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Ending Macro - Variable Range
    By Austex_egger in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-22-2013, 02:52 PM
  2. Need help identifying a value range for ending a Loop Until
    By erik1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-06-2013, 05:19 AM
  3. [SOLVED] Macro stops too early - Help!
    By cossie2k in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-25-2012, 10:15 AM
  4. Inserting text based on a predetermined criteria early in the macro
    By MonkeyFlyer in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-30-2012, 06:06 PM
  5. determine begining row,col and ending row,col of variable range
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 07-17-2011, 08:09 PM
  6. Ending macro early
    By TedH in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-20-2009, 11:45 AM
  7. Ending Macros
    By lj123 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-12-2008, 01:10 PM
  8. Ending a macro early conditionally on one cell being blank
    By Rokuro kubi in forum Excel General
    Replies: 3
    Last Post: 05-26-2006, 09:15 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1