Macro repeat

[one_more_cave_dweller]

I have a macro that copies information from a sheet each day and pastes it horizontally into a summary sheet for the month. (a1=1st, a2=2nd etc.).
The macro is triggered by a control button in the first sheet the user clicks after they have entered the information. How can I stop them from entering the same day twice? I have tried cell validation but that only stops the first column from being repeated. I need the whole row stopped if the first column (a) is a repeat.

Thanks

 

[micahs_10]

So is the user entering a date? If so, you could add a part to the macro that will search for the date the user entered against the dates already ran. And if the date is found, the macro quits. Does that sound like a possibility...I'm not sure I fully understand.

 

[one_more_cave_dweller]

That sounds exactly like what I need. The first column is the date so if the macro looks for the date and stops if it has already been entered that would be perfect. How could I write this kind of code. The macro will be triggered from sheet1 and the data is pasted in sheet2.

Thanks

 

[micahs_10]

Ok, so the user enters a date in cell A1 on sheet1, and then some data from row 1 is copied into sheet2? If this is correct, is the date the user entered carried over to sheet2 as well?...to what column?

I think this code is *almost* what you need (if the columns/references are correct), hopefully somone on the board can help out with it. Also, you'll have to change the Range("A1") reference so that it goes to the correct row.

        myDate = Range("A1").Value
        Sheets("Sheet2").Activate
        If WorksheetFunction.CountIf(Range("A:A"), MyDate) > 1 Then Exit Sub

 

[one_more_cave_dweller]

After all the data is entered on sheet1 the user clicks a control button on sheet1 that triggers a macro that copies the data to another sheet. The first column that is pasted is the date.

 

[neonangel]

Perhaps this will help. To simplify the problem, I created the following data in A1:C5 of sheet 'Src'

Date Data1 Data2
9/02/2005 5 10
10/02/2005 15 20
11/02/2005 25 30
11/02/2005 35 40

Then in sheet 'Dest' I created the same heading row for A1:A3

The following code copies over the first instance for each date from Src to Dest (without using selection - you may find it easier to create code that actually selects each cell rather than setting ranges)

Dim src As Range, dst As Range, rng As Range

' Set initial source and destination ranges
Set src = Sheets("Src").Range("A2")
Set dst = Sheets("Dest").Range("A2")
If Not dst.Value = "" Then Set dst = dst.Offset(-1, 0).End(xlDown).Offset(1, 0)

Do While Not src = ""
' check to see if source date is in destination date
Set rng = Sheets("Dest").Range("A:A").Find(src.Value)
If rng Is Nothing Then
src.EntireRow.Copy dst
Set dst = dst.Offset(1, 0)
End If
Set src = src.Offset(1, 0)
Loop

The output then will be:

Date Data1 Data2
9/02/2005 5 10
10/02/2005 15 20
11/02/2005 25 30


Hopefully this should get you started. The important bit is the .Range("A:A").Find( <some reference to your date value> )

Regards,

Alex