+ Reply to Thread
Results 1 to 18 of 18

copy cells from sheets based on date, paste to another sheet

Hybrid View

  1. #1
    Registered User
    Join Date
    06-09-2008
    Posts
    28

    Re: copy cells from sheets based on date, paste to another sheet

    I have now created a separate module which is called from the worksheet change event. This appears to run further but now gets stuck where I am copying data. Annotated below.
     Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        
    Call CheckPaySchSO
    
    End Sub
    ------
    Sub CheckPaySchSO()
    
    Dim Today
    Today = Date
        
        Sheets("CoA").Select
        Range("I47").Select
    
        Do While ActiveCell.Value <> ""
            
            If ActiveCell.Value < Today Then ' checks to see if contract date is before the present date - if yes go to next evaluation
            
                If ActiveCell.Offset(0, 7).Value = "" Then ' if there is no previous accounting entry date then
                    
                    If ActiveCell.Value + Month(ActiveCell.Offset(0, 1).Value) < Today Then ' if contract start plus one frequency period is before present date
                         Call enterdata                   ' create entries on Receipts & Payments
                                        
                    End If
                    
                Else
                    If ActiveCell.Offset(0, 7).Value + Month(ActiveCell.Offset(0, 1).Value) < Today Then    ' if last entry plus one frequency period is before present date
        
                         Call enterdata                   ' create entries on Receipts & Payments
                    End If
                End If
            End If
        
        ActiveCell.Offset(1, 0).Select 'evaluate next row
            
        Loop ' Until IsEmpty(ActiveCell) = True
        
        Exit Sub
    
    End Sub
    
    Private Sub enterdata()
    
    ActiveWorkbook.Sheets("Receipts & Payments").Activate
    Range("a10").Select
    
        Do
        If IsEmpty(ActiveCell) = False Then
        ActiveCell.Offset(1, 0).Select
        End If
    
        Loop Until IsEmpty(ActiveCell) = True
    
        ActiveCell.Value = Sheets("CoA").ActiveCell.Value  'Gets stuck here
        ActiveCell.Offset(0, 1) = Sheets("CoA").ActiveCell.Offset(2, 0).Value
        ActiveCell.Offset(0, 2) = Sheets("CoA").ActiveCell.Offset(3, 0).Value
        ActiveCell.Offset(0, 3) = Sheets("CoA").ActiveCellOffset(5, 0).Value
        ActiveCell.Offset(0, 4) = Sheets("CoA").ActiveCell.Offset(4, 0).Value
        ActiveCell.Offset(0, 10) = Sheets("CoA").ActiveCell.Offset(6, 0).Value
        
        ActiveWorkbook.Sheets("CoA").Activate
     
            If ActiveCell.Value + Month(ActiveCell.Offset(0, 1).Value) < Today Then
                    ActiveCell.Offset(0, 7).Value = ActiveCell.Value + Month(ActiveCell.Offset(0, 1).Value)
                    ' if contract start plus one frequency period is before present date
            Else: ActiveCell.Offset(0, 7).Value = ActiveCell.Offset(0, 7).Value + Month(ActiveCell.Offset(0, 1).Value)
                                        
            End If
                    
    End Sub

    Presumably this is not the correct way to copy the cells of the rows that meet the if criteria. So how do I do this?
    Last edited by upod; 10-22-2009 at 12:56 PM. Reason: Makes easier reading to see all the code instead of one line

  2. #2
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: copy cells from sheets based on date, paste to another sheet

    Right, the problem is you have activecell on both sides of the equal sign. The ActiveCell property only identifies the active cell on the active sheet. So if CoA is not the active sheet, Sheets("CoA").ActiveCell doesn't make any sense.
    Can you use the cell index instead? For example:

    ActiveCell.Value = Sheets("CoA").Cells(1,1).Value ' But your cell instead of 1,1.
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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