1

I'm a total novice when it comes to VBA and have created my VBA based off tutorials online. A bit of background: my dataset is made up of 3 sheets with different information, if data is entered into the first sheet with a specific criteria, that whole row then moves into the second sheet, where further can be entered. if on the second sheet an option is selected that entire row must then move to the third sheet. So essentially i will only ever have 1 copy of any one record.

The problem i am having, is that when the row is cut from sheet 1 to sheet 2, i then cannot then edit that particular data in sheet 2 without a duplicate copy being created, its as if the paste hasn't been found even though the pasted data is there and is editable.

I am using an index row if that is helpful. cut cells are pasted to new sheet and the row deleted from the original sheet

please someone, anyone who can fix this it would be greatly appreciated.

here is the code for the cut/paste

Private Sub 
    CommandButton4_Click()
    a = Worksheets("Database").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To a
        If Worksheets("Database").Cells(i, 16).Value = "Retention" Then
            Worksheets("Database").Rows(i).Cut 
            Worksheets("Archive").Activate
            b = Worksheets("Archive").Cells(Rows.Count, 1).End(xlUp).Row
            Worksheets("Archive").Cells(b + 1, 1).Select
            ActiveSheet.Paste
            Worksheets("Database").Activate 
            Rows(i).EntireRow.Delete
            MsgBox "ARCHIVE RECORDS UPDATED", vbInformation 
        End If
    Next
End Sub

(this is duplicated for 2x sheets) –

Michael Hey
  • 11
  • 2
  • should also say this is VBA Excel – Michael Hey Aug 08 '21 at 09:50
  • 1
    If you're asking a question for help with your code, you need to provide it for us... – Simon Aug 08 '21 at 10:02
  • apologies, here is the code for the cut/paste Private Sub CommandButton4_Click() a = Worksheets("Database").Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To a If Worksheets("Database").Cells(i, 16).Value = "Retention" Then Worksheets("Database").Rows(i).Cut Worksheets("Archive").Activate b = Worksheets("Archive").Cells(Rows.Count, 1).End(xlUp).Row Worksheets("Archive").Cells(b + 1, 1).Select ActiveSheet.Paste Worksheets("Database").Activate Rows(i).EntireRow.Delete MsgBox "ARCHIVE RECORDS UPDATED", vbInformation End If Next End Sub (this is duplicated for 2x sheets) – Michael Hey Aug 08 '21 at 14:42
  • Please don't post it in the comments. Edit your original question and use the code tags provided. – Simon Aug 08 '21 at 18:50
  • Apologies, I am new to all of this. – Michael Hey Aug 09 '21 at 07:25
  • So you're looping through your database sheet to find "Retention". If found, then you cut it from Database sheet and paste it to the bottom of the Archive sheet. Do you want it to stop once it does this? Or do you find all cells that contain "Retention" and do it for all of them? Cause at the moment if there is more than one your msgbox pops up for all of them. There are several ways to improve your bit of code you have, but I'm unsure what your actual problem is. – Simon Aug 09 '21 at 08:30
  • Hi Simon, so for the 'retention' records, no I would like it find all records that say retention and move them, these records are then locked so they cannot be edited from form. The Issue I'm having is when I copy and paste records from 'Assessments' to 'Database' it copies and pastes it fine, but when I go to edit the copied record via the form, it creates a duplicate copy which is editable, the original row just sits there, and every time i try to edit the original row, it simply creates a new copy each time the have a new index number. – Michael Hey Aug 09 '21 at 08:49
  • Sorry to be commenting as an answer, not enough reps to comment yet. Just a few reminders. Michael, you don't need to select cells and activate sheets for most things in Excel VBA. It will slow your codes. You can also read on Application.EnableEvents, Application.ScreenUpdating and Application.Calculation for general speed gains. This might be a good read: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba/35864330#35864330) – Gokhan Aycan Aug 08 '21 at 22:04
  • There is clearly some other code at work here. We need to see this form/other code cause what you've provided can't be doing what your saying. Maybe record a gif of it doing what's wrong (Would be helpful to visualise), or provide the workbook itself with any sensitive info removed (best option), or provide the full userform code. As I say there has to be some other code doing this so without more info no one can really help you. – Simon Aug 10 '21 at 06:27

1 Answers1

0

When running through a collection where items need to be removed, don't go from start to end, but from end to start.

Let me give you the example of removing duplicates of the list A:

index value
    1     1
    2     2
    3     2
    4     2
    5     3

Perform this simple pseudo-code:

for (i = 1 to 4)
{ if A(i) = A(i+1) then remove A(i) }

This is the result:

i  A before       A afterwards
1  1,2,2,2,3      1,2,2,2,3
2  1,2,2,2,3      1,2,2,3
3  1,2,2,2,3      1,2,2,3
4  1,2,2,3        => either stop or error

And now in the other sense:

for (i = 5 downto 2)
{ if A(i) = A(i-1) then remove A(i) }

i  A before       A afterwards
5  1,2,2,2,3      1,2,2,2,3
4  1,2,2,2,3      1,2,2,3
3  1,2,2,3        1,2,3
2  1,2,3          1,2,3
Dharman
  • 30,962
  • 25
  • 85
  • 135
Dominique
  • 16,450
  • 15
  • 56
  • 112