Macro to Tabulate 100k Data into Individual Sheets

[leeaw]

Hi,

I need to copy/move data from the main database to individual sheets based on the number of assets.

My number of assets can ranged from 1-1000.
Is it possible to automate the creation of sheets based on the number of assets?

The file is too big. But my rows = 102k, columns from A - V.

[arlu1201]

Should the sheets be created based on the asset_ID? Should the sheet be named the same as the asset_id?

[leeaw]

The sheets can be named according to asset id but the sheets must consist of the same total number of assets.

Means i need to consolidate all the same number of assets into one sheet. (eg. 1 asset into 1 sheet, 100 assets into 1 sheet)
if there are more than 1 person who have 1 asset then should be consolidated into 1 sheet.

I have this macro but it can only hold 30 arguements.And i have to manually create 1000 sheets.
shDest = WorksheetFunction.Choose(AllCells.Rows.Count , "2 Assets", "3 Assets", "4 Assets", "5 Assets")

Please Login or Register  to view this content.

[Cutter]

@ leeaw

Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
Thanks.

[jindon]

Try this

Please Login or Register  to view this content.

[leeaw]

Hi Jindon,

I have used the macro. But it does not move the number of assets to the correct sheets. But rather it actually shifted the assets to different sheets based on the project id.

The sheets should be based on the numbers of assets i have. (eg. Sheet 1 should comprise of all 1 asset, sheet 2 (2 assets) ... )

eg. In the data Andrew has 3 assets based on the S/NO. Peter has 2 assets based on the S/NO.

1. Notebook Andrew
2. PC Andrew
3. Scanner Andrew
1. PC Peter
2. Notebook Peter
1. PDA Jackson
2. PDA Jackson
3. IPAD Jackson
4. IPhone Jackson

[jindon]

Do you mean like this?

Otherwise I need a workbook with your desired result.

Please Login or Register  to view this content.

[leeaw]

sorry for the inconvienced. The macro duplicated from the Main Sheet. Most likely is because all have number 1 in it.

Please see the attachment.

The main is the database. It should rightfully go into the respective Asset sheets.

[jindon]

According to your code

Please Login or Register  to view this content.

It is adding the records after the existing records.

Do you want refresh all the sheets and paste the records as new?

[leeaw]

where do i paste the records? Thanks

[jindon]

where do i paste the records? Thanks

What do you mean? thanks.

[leeaw]

sorry i misunderstood your question.

Should be paste the records as new into the individual sheets.

[jindon]

I don't understand what you mean.

try

[leeaw]

yes yes this is the one. Thanks!!! it works.