Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have the following code...

Sub test()

Workbooks("MAY10-Key Indicator Daily Reportcopy.xls").Sheets("Input").Activate
Set c = Range("B15:B45")
For Each cell In c
    If cell.Value = Range("I5").Value Then
        Workbooks("McKinney Daily Census Template NOV 10.xls").Sheets("McKinney").Range("C15:I15").Copy
        cell.Offset(0, 37).PasteSpecial
    End If
    Next
Application.CutCopyMode = False

If Cell.Value = Date Then
    Range(Cells(Cell.Row + 1, 3).Address, Cells(Cell.Row + 1, 9).Address).Select
    Cells(Cell.Row + 1, 3).Activate
   Exit For
End If

Next Cell
End Sub

In the WB(McKinney Daily Census Template...), I have a column of dates that match with another WB. When the date changes I need WB(McKinney...) to drop to the next line range. I.E

11/01/10 - C15:I15

11/02/10 - C16:I16

11/03/10 - C17:I17

What do I need to add to my code to accomplish this. Thanks

share|improve this question
    
so when you open WB McKinney you want excel to automatically select the next empty row in columns C through I –  Xantec Nov 3 '10 at 18:24
    
yes, but they wont be empty. It will have data in it –  edmon Nov 3 '10 at 19:15
    
@Xantec. Actually when I run the macro the next day I want it to automatically select the next row in column c through i –  edmon Nov 4 '10 at 15:45
    
great, one final point to clear up and then i should be able to code it for you. is there a column that has the dates in it? if not, are the rows in C:I empty prior to the next day? –  Xantec Nov 4 '10 at 15:52
    
Yes. Column B15:B45 has dates for the current month. –  edmon Nov 4 '10 at 16:23
add comment

1 Answer

up vote 0 down vote accepted

This code has been updated to be specific to the needs indicated in the comments.

Sub test()
    Dim MayCell As Range, MayC As Range
    Dim McCell As Range, McC As Range
    Dim McK As Workbook, MAY10 As Workbook
    Dim ChkDate As Variant

    Set MAY10 = Workbooks("MAY10-Key Indicator Daily Reportcopy.xls")
    'may need to change, if your MAY10 file name changes
    Set McK = Workbooks("McKinney Daily Census Template NOV 10.xls")
    'this may need to change for each month/year

    MAY10.Activate
    MAY10.Sheets("Input").Activate
    ChkDate = Range("I5").Value
    Set MayC = Range("B15:B45")
    For Each MayCell In MayC
        If MayCell.Value = ChkDate Then
            McK.Activate
            McK.Sheets("McKinney").Activate
            Set McC = Range("B15:B45")
            For Each McCell In McC
                If McCell.Value = ChkDate Then
                    Range(Cells(McCell.Row, 3).Address, Cells(McCell.Row, 9).Address).Copy
                    Exit For
                End If
            Next McCell
            MAY10.Activate
            MayCell.Offset(0, 37).PasteSpecial
            Exit For
        End If
    Next MayCell
    Application.CutCopyMode = False
End Sub
share|improve this answer
    
I get a compile error message that says... Exit For not within For...Next –  edmon Nov 4 '10 at 20:10
    
can you update the code in your question with what it looks like now, with my addition added? –  Xantec Nov 4 '10 at 20:12
    
It is now updated. –  edmon Nov 4 '10 at 20:20
    
ok, i have updated the code in my answer, including the code from your question. If you remove your Sub test() from your module and copy in all of the code in my answer it should work –  Xantec Nov 4 '10 at 20:57
    
It copies the first row data (date 11/01/10). But it does not move down to the next row of data when i change the date to 11/02/10, it just paste 11/01/10 data again for the next day –  edmon Nov 4 '10 at 21:05
show 7 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.