Macro to Delete useless lines, rearrange by dates and skip lines between different days.

Sort Columns Appropriately

Tmc2159,

Welcome to the Excel Forum.

Step 1:
I want to delete any row which has the words "Not Given." in Column J.

Step 2:
I want to sort all the Rows by the date given in Column C.

Step 3:
I want to arrange the Number together.
Meaning all the Mach 1 should be together and Mach 2 should be together if they have the same date.

Step 4:
I want to skip a line when the date changes.

Step 5 ( Optional):
I would like my different dates to be seperated in different sheets but that probably can not be done in excel.

All of the above is doable.


When we create the new worksheets for the dates in column C, Excel will not allow us to use this format "5/2/2012" (we can not use the / character in a sheet name.

We could use 5-2-2012. Is this acceptable?


Can you supply another Template.xlsx file, containing the same Data worksheet, and containing (manually formatted by you) what the worksheet for the date "5-2-2012" will look like (titles, data from worksheet Data, etc.)?

And, can we have another worksheet Temp, that will show us what worksheet Data will look like after the macro?

Try the code below, which should take you up to the optional item. It is doable but it's a bit late/early here for me to mess with it today.
P.S. turn your filter off before running the code.

Please Login or Register  to view this content.

Tmc2159,


Detach/open workbook Template_Tmc2159_EF837368_SDG15.xlsm and run the UpdateCreateSheets macro.


If you want to use the macro on another 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).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the UpdateCreateSheets macro.

Tmc2159,

OK, now we have six steps to the macro.

Step 1: I want to delete any row which has the words "Not Given." in Column J.
Step 2: I want to sort all the Rows by the date given in Column C.
Step 3: I want to arrange the Number together. Meaning all the Mach 1 should be together and Mach 2 should be together if they have the same date.
Step 4: I want to skip a line when the date changes.
Step 5: I would like my different dates to be seperated in different sheets but that probably can not be done in excel.
Step 6: However i want the lines for which the equipment is not running to be automatically not to appear on the second sheet.


Are the above six steps correct?

Changed Question My fault End of this Thread

Tmc2159,

So, you want to go just from sheet Data to Sheet1?

What ever happened to a separate sheet for each date?

Your latest template shows Sheet1 with "Equipment Not Running." rows.

And, the data is not sorted by date, and then by the "Mach's"?

We need another template that shows Sheet1 correctly.

Tmc2159,

I am totally confused from all the changes you have gone thru.

In the Quick Reply box, just put the word BUMP. Then, click on the Post Reply button, and someone else will assist you.

Issue was Solved