I need help when archiving data from one worksheet to another using VBA

samunders

New Member
Joined
May 26, 2020
Messages
27
Office Version
  1. 2019
Hello i am trying to create a sheet that will auto copy an entire row to the achieve sheet and then delete the data from the copied row.

Its essentially an order form and once the object has arrived then it needs to move to the archived sheet for records, and removed from the "outstanding sheet"

I have managed to sort the copy part with the following VBA but cannot seem to figure the delete row section, once its been copied.

current VBA:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 And UCase(Target) = "ARRIVED" Then
Cells(Target.Row, Target.Column).EntireRow.Copy Destination:=Sheets("19P1 ARCHIVE").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Application.EnableEvents = True

End Sub



any help would be greatly appreciated.

many thanks
 

Attachments

  • excel.png
    excel.png
    142.3 KB · Views: 11
  • excel1.png
    excel1.png
    99.4 KB · Views: 11

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Welcome to the Board!

Try something like this:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 9 And UCase(Target) = "ARRIVED" Then
        Cells(Target.Row, Target.Column).EntireRow.Copy Destination:=Sheets("19P1 ARCHIVE").Range("A" & Rows.Count).End(xlUp).Offset(1)
        Application.EnableEvents = False
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
End Sub

By the way, you should also be able to replace this line:
VBA Code:
Cells(Target.Row, Target.Column).EntireRow.Copy Destination:=Sheets("19P1 ARCHIVE").Range("A" & Rows.Count).End(xlUp).Offset(1)
with this simpler version:
VBA Code:
Target.EntireRow.Copy Destination:=Sheets("19P1 ARCHIVE").Range("A" & Rows.Count).End(xlUp).Offset(1)
 
Upvote 0
You absolute wizard!! That worked perfectly!

I'm not sure if its possible (I'm sure you wizards will know ;)) but is there away that after the entire row has been copied and then deleted, is there a easy macro that will then reinsert a new row in the form to keep 10 rows for items?
When I try to do in manually I get an error I will add a picture.

Thank you so much again!
 

Attachments

  • Excel 1.png
    Excel 1.png
    129 KB · Views: 4
  • Excel 2.png
    Excel 2.png
    95.3 KB · Views: 4
Upvote 0
You absolute wizard!! That worked perfectly!

I'm not sure if its possible (I'm sure you wizards will know ;)) but is there away that after the entire row has been copied and then deleted, is there a easy macro that will then reinsert a new row in the form to keep 10 rows for items?
When I try to do in manually I get an error I will add a picture.

Thank you so much again!
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 And UCase(Target) = "ARRIVED" Then
        Cells(Target.Row, Target.Column).EntireRow.Copy Destination:=Sheets("19P1 ARCHIVE").Range("A" & Rows.Count).End(xlUp).Offset(1)
        Application.EnableEvents = False
        Rows(Target.Row).Insert
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

I think that has sorted it?
 
Upvote 0
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 And UCase(Target) = "ARRIVED" Then
        Cells(Target.Row, Target.Column).EntireRow.Copy Destination:=Sheets("19P1 ARCHIVE").Range("A" & Rows.Count).End(xlUp).Offset(1)
        Application.EnableEvents = False
        Rows(Target.Row).Insert
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

I think that has sorted it?

It has sorted it however i have 1 Problem which i can solve by deleting Column A, B, C. however the sheet wont copy unless there is data in column "A" ! solved this by putting a "." in that column and colored it the same as the background. but when i insert a new row the dot inst there can I macro a "." in column "A" of the inserted row?

Thank you so much again, I have learnt so much already from watching and reading these threads.

Sam
 
Upvote 0
Instead of deleting, then inserting a row at the same spot, why not just clear the row instead, i.e.
VBA Code:
Rows(Target.Row).ClearContents

If you wanted to put a "." in column A of the row just copied over, here is one way:
VBA Code:
Cells(Target.Row, "A") = "."
 
Upvote 0
You have DONE it and I cannot thank you enough! Didn't know "clear contents" was a thing, I'm such a ROOKIE!

Thank You Thank You Thank You!
 
Upvote 0
You are welcome!

Yes, ClearContents just erases all the values in the row without getting rid of the row, just leaving behind a blank row.
The Delete command actually deleted the row, so all the other rows under it move up one.

I'm such a ROOKIE!
We were all rookies at one time!
:)
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top