Copy from 'Data' sheet to others

**j3di** · 11-13-2011, 08:40 AM

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:

Please Login or Register  to view this content.

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

Thanks

**jaslake** · 11-13-2011, 05:04 PM

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?

**j3di** · 11-13-2011, 05:12 PM

John, 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?
Am I rambling? !

**jaslake** · 11-14-2011, 11:40 AM

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.

**j3di** · 11-14-2011, 12:13 PM

Originally Posted by jaslake

Hi j3di

I really don't understand what you're telling me herePlease 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. Ans/ Yes, all the records in the data sheet are replaced. You have asked some very interesting questions and on reflection I think it best that if the 'data' sheet is refreshed, that any existing data is removed from the other sheets. This will prevent problems with duplicate information being added or needed to be accounted for.

How is Sheet Data created:
Ans/ I import from a CSV file to the 'data' sheet.

Does new Sheet Data contain all of the old records:
Ans/ New sheet data is created by specifying date criteria (eg from 00:01 10/10/2011 to 23:59:59 12/10/2011) and saved as a CSV file for uploading to this Workbook, so it is possible that repeat information could be uploaded if the dates overlap.

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.
Ans/ Yes, all the records in the data sheet are replaced. You have asked some very interesting questions and on reflection I think it best that if the 'data' sheet is refreshed, that any existing data is removed from the other sheets. This will prevent problems with duplicate information being added or needed to be accounted for.

Thanks for your help, it is very much appreciated!

**jaslake** · 11-14-2011, 12:35 PM

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.

**j3di** · 11-14-2011, 12:38 PM

Yes, I think all the other sheets should be cleared, after a refresh of data.

**jaslake** · 11-14-2011, 12:40 PM

Hi j3di

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

**j3di** · 11-14-2011, 01:55 PM

Many thanks John.

**jaslake** · 11-14-2011, 02:08 PM

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.

**Alf** · 11-14-2011, 05:25 PM

Hi j3di

Interesting problem so I also had a go at it. I've tested deleting sheets to see if they are reinstalled and so far I can see that part works as well as the splitting of data.

I've added a button for my macro "Sort_data".

Alf

**j3di** · 11-14-2011, 07:50 PM

John, many thanks. I have refreshed the 'data' tab with about 8000 lines and it sorts pretty quickly. Works as required, great!
Alf, I had a few problems after refreshing my data - it didn't find the new stuff. Thanks though, if only I could understand half of what was coded here by both of you!

**jaslake** · 11-14-2011, 07:53 PM

Hi j3di

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

**Alf** · 11-15-2011, 02:11 AM

Hi

Glad you got a solution to your problem. For me it's back to the drawing board!

Alf

Copy from 'Data' sheet to others

LinkBack

Thread Tools

Rate This Thread

Display

Copy from 'Data' sheet to others

Re: Copy from 'Data' sheet to others (Needs some tuning)

Re: Copy from 'Data' sheet to others (Needs some tuning)

Re: Copy from 'Data' sheet to others

Re: Copy from 'Data' sheet to others

Re: Copy from 'Data' sheet to others

Re: Copy from 'Data' sheet to others

Re: Copy from 'Data' sheet to others

Re: Copy from 'Data' sheet to others

Re: Copy from 'Data' sheet to others

Re: Copy from 'Data' sheet to others

Re: Copy from 'Data' sheet to others

Re: Copy from 'Data' sheet to others

Re: Copy from 'Data' sheet to others

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions