Splitting the workisheets

[ranik2243]

Hi

This was just an example to explain. I have hundreds of course codes.

As i had already explained earlier i need this by using only macro code not by copy past

This request is going and coming back again with many questions.

I am asking help please understand.


Regards,
Rani

[ranik2243]

Thanks for your efforts.

am aware of copy pasting manually,

however i needed using macro code

Regards,
Rani

[Sisyphus]

Hi Rani,
OK, OK. Remember: Patience is a virtue.
I attach the code you need. Here are the instructions for use.

The entire code is contained in a single module. You should copy that entire module to the workbook that contains your data. Perhaps the easiest way is to drag the module to the other project in the Project Explorer window in the VB Editor.

Before you run the code you should adjust the parameters. They are all at the top of the code sheet:

Code: Select all

Private Enum Orientation
    EnSheets = 1                            ' Sheets in Source Workbook
    EnTitle = 1                               ' Title row
    EnFirst                                     ' First data row in Source sheet
    EnCrit = 2                                 ' Criteria = column B
    EnNumCols                               ' Number of columns in Source Sheet
End Enum

    Const Source As String = "Sheet1"       ' Source Worksheet Name


Enums give names to numbers. It allots numbers automatically with an increment of 1 in each row. You can change the sequence by giving a name the value you want. Then the next value will be higher by one than the last. Therefore, in the above list EnFirst has a value of 2 and EnNumCols has a value of 3. If you change the value of ENCrit the value of EnNumCols will also change automatically. You must allot the corect value to each name using either the automatic increment feature or by entering hard numbers.

EnSheets is the number of sheets in your Source workbook. All new sheets that the macro generates will be added after these sheets. Also, the new sheets will be sorted. If you set a wrong value to EnSheets your existing sheets will end up somewhere among the few hundred new sheets you are about to create.

EnTitleRow is the row from which column titles are copied to the new sheets.
EnFirst is the first row containing data that you want distributed.
EnCrit specifies the column in which the sorting criteria are recorded. 2 = column B. If your data has the criteria in another column, change the value of EnCrit. C = 3, D = 4 etc.
EnNumCols is the number of columns you need to transfer. In the sample datasheet this number was 3. The value affects how many columns will be formatted and titles copied. It has no influence on the data transfer. Entire rows are copied.

The constant Source holds the name of the worksheet containing your data. In the sample data the name of this sheet was "Sheet1". You can insert the name of the sheet that holds your real data. The other three sheets were created by the macro. If you call the macro again nothing will happen because all sheets required have already been created. Therefore, modifications in the original list will not be carried to the child copies.

You can call the macro by clicking Tools/Macros/Macro, selecting SplitWorksheets and press the Run button - then lean back, enjoy a cup of tea and think of your next conquest.
Have a great day!

[ranik2243]

Thanks a lot , its working now

Thanks for explaining me step by step. Very thankful for your time for helping me.

I am thankful to this site which is excellent for knowledge sharing.

Best Regards,
Rani