Macro to split a cell data into rows and copying other cells as it is in rows

[shaan65]

Plz find the attachement in which there are some cell in column D which have data like S22A/S22B/S22C/S22D or BS12/BS17/BS07/BS03 like that to split these in row and also to copy the data of other row & column.

[stanleydgromjr]

shaan65,

Received your Private Message.


Detach/open workbook SplitDataV2 - shaan65 - EF754025 - SDG13.xls and run macro SplitDataV2.


If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.

[shaan65]

thanks brother

[stanleydgromjr]

shaan65,

You are welcome.

If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.

[shaan65]

Hi,

Thanks a ton for your very quickreply. However, an implication exists herein running
this macro considering this below scenario. Suppose, I have 100 Workssheets in a
single Xlssheet. Should I run same macro for each and evey sheet seperately so it
results100 Sheets that is a tiresome task. Could you please update that produce the
results in 1Go?

Also, in the macro i want that what ever the changes we are making through macro should be highlighted or shaded in different colour.

for reference you can take my earlier attachment which has two sheets i want the same macro for the both sheets which should also highlight the changes and work for both sheets.

Regards,
Shankar

[stanleydgromjr]

shaan65,

The following macro will cycle thru all the worksheets in the workbook.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Then run the SplitDataV3 macro.

[shaan65]

Hi Stan(GENIUS),
Thanks brother....your code is working fine but i have some more issues could you plz help me resolving this also......

1. I want to delete all Blank rows & Columns.
2. I want to remove all 'spaces', 'tab', & 'enter' in all rows & columnns except column A&B.
3. Also i want to add 4 spaces in each cell of Column D(see my attached sheet).
4. Also it should split the data wherever '/' founded like earlier.
5. Above changes should be highlighted.

Plz refer my attacment of the earlier post(first one) as i have to make this changes in the same sheet. plz reply

[stanleydgromjr]

shaan65,

I am having a problem opening your original workbook.

Please post a new workbook, with two worksheets as before, and with two additional worksheets labeled Results1 and Results2 (that are what the two worksheets as before should now look like when the macro is finished), by clicking on the New Post button, then scroll down and click on Manage Attachments button.

[shaan65]

Thanks for your Reply Brother,

I am attching here the documents as required by you.
Please resolve my issue as earliest.

[stanleydgromjr]

shaan65,

The following quote may have confused you.

Please post a new workbook, with two worksheets as before, and with two additional worksheets labeled Results1 and Results2 (that are what the two worksheets as before should now look like when the macro is finished), by clicking on the New Post button, then scroll down and click on Manage Attachments button.

Please re-post your workbook:

With worksheet Cards - Special Handling followed by worksheet Result1. Worksheet Result1 should be manually formatted by you to show the results of worksheet Cards - Special Handling.

With worksheet NonCards - Special Handling followed by worksheet Result2. Worksheet Result2 should be manually formatted by you to show the results of worksheet NonCards - Special Handling.

[shaan65]

Hi brother i am attching here the sheet again plc help me soon

[stanleydgromjr]

shaan65,

You are not following my instructions.

One last try.


Detach/open workbook SplitDataV2A - shaan65 - EF754025 - SDG13.xls.


On worksheet Result, manually change this worksheet to your desired output.


Then re-post workbook B]SplitDataV2A - shaan65 - EF754025 - SDG13.xls[/B], and, I will update the macro to match worksheet Result.

[shaan65]

Hi Stanley,
Thanks for ur help brother my issue has been resolved and m going to put this thresd in SOLVED.
Many many thanks for all ur help and support