Transferring data from master sheet to slave sheets

hi all,

i am after a macro to transfer data from one sheet to another based on a column. Please see attached


So i have 6 sheets

Complete Data (master)
here i will cut and paste or add more rows when i get the information comes in. i would say i would have around 500 rows max

in Column F i give the person a catergory.

once i select that catergory i would like the whole row of that information to transfer to that catagory sheet

example: i place Unknown 1 in Column F, that goes to unknown 1 sheet

Hope that makes sense

Right click on the "Complete data" sheet tab and select "View Code" then paste the below into the window that opens.

Please Login or Register  to view this content.

That will move the row of data to a specified sheet when you complete column F for that row.
It will work up to row 1000. You can extend that in the code if necessary, of even make it dynamic.

It would need error handling in case of a value being recorded in column F that doesn't have a sheet with the same name.
I've not done that as I'd assume that column F is populated (in your real workbook) by data validation to avoid this happening.

You could use very similar code to loop through all existing rows and distribute them between relevant sheets. Let me know if that's something you need.

BSB

Hi BSB

mate that works great, i would be applying this to a work sheet thats already got alot of data on already, could i get the code to loop that?

once i have loop that code does it mean it will continue to work or would i have to then delete it and use the one above?

BSB,

just a quick one, i will be changing the catagory in complete data, is there a code that would then delete it from the catagory it was in and then add to other?

so i place lee mac in application cat, then he moves to entrant. i need it to remove from application and add to entrant sheet

BSB

paste what mate?

Yes i can have each person a unique ID from 1-1000 mate to start with. only issue would be some people may need to be removed from complete data sheet as well

Sorry mate, completely forgot to add the code there.
My previous post now amended to include it.

If someone was deleted completely from the sheet would you recycle those IDs?

BSB

i would just assign a serial number in the work sheet from 1 to whatever, these could be recyled or just left mate so the sheet looks like this, whatever is best

1
3
4
5
9

i would just assign a serial number in the work sheet from 1 to whatever, these could be recyled or just left mate so the sheet looks like this, whatever is best

1
3
4
5
9

Replace the code in post #2 with the below. If you change a value in F on the master sheet it will loop through the sheets (other than the master sheet) and delete the row with that ID Number, then carry on as before (copying the row to it's new relevant sheet).

Please Login or Register  to view this content.

This just leaves you to add a method for deleting all instances of an ID Num. If you need help with that I'll be back after I've shovelled dinner down my throat and can help further.

BSB

Hi mate,

I'll try it out mate

I just wondering as all my work is on main data sheet would it not be best for the macro to wipe data across the sheets and the repopulate with all new data?

The other sheets are basically for reports, tables and email macro for those on that sheet.

You certainly could do it that way. I don't think there would be much difference in the length of the VBA to clear the sheets and repopulate them from the master sheet compared to the below, or much difference in the processing time either.

Paste the below into the regular module and run it (Alt+F8 / Run. Or you could attach it to a button). An input box will appear asking for the ID Number you wish to delete. It will then loop through the sheets, including the master sheet, and delete any rows that pertain to the value you enter.

Please Login or Register  to view this content.

All of this has been added to the attached file.

BSB

BSB,

ill be cutting more data in all the time, could you do me a favor and combine both codes.

basically so i can work work on the data sheet, adding data, deleting rows, changing catagory. then i jyst predd the macro and it deletes all sheets and then readds the new data?

sorry if i am being a pain mate

this will hopfully stop duplicate rows then mate

Not sure I understand.
You want a macro that deletes all data on anything other than the master sheet then redistributes it all again?
And you want that process instead of which above?

BSB

BSB,

mate it works great, only issue i see is if i assign the macro distrubution to a button and i press it, it duplicates the information. so was hoping you could but a clear all code in there.

does that make sense? if not no issue as i can manualy clear all sheet contents

Aha, I see. Try this updated code:

Please Login or Register  to view this content.

BSB

mate,

this is fantasic thank you so much, i will apply all these to the worksheet at work, if i have any issues are you ok for me to PM you mate

No probs mate. Happy to help

If you need anything more on this, drop a message in this thread and it will pop up in my notifications.

Thanks for the rep point

BSB

sorry mate, last question

the code you done for sheet 1 (there updates and moves row of data) that had a range to f1000. Does the macro for distrubution have a limit or is it written in a way that if looks for the whole of column F

This line deals with that:

Please Login or Register  to view this content.

It starts the range at F2 then performs the equivalent of Ctrl + Down to find the last populated row in column F. It then performs the loop that copies to the other sheets for each row in that range.

So long as there are no blanks in this row all should work well and not need to be adjusted as you add/remove data to/from the master sheet.


You could use the last row approach for the sheet change event to negate the need for specifying a range. That's what I meant early on when I said you could make it dynamic.

BSB

Mate, got it! again thank you so much

BSB,

mate, i will be cutting and pasting a lot of data, which is repeated every week. Do you know of a good macro that can look for duplicates across the rows (matching three columns say b,c and d) and then delete them?

If you're checking for duplicates based on just those three columns, does that mean data in the other columns of a specific row in the new data could/would/should be different to the matching row in the old data?
If so, which would you keep and which would you delete?

Personally I'd go for a macro that allowed you to specify the file to import and do the duplicate check and deletion by looping through it row by row on the way in.

BSB

BSB,

basically i will be send a weekly update from the Army recruiting group (this will have some new guys but alot of guys info that i already have), this will be an excel spreadsheet with a number of columns but ill only need 5 columns out of 10 ish (recruit id, surname,first name email and phone number). ill cut those 5 columns into my spreadsheet, then run a duplicate search.

sent not send

BSB,

i could upload both work books tomorrow for you to look at if that would help

BSB,

i have tried to add the macro and sheet code to the new test sheet and i am getting error codes.

sorry about this mate, but could you take a look please

BSB,

mate sorry, i was overlooking a sheet and bad spelling mate.

i need some help with other areas if you can help

BSB

i think i am now taking the mick lol,

I am struggling with the following.

1. I cannot get the distribution macro to distribute without deleting workings and overview sheets

2. the automatic update macro when I change the category has an error message.

3. the two buttons I added to auto sort alphabetic and date order, it only works to where I have set it (example ser 700 ish), is there a way it can check the sheet and if the cells are filled in then it sorts it

4 . the add new line, can you make so it sorts out the ser 1,2,3,4 etc in left hand column.

1. Change this line:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

This will ignore the Overview and Workings sheet when distributing the data.

2. Change this line:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

and the error will not appear.

3. You can do this by ascertaining the last row and using that to specify the range to sort. Post the code you have to sort the data and I'll amend it to do what you need. I don't seem to be able to find it in the attached workbook.

4. You mean you want it to increment the number in column A each time you add a new line? What code do you have attached to that button? I cannot find this in the attachment either.

BSB

mate they work a treat, some reason the codes are missing. ill redo and send them on here.

4. yes i would like it to move the numbers down.

BSB,

This is the macro i have for new row, copyies the formulas down but aslo the guys details, i just need the row blank. also in Column A it does not move the number down just copies it

Sub Copy_One_Row_Below()
With Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 25))
.Offset(1).Insert shift:=xlDown
.Copy
.Offset(1).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
.Offset(1).PasteSpecial xlPasteFormats
Application.CutCopyMode = False

End With
Cells(ActiveCell.Row + 1, 2) = "=" & Cells(ActiveCell.Row, 2).Address & "+1"
End Sub

BSB,

I hope this makes sense, I get given another workbook with a sheet called Rifles on it, the spreadsheet column run from B - U, is there away I can run a check and compare F and G on the Rifles against C and E in my work book. if a duplicate is found it then deletes the whole row from Rifles sheet?

What would be better is if a box appeared that I could tell them which columns to compare as I do get other sheets sent to as well? if that's to much just say, mate

Merry Christmas BSB and thank you for your help