Copy row to different sheets based on one column while keeping format

Hi guys, I hope someone can help me on this issue I've been working on for the past few days!

I am trying to create a macro to copy entire rows from sheet1 to sheet5,6,7,8,etc. based on column A on sheet1 (sheet 5,6,7,8,etc. will be named after the categories that will be pasted onto it, eg. Mobile, Branch, Adjudicaitons, etc.). Column A, indicating the 'category', has 16 different categories on it eg. Mobile/Online,Adjudication,Branch, etc.. The VBA code im trying to get needs to copy and paste the existing rows on sheet1 to its respected sheet while keeping the original content on these respected sheets, which includes headers, titles, existing data, etc. When new data is entered to sheet1, Id like it to also be distributed to the existing sheet by being added to the first empty row on that sheet so that no information is lost, and when I change data on sheet1, I'd like it to change for the other sheets as well. I do not need the code to create new sheets, as I will do that manually. I also need the format to be kept the same eg. cell and text size and color.

Currently:
-There are 4 sheets, the sheet with the data I need to distribute is sheet2. I will manually create 16 more sheets and put unique headers and stuff in each for data to be copied and pasted into it.
-There are around 80 rows, more will be added daily.
-Columns are from A-Y, but new columns might be added later. Row 1 are all headers, therefor the data that will need to be copied and pasted will start on row 2.
-Some data on sheet2 are refereed to sheet 1, but I just need to copy exactly whats shown on sheet2 to the other sheets.

I am using excel 2010

Thanks for the help guys! :]

Hi! Thanks for the reply.
Unfortunately, my company's computer will not let me post any files onto anything unless its under the company domain.
But if I could type out a better description of my document I would be glad to do so

-Copy rows from one sheet to other sheets based on the text I have in column A, there are 16 different possibilities on column A, therefor the macro will need to copy rows onto 16 different sheets (I will make these sheets manually, and they will be named after the 16 text on column A).
-Keep the format while copying
-Keep the existing information on the sheet the data is being pasted on, therefor the macro will have to paste on the first free row

Thanks again!

Does this help?

Please Login or Register  to view this content.

Hi John,

Thanks for the code. It works to some extent, but it doesn't keep the format (eg. cell and text size) when it pastes the data. Also, I would be adding new information to the worksheet, is there a way for the macro to run continuously so as I add information on one sheet it updates on the other? I don't mind running the macro every time I make an update, but the information ends up duplicating.

Thanks!

It would be better to have something to test it with. On data I made up to pastes all including the formating.

Hi John,

It was a mess up on my part while creating the other sheets for the data to be pasted on. The code works fine
But is there a way to stop the duplication's or have the code run continuously? I will be adding new data onto the master sheet and I need it to update onto the 16 other sheets I have. I don't mind running the macro every time I have an update but the data all gets duplicated each time I run the macro.

Not tested

Please Login or Register  to view this content.

This is an alternate approach using sheet event code? If you use this approach when adding data to the master you'd have to enter data into column a last. Hope that helps.

Please Login or Register  to view this content.

Hi Jindon,

Thanks for the code. But there is an error: "Run-time error '9': Subscript out of range". The error is for the line 'Sheets(e).Cells.Clear'

Thanks for the help

Hi John,

How can I get the private sub to run? It is not showing up on my Macro list and I don't think that it is running automatically because when I made an update to test it, nothing happened.
If we refer back to your first suggestion, can we make the code delete all the existing rows after row 2 for the 16 sheets the data is being pasted on every time I run the code so that there will be no duplicates? Or, if it is easier, every row on column D has unique texts, if the code can detect the text in column D in order to avoid duplication when it is coping and pasting that would work too? :D

Another alternative is too just copy the activerow.

Please Login or Register  to view this content.

Hi John,

That solution will suffice.

Thanks for the help!! :D

Originally Posted by hey1234

Hi Jindon,

Thanks for the code. But there is an error: "Run-time error '9': Subscript out of range". The error is for the line 'Sheets(e).Cells.Clear'

Thanks for the help

I assumed you already have each corresponding worksheets.

Please Login or Register  to view this content.

You're welcome. Glad to help out and thanks for the feedback.