Help- Copy and paste VBA

[Excelnoobisme]

Hi, currently im using the code as below and working well. This will copy and paste from "working" to "data" on a daily basis. I wish to to protect the user from copy and pasting twice in a day. What code i need to add in? Column A is Date.


Sub CopyandMove()
Dim i As Long
With Sheets("working")
i = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A1:J" & i).Copy

End With
With Sheets("data")
If IsEmpty(.Range("A1")) Then
.Range("A1").PasteSpecial Paste:=xlValues
Else
.Range("A" & Rows.Count).End(xlUp).Offset(1,0).PasteSpecial Paste:=xlValues
End If
End With
End Sub

 

[venkat1926]

I am wondering whether this will help

I have slightly modified your macro "copyandmove"
I also added another macro "avoid_twice"

NOW ON THE FIRST TIME RUN "COPYANDMOVE"

subsequently either on the same day or the next day run ONLY "avoid_twice"

If you go through "avoid_twice" it will give you an idea what I am attempting

Sub CopyandMove()
Dim i As Long
With Sheets("working")
i = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A1:J" & i).Copy

End With
With Sheets("data")
If IsEmpty(.Range("A1")) Then
.Range("A1").PasteSpecial Paste:=xlValues
Else
.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlValues
End If
[B].Range("T1") = Date[/B]
End With
End Sub

Sub avoid_twice()
With Worksheets("data")
If .Range("T1") <> Date Then
CopyandMove
End If
End With
End Sub

.

 

[Excelnoobisme]

cant. i think just need to incorporate 1 more if statement from my original code to make it work. any1 can help pls?

 

[Nalani]

Just helping out on what venkat had provided.

I think you would need more than just one more line to your exisitng code. You need some way of letting Excel know when you had run the macro.


Try these two macros which can be placed in the same Module. You will have to use the avoid_twice macro instead of your Copyand Move macro:

Sub CopyandMove()
    Dim i As Long
    With Sheets("working")
        i = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("A1:J" & i).Copy
    End With
    With Sheets("data")
        If IsEmpty(.Range("A1")) Then
            .Range("A1").PasteSpecial Paste:=xlValues
        Else
            .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlValues
        End If
        .Range("T1") = Date  'Change this location if needed
    End With
End Sub
 
 
 
Sub avoid_twice()
    Dim Msg, Style, Title, Response
    With Worksheets("data")
        If .Range("T1") = Date Then  ' if changed above - change here also
            Msg = ("Data has already been moved today")
            Style = vbOKOnly
            Title = " SORRY"
            Response = msgbox(Msg, Style, Title)
        Else
            CopyandMove
        End If
    End With
End Sub

Hope that helps.