Copying specific data to different sheets

[HXIO]

Hi,

I was wondering is there a way to copy each rows to different sheets(100-200 rows) starting from (A2:J2), and if column F has the same number it will be copy to the same sheet. Otherwise copy it to a new sheet. For the sheet that it will be copying to it will always be copying to row(A2)

The first sheet will have all the data and will be copy to different sheets.


Thanks

[Mumps1]

Do you want to create the different sheets or do they already exist? Do you want to name the sheets based on the contents in column F? Column F in the file you posted is currently blank. What type of data will there be in column F? Perhaps you can post an updated file that contains the data in column F.

[HXIO]

Apologized, its column E. What I trying to achieve was to copy each rows from (A:J) to a sheet that ready exist in this case the first row would be copy to sheet2 then the next row would be sheet3

For example in this case it would be (A1:J1) copying it to sheet2 then (A2:J2) to sheet2 and so on until the last row with data.

The sheets don't need to be name based on any data in sheet1.

Column E sometime has the same numbers and if they are the same the rows with the same numbers will be copy to the same sheet.

[Sintek]

You still don't tell us how the code should know to copy to sheet1, sheet2, sheet3 etc...
What criteria knows which sheet is is to be copied to/
As per Mumps1 suggestion you should upload a sample file with more info and criteria based on you actual file...

[HXIO]

You still don't tell us how the code should know to copy to sheet1, sheet2, sheet3 etc...
What criteria knows which sheet is is to be copied to/
As per Mumps1 suggestion you should upload a sample file with more info and criteria based on you actual file...

Hi sintek, I don't have access to the file and PC at the moment but I will upload one when I have access to them.

As for what criteria for which sheet there's only the criteria of column E if they are the same they will be copy to the same sheet. Otherwise, it will be copy to different sheet.

So basically it's something like this, I would like the code to start copying sheet1 rows starting from (A2:J2) until the last row with data. Each row will be copy to a sheet that already exist starting from sheet2. They will need to be copy to the range (A2:J2) every time.

If row1 and row2 have the same column E number it will be copy to the same sheet for this example it will be sheet2. Otherwise, row2 will be copy to the next sheet which is sheet3. Then continue this until the last row in sheet1.

[Sintek]

Still don't get it...Upload a sample when you have time with a before & after scenario...

[Mumps1]

With Sheet1 being the active sheet, try this macro. Make sure that you have enough existing sheets to equal the number of unique values in column E otherwise the macro will generate an error and some rows will not have a sheet to be copied to.

Please Login or Register  to view this content.

The macro assumes that you have headers in row 1 and the data starts in row 2.

[HXIO]

Hi guys, sorry for the late reply here are the updated version of the example with column F instead of column E, and an afterword example of how I want the data place to the existed sheets.

[Mumps1]

Try:

Please Login or Register  to view this content.

Make sure you have enough existing sheets.

[HXIO]

Try:

Please Login or Register  to view this content.

Make sure you have enough existing sheets.

Hi Mumps1, thanks for the code, but it still not what I'm looking for. It copy each rows but its not copying it into the the range (A2), instead it inserted a copy after the row1. Also is it possible to use column F instead of E, because I'd try to changed it to F and error pops up thanks

[Mumps1]

When I run the macro on the Example2 file you posted, the result is exactly what you showed in the Afterexample file so I'm, not sure what is not working for you. Are you testing the macro on a different file? If so, please attach a copy of that file that is not working for you.

[HXIO]

When I run the macro on the Example2 file you posted, the result is exactly what you showed in the Afterexample file so I'm, not sure what is not working for you. Are you testing the macro on a different file? If so, please attach a copy of that file that is not working for you.

Hi Mumps1, this is what it looks like after I run the marco on Example2 file

[Mumps1]

If you compare the workbook from your Post #12 to the Afterexample workbook you posted in Post #8, you will see that they are the same. I thought that is what you wanted. If it is not what you wanted, then please post a workbook with at least a few sheets with your desired results. You may have to do this manually.

[HXIO]

If you compare the workbook from your Post #12 to the Afterexample workbook you posted in Post #8, you will see that they are the same. I thought that is what you wanted. If it is not what you wanted, then please post a workbook with at least a few sheets with your desired results. You may have to do this manually.

Hi Mumps1,

Apologised, I realised where the problem is now.

But Mumps1 I've another question if I want to use column F instead of E for the IF statement of copying the to the same sheet if they have the same column F number.

Also is it possible to copy only from the range (A:J) instead of the whole row?

I've included some examples

Thanks

[Mumps1]

Try:

Please Login or Register  to view this content.

[HXIO]

Try:

Please Login or Register  to view this content.

Thanks Mumps1

It does just like how I want it, many thanks for helping me.

If its possible can you explain to me how this code whats because I'm trying to learn and get in more depth into excel thanks

[Mumps1]

You are very welcome. Here is the macro with explanatory comments. I hope this helps.

Please Login or Register  to view this content.

[HXIO]

Hi Mumps1,

Here's what I meant when I do this to more than 100 sheets. The pictures that had been copied sometime appear more than once in other sheets. I tried to upload the whole workbook but as its too big it won't let me here's are some examples. Are there anyway to fix this?

[Mumps1]

Try making the pictures smaller or enlarge the cells so that the pictures are entirely within the cell. Also, I'm surprised that the macro didn't generate an error when you ran it. Did you get any errors? You have data starting in row 6 that wasn't in your original post. This data will give inaccurate results for the "LastRow" variable. Also you have merged cells which almost always cause problems for macros. You should avoid using merged cells at all costs.

[HXIO]

Try making the pictures smaller or enlarge the cells so that the pictures are entirely within the cell. Also, I'm surprised that the macro didn't generate an error when you ran it. Did you get any errors? You have data starting in row 6 that wasn't in your original post. This data will give inaccurate results for the "LastRow" variable. Also you have merged cells which almost always cause problems for macros. You should avoid using merged cells at all costs.

So far haven't encounter any errors at the moment. so I need to unmeged the cells before running this code?

[Mumps1]

If the macro is working properly without any errors, then leave it as it is. If it isn't working properly then explain how it is not working. Did re-sizing the pictures solve the duplicate picture problem?