Data into multiple worksheets based on column in Excel and apply auto filter and freeze

Hello Gurus,

I am using below VBA code for split data into multiple worksheets based on column in excel and in addition to that i am looking to apply below ones aswell

1. Apply Autofilter on all worksheets
2. Apply Freezepanes top row.(header) on all worksheets.
3. In my data I have numbers in column A (1,2,3,4,5..till 15). I want to add/suffix alphabet to each number as "P". I don't want to add in my data but I want to add this specific letter alphabet "P" to all 15 worksheets tab(name of worksheet) (for example: P1,P2,P3,P4...P15)
4. Instead of calling this code as macro from excel, I want to call this code as batch script.

below code shows only split data into multiple worksheets based on column in excel, sorry if am using other forum/website URL in our site.

https://www.extendoffice.com/documen...on-column.html

Thank you in advance.

Hi skumarlingam,

See the attached zip file which contains the following 3 files:
a. ExcelForumStealthProcessing.bat - Batch file which opens ExcelForumStealthProcessing.xlsm
b. ExcelForumStealthProcessing.xlsm - Excel file with macros that does what you asked for.
c. ExcelForumStealthProcessingTarget.xlsm - Simulated data file with no Macros.

The .bat file contains the Folder and File Name of the Excel Data file. You have to change the names to suit your computer. The Excel file with macros contains the Folder and File Name of the Excel Data file in cell A21. You have to change the names here also.

If this does not answer your question, please let me know what you need. A sample file with before and after is best.

The batch file contains the following code:

Please Login or Register  to view this content.

The Excel file with Macros contains the following code:

In the ThisWorkbook module:

Please Login or Register  to view this content.

In an ordinary Code module such as ModStealth:

Please Login or Register  to view this content.

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

Lewis

Thank you very much LJMetzger. Let me run I will keep you updated.

Hi TJMetzger,

Thank you for sending excel,macro and bat script.

after downloading zip file(includes bat and two excel files). when I ran for testing, bat ran successfully and worksheet names renamed,autofilter and freeze top pane is working. somehow data is not showing what I was looking for.

and when am trying to open excel file (ExcelForumStealthProcessing.xlsm) it's opening and closing.its not open(ed) continuously.

after batch script completes operation I received error pop-out "Microsoft Excel Stopped working"

I am attaching sample excel file along with sample data. In excel B2 column data have (1,2,3,4,5,6,7,8,9,10,11,12,87).

I need to show B2 column data split in different worksheet in same spreadsheet i.e. VB code should create new worksheet as P1 all 1 relative data and create second worksheet as P2 all 2 relative data...like for P3,P4..P12 and P87
and freeze top row and apply auto filter to all worksheets.

Master worksheet data should remain same as-is.

Below URL -VBA code can split and create new worksheet in same spreadsheet only misisng is auto filter and freeze top row and making VBA code as batch script.

https://www.extendoffice.com/documen...on-column.html

Thank you so much for your help.

sorry I misspelled your name.

Hello LJMetzger,

Please see attachment for before and after excel.

Thank you in advance.

Hi,

See the attached zip file which contains the following 3 files:
a. ExcelForumStealthProcessing2.bat - Batch file which opens ExcelForumStealthProcessing.xlsm
b. ExcelForumStealthProcessing2.xlsm - Excel file with macros that does what you asked for.
c. List_5716611_before.xlsm - Your raw data file

AutoFilter was off before, because the parse_data() routine you used previously put AutoFilter on, and my software undid the AutoFilter. I removed the AutoFilter from my software.

I do not understand what you mean by 'Batch Processing. To me it means all processing is done by the 'Batch File' and then all resources are closed. If it means something different to you, please let me know what resources you want open when processing is completed.

Lewis

Perfect, Thank you. I downloaded latest zip file and I ran it. everything looks good now.

Sorry I don't know what happen for previous version zip file. After I ran it my excel stops working may be something wrong with my system or excel.

Small glitch can you please help me on this.

1. Can we avoid extra auto filter in all worksheets till Column "K" we have data. We don't need auto-filter from Column "L". (please see attached screenshot)
2. Can we apply auto-filter and freeze top row for "Formatted" worksheet as well.
3. If I run second time same batch file it suppose to update same worksheets, but here it creating new worksheets in addition to previous worksheets. and throws run-time error which is expected since worksheet already created.(please see attached screenshot)

Since I want to run this batch daily.

Once again thank you so much LJMetzger

Sorry if I confuse you.

Thank you.

The 'AutoFilter' problem is easy to fix. I will remove the 'AutoFilter' from the 'parse_data' routine, which puts AutoFilter across all columns, and put my 'AutoFilter' code back in

The code as written is designed to run once on a given file as you have discovered and understand.

I am confused about what you want on iterations 2 thru n.

a. Currently (except for AutoFilter) the code seems to do what you want the first time.
b. I need to know what you need on subsequent iterations.

Is new data appended to the existing Tabs?
Will there be any duplicate data?
If there is duplicate data, does the data get ignored, or appended to the existing data?

Thank you for your reply.

Yes, Except auto filter the code works perfectly what i need for.

sorry my bad i should say in initial discussion.

Data updates daily on excel, and records will be distinct.(except B2 column, since B2 column have multiple 1's,2's,3's...12's and 87's)

Scenario will be:

Step1. Excel updates morning at 9am (only one worksheet will be available inside i.e. "Formatted")
Step2. Batch will run @9:15am and apply my needs on same excel and split data based on B2 column, create new worksheets in same excel and apply auto-filter and freeze top row on all worksheet including "Formatted" worksheet.
Step3. If I run batch file second time on same day, code should update same worksheets. not to create new worksheets again.
Step4. Next day again when batch runs via windows task scheduler same Step1 and Step 2 should repeat.

Thank you very much again and sorry for confusion. please let me know if any points makes you confuse.

Hi,

I think I understand now.

See the attached zip file which contains the following 3 files:
a. ExcelForumStealthProcessing3.bat - Batch file which opens ExcelForumStealthProcessing.xlsm
b. ExcelForumStealthProcessing3.xlsm - Excel file with macros that does what you asked for. It contains several changes. I was able to modify the Parse routine so that it now also works in Excel 2003.
c. List_5716611_before.xlsm - Your raw data file. Please note that Tab 'Formated' is spelled with one 'T'.

To access file ExcelForumStealthProcessing3.xlsm without it running and closing:
a. Open Excel
b. 'Left Click' on any cell in the Excel Spreadsheet.
c. ALT-F11 to get to VBA.
d. Press CTRL 'G' to open the IMMEDIATE WINDOW.
e. In the 'Immediate Window' type 'Application.EnableEvents = False' followed by the 'Enter Key' (no single quotes).
This disables Excel Events (Macros from automatically running).
g. To enable events (automatic macros) when you are done, in the 'Immediate Window' type 'Application.EnableEvents = False' followed by the 'Enter Key'

File ExcelForumStealthProcessing3.xlsm contains:
In the ThisWorkbook code modue:

Please Login or Register  to view this content.

In Ordinary Code module 'ModStealth':

Please Login or Register  to view this content.

In Ordinary Code module 'ModParseDataNew':

Please Login or Register  to view this content.

Lewis

Lewis, Thank you very much, you are my hero. It works perfectly fine. you made my day.

I renamed "Formted" to "Formatted" Thank you.

When I schedule batch ExcelForumStealthProcessing3.bat from my windows task scheduler. I am receiving weird warning message may be because of too long filename path?

Thank you in advance

I am receiving weird warning message may be because of too long filename path?

Verify that the file is actually in folder C:\Windows\System32. My speculation is that the file is in a different folder.

I don't use the Windows Task Scheduler so I don't know if there are any path size limitations.

However, the error message is from Excel, which means the Batch file ran, and Excel couldn't find the file ExcelForumStealthProcessing3.xlsm. When I tested the Batch file, I 'Double Clicked' the .bat file from Windows Explorer and I had an extremely long path to the Excel file and had no problems running on Windows Vista.

Thank you for your response.

Even when I double click on batch it ran fine without any issue. when I schedule using windows task scheduler. it throws error.

I copied all files (batch and two excel files) over to location in C:\Windows\System32 still I received same error.

Thank you.

Lewis, Everything working fine now. I moved file to my server its working fine now using windows task scheduler.

I didn't do any modifications neither on batch file nor excel spreadsheet.

Thank you once again for helping me.

It was my pleasure to help. I'm glad it worked out for you.