Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Create a new sheet for every Unique value in selected filltered Column

0

Dear Sirs,

My project is to Create a new sheet for every unique value in selected filtered column.

The project is divided in 2 steps

1.Creating userform to specify data column for filtering and extraction .

This steps has been completed by the help from Variatus in the following link

https://www.teachexcel.com/talk/3075/how-to-create-userform-to-specify-data-column-for-filtering-and-extraction#discuss_section_3077.

The code is written in the attached TXL 181005 Purchase Report.xlsm file  and the data file is Purchased report.xlsx 

By clicking the green button on the lone Dashboard tab. The program will ask us to specify a file which must be similar in structure to the attached Purchased report.xlsx data file. It will then read the contents of that file into the drop-downs of the user form that opens

2. Based on the specifying data column for filtering and extraction in step 1 :  I need further help in creating macro code to create a new sheet for every Unique value in selected filltered Column

The data file will be opened, read and closed - never modified in any way. the report is creating in a third workbook for the output.

The code will copy all rows with the same value in the selected filtered column of the range to a new worksheet in a third workbook for the output. It will do this for every unique value.The sheets will be named after the Unique value.

if the user selects PO Date = 2018-06-28 in the attached Purchase report.xlsx file, 
The Code will create third workbook for the output with 6 sheets below in

  • Office Supplies and Printing
  • Outsource dan Service
  • ATK
  • Machine Part
  • Maintenance
  • IT

Looking forward to having your further advice in this regards.

Many thanks and best regards

Arsil Hadjar

excel
MS Office Version: 2010
posted by: Arsil (rep: 32) on Sun Oct 28, 2018 at 8:31 pm
Answer
Discuss

Discussion

Hello Arsil,
This is a site for asking questions related to teaching (learning) Excel. It is not a site offering a free code writing service even though the difference is sometimes hard to make out. For best results, ask questions that can be answered in such a way as to help you keep control of your project. Also, limit yourself to one question per thread.
Variatus (rep: 4889) Oct 29, '18 at 3:14 pm
Dear Variatus,

Many thanks for the feedback and advice in asking the question in this site.
I have tried to edit and limit my the question to one question as posted above. Hopefully it will be OK.

Looking forward to having your further advice in this regards

Many thanks and best regards
Arsil Hadjar
Arsil (rep: 32) Oct 29, '18 at 8:33 pm
Add to Discussion

Answers

0
Selected Answer

Hello Arsil,

The attached workbook now has the code to execute what is specified in the user form. It's a fairly complicated program and might therefore contain some errors which don't become apparent until it is used in real life. Note that a feature I have added to the user form is that you can mit the "To" date. In such a case the program will extract data for one day only.

posted by: Variatus (rep: 4889) on Tue Oct 30, 2018 at 3:44 pm
Discuss

Discussion

Dear Variatus,
Many thanks for the help.
With the code provided , after adjusting the path and folder name
I can extract each report based on the data selector -
-Type of data to be extracted and select the filter criterium
-also for PO date 

From Data selector , In the second drop down list is it possible to add an Option to select All (=Select All Filter criterium)
So when I select Item category as the type of data to be extracted , and I select all (=Select All filter criterium) ,
The code will create a workbook report with separate sheet for different filter criterium  below
- Apparel
- ATK 
- Column HPLC
-Electricity
-House Hold
-Innovator
-IT
-Kendaraan ( Service&Parts)
-Laboratory Spare Part
-Labware
-Machine Part
-Maintenace-
-Microbiology
-Office Supplies and Printing
-Outsource and service 
-Packaging Items
-Production Supplies
-R&D
-Reagent Laboratorium
-Service
-Standard Laboratorium
-Testing Product
-Utility

Looking forward to having your further advice in this regard
Many thanks and best regard

Best regards

Arsil Hadjar
Arsil (rep: 32) Oct 30, '18 at 9:37 pm
Hello Hajar,

It's possible because everything is possible. However, I don't even want to think about it. Logically, your new idea is a part of the data selection which we placed in a separate thread and before the current one for a reason. The reason was that the second part of the project can only execute wha the first one has prepared. So, if we would go back to the first part I would say it's a new thread because the user form was designed and executed and now you want it modified and how to do that? But after the user input has been modified another modification has to be made to the code I now provided. To put this into a question and answer context the user form modification would have to be executed in such a way that the new form can still work with the current (this thread's) execution code, and that is the part I don't want to think about.  
Variatus (rep: 4889) Oct 31, '18 at 12:56 am
Of course, I am already thinking about it lol: I think you would need to develop the new option completely separate from the current project (step 1) and then integrate it with the current project (probably in step 2 but possibly steps 2 and 3). If you do it that way, however, you will run up against the same problem you face now which is that any errors from the previous step will complicate and even prevent modification. At this moment you have 2 steps, both untested, both unsatisfactory to you, and you want to wrap completion and modification into one cigar. That pig can't be made to fly. Follow the logic, Hajjar. You are managing this project. I am just writing a little code. The code is working. So, why don't we have a finished project?
Variatus (rep: 4889) Oct 31, '18 at 12:57 am
Dear Variatus ,

Many thanks for the response and great help .
The 2 steps have tested and the code is working perfectly.

With your help I can finish the initial project

Again many thanks for such great help.

Best regards
Arsil Hadjar
Arsil (rep: 32) Oct 31, '18 at 3:01 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login