Copy from 'Data' sheet to others

The attached file shows where i'm at. What it does is sort the 'Data' sheet onto named sheets according to the number within the data in column 4. I have working code but with over 7k lines in the 'Data' sheet the operation is a little slow. Can anyone suggest some changes to help? I have heard that cut n paste is the slow way of doing things!

Any suggestions on how I can combine another function so that when the 'Move Stuff' routine is called, it checks for duplicate data in the target sheet and deletes it?

Here is the main code:

Sub MoveData()
Application.ScreenUpdating = False
Dim PointNo As String
Dim LastRow As Long
Dim Text As String
LastRow = [A65536].End(xlUp).Row
For I = 2 To LastRow Step 1
Sheets("Data").Select 'Select the DATA tab
Text = Cells(I, 4) 'Text = value of cell i:4
PointNo = Mid(Text, 3, 4) 'start at 3 and next 4 chars
Rows(I & ":" & I).Copy 'copy entire row
Sheets(PointNo).Select
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Select 'select next line on that sheet for next visit
Next I
Sheets("Data").Select
MsgBox "Sort Complete", vbInformation
Application.ScreenUpdating = True
End Sub

Also there are no error traps should something dodgy be encountered, ie Destination sheet doesn't exist.

Thanks

Hi j3di

This would infer that you wish Target Sheet to be cumulative...is this the case?

when the 'Move Stuff' routine is called, it checks for duplicate data in the target sheet and deletes it?

If this is the case I'd assume the "Old" record should be replaced by the "New" record. Is this the case?

Hi j3di


I really don't understand what you're telling me here

I would like to be able to refresh the information in 'data' but I don't think that will create duplicates, so in that sense each number tab should maybe be sorted by it's date/time column? What would happen if the 'move data' routine was called more than once? That would create duplicates which would be a problem. So to answer your question, the data should be appended each time but without duplication

Please lead me through this:

• How is Sheet Data created
• Each time Sheet Data is created does new Sheet Data contain ONLY new records OR,
• Does new Sheet Data ALSO contain ALL of the old records
• If new Sheet Data includes new records and all of the old records, are the new records appended to the bottom of old Sheet Data or are all the records in Sheet Data replaced.

Hi j3di

Regarding this

I think it best that if the 'data' sheet is refreshed, that any existing data is removed from the other sheets

Do you wish for existing data to be removed from ALL the other sheets or only from those sheets impacted by the refreshed Sheet Data?

Removing it from ALL the other sheets is rather simple...you've already written the code for that.

Hi j3di

I'll work on it. Should have something for you to look at early this afternoon.

Hi j3di
The code in the attached contains two new modules (MoveStuff_1 Module and WipeStuff_1 Module). I've commented the code to show you what the code is doing. If you have any questions regarding what the code is doing please ask.

Let me know of issues.

Hi j3di

You're welcome...glad I could be of help.