VBA Help need for pullout data to new sheet from Master Sheet & save as new file (UserForm

Folks,

I have a workbook with a sheet called as “Master List” and one UserForm. I need a Macro / VBA code for pull out the “Justified” jobs from the “Master _List” based on cell value of Column H (“Status”) and create a new tab for the same (Tab name as “Justified Jobs”). And, I want to do the same for “Deleted” and “Deferred” jobs based on cell value of Column I “Reason for Not Justified”. Once the three sheets created in Master_List, I want to save as these sheets as a new work book in a specific folder as per user input through the UserForm1. I have attached the excel file of what I'm trying to accomplish.

Note:

1) The criteria for Deleted job’s tab is excluding “Deferred”, “N/A” and blank cell from Column I “Reason for Not Justified”.
2) There is a multi select ListBox on the UserForm to select the required columns for the new tabs ("Justified", "Deleted" and "Deferred")


Thanks in advance for your expertise!

Joshi

Hi Joshi

Please create these three worksheets based on the Data in your Sample File. “Justified Jobs” and “Deferred” seem to be rather straight forward. Regarding "Deleted", have no clue what this means

The criteria for Deleted job’s tab is excluding “Deferred”, “N/A” and blank cell from Column I “Reason for Not Justified”.

Just thinking out loud I'm not certain why we need this

There is a multi select ListBox on the UserForm to select the required columns for the new tabs ("Justified", "Deleted" and "Deferred")

Hi John,

Thanks for your reply.

Please find attached the modified workbook with example as advised.

Answer for the second query, I want a multi select list box to select the columns for new sheets which need to be created from "Master List" because other stake holders wish to see the job list with some particular columns only (may be less than 20 Columns). Please note that the Master List for my reference only.

Thanks in advance,

Hi Joshi

We're going to take this in steps. The first step only deals with the Left Side of your UserForm. The code does the following:

When the User clicks on Create Job List Button the Code checks to see if any Columns are selected in ListBox1

Please Login or Register  to view this content.

If none are selected it creates Worksheets as defined by whichever Option Button has been selected:

• Justified Job List
• Not Justified Job List (Except Deferred Jobs)
• Deferred Job List
• All (Justified, Not Justified & Deferred - 3 Sheets)

If Columns ARE selected in ListBox1 these same Option Button Reports (depending on which Option Button is selected) are created with only those Columns that are selected in ListBox1.

Play with these features...what's working...what's not. Get back to me.

Hi John,

Thanks for the codes, its working perfectly. I faced one small issue, when I was updating the Master List and run code again the Formats (Borders and filled color) were not clearing, I have changed the code as below.
Code From

Please Login or Register  to view this content.

Code to

Please Login or Register  to view this content.

.

Is it correct? Please advise for next step.

Hi Joshi

This would be correct

Sheets("Justified").Cells.Clear

The next step is to work on the Right Side of your UserForm.

Your "Design" seems to indicate the User will Cut/Paste a Folder Path in TextBox1 and/or TextBox2. This can be left as is if you so desire. However, I'll ask if there are a limited number of paths that might be used. If so these could be populated in a ListBox/ComboBox such that the User could simply select the Path they wish to use.

I also have a question regarding the Command Buttons Create New Job List and Update Existing Job List. What do you envision here...Create vs UpDate? Why not just Create? If it exists, delete it and create a new one.

Hi John,

Thanks for your reply and advise. Please see the below answer for your two query.

1. I would like to keep the text boxes for the Folder Path since they may vary most of the time.
2. Regarding the Command Buttons "Create New Job List" for creating new job list. "Update Existing Job List" for updating the existing list, please note that the saved list may access many users at a time with read only access (I will set a password as " SDG 123 " for modify access in the saved workbooks), if the file kept open by any of these users then we cannot delete the Existing File, that's why I placed a button for updating the existing workbooks . When I hit the "Update Existing Job List" button the existing workbook to be opened with modified access password "SDG123" then get updated.

I have placed two more buttons for the path selection, is this can be fixed? (if this very difficult please leave it). Please find attached the modified file for your reference.

Thank you so much for your time and help.

Hi John,

I have attached the modified workbook again since I am unable edit any of my post once I sent.


Thanks,
Joshi

Hi Joshi

I ran into an issue of MASSIVE file size when Columns were hidden (ListBox1.ListIndex was not -1). The Code in the attached addresses that issue.
The attached also has Code for TextBox3 (File Name), TextBox1 (Drive M), TextBox2 (Drive G) and CommandButton2 (Create New Job List). It DOES NOT have Code for CommandButton4 (Update Existing Job List).

You'll need to change the Code in Module "PublicVariables"

Please Login or Register  to view this content.

I'm not certain what you're looking for here...

2.Regarding the Command Buttons "Create New Job List" for creating new job list. "Update Existing Job List" for updating the existing list, please note that the saved list may access many users at a time with read only access (I will set a password as " SDG 123 " for modify access in the saved workbooks), if the file kept open by any of these users then we cannot delete the Existing File, that's why I placed a button for updating the existing workbooks . When I hit the "Update Existing Job List" button the existing workbook to be opened with modified access password "SDG123" then get updated.

I'm pretty certain I don't know how to accomplish what you're looking for.

Let me know of issues.

Hi John,
Excellent codes, thank you so much.
Few issues:-
1) If I Select the OptionButton5 and folder path is blank in TextBox1 then should show a Msgbox "Please enter or select folder path". And it should be applied for the OptionButton6 (linked with TextBox2) and OptionButton8 (if both TextBox1&TextBox2 are blank).
2) I want to delete the "Sheet1" from the newly created job list (Sheet1 is appearing in the newly created workbook along with "Justified", "Deleted" & "Deferred").

Optional : Can we protect the new workbook with read only access (Pass: "SDG123") by modifying your below code.

Please Login or Register  to view this content.

Thanks in advance.
Joshi

Hi Joshi

See what the Code in the attached does for you...still don't know about this...not sure how to handle it

Can we protect the new workbook with read only access

Can't do this due to Code Issues

I want to delete the "Sheet1" from the newly created job list

So I've hidden "Sheet1" here

Please Login or Register  to view this content.

The User can't unhide it...can only be done via Code here

Please Login or Register  to view this content.

Let me know of issues.

Hi John,

Excellent job, thanks a lot. A minor error, I am not able to enter a file name with space, showing MsgBox " Your Filename Contains Invalid Character(s), could you please fix this issue.

Thank you so much once again for your time and help.

Hi Joshi

A minor error, I am not able to enter a file name with space

That's by design. If you wish to enter space(s) in the File Name change the Code to this

Please Login or Register  to view this content.

Hi John,

Great work. Thank you so much.

One more small issue, I believe this will be the last and final. I am getting a "Debug" message if I hit the right side create job list button without creating any job list by using left side button. In this case I want to show a message "Please create the job list first by using left side options".

Thanks in advance.

Joshi

Hi Joshi

How many worksheets exist in the Master Job List Workbook BEFORE CommandButton1 "Create Job List" is clicked...just 1 (namely Master_List)?

Hi John
Only one "Master_List".

Thanks in advance,
Joshi

Hi Joshi

Add the lines of code as indicated

Please Login or Register  to view this content.

Hi John,

Now its working perfectly! Thank you very much.

Only having some delay, its taking too much time to create the job list when I select some columns from the ListBox1, but if I select all columns the code will run very fast. Any idea?

Thank you so much once again for your time and help.

Joshi

Hi Joshi

I had this same issue

its taking too much time to create the job list when I select some columns from the ListBox1

There may be a slightly different approach...I'll look at it.

Hi Joshi

I like this much better...see what you think.

Hi John,

The last one is running very fast. Thank you so much.


- I have slightly modified the userform by adding textbox4 for the password input (Please see the attached workbook), I don't want clear the textbox4 & textbox3 values (Password & Filename) when I close and reopen the userform unless I click on the "Reset" button.
- When I open the copied workbook (Which we created thru the userform right side controls) should be activate the "Justified" sheet always. Can we fix these two issues?

Hi Joshi

Are you saying you ALWAYS want the "Justified" Sheet to be created even if it's not selected?

When I open the copied workbook (Which we created thru the userform right side controls) should be activate the "Justified" sheet always

Hi John,
No, when i open the copied workbook (three sheets "justified", "deleted" & "deferred" which we copied to two folders) I want activate the "Justified".

Hi Joshi

See if the attached works for you.

Hi John,

Thanks for your time and help. Regarding the userform, when I close and reopen the workbook and all of the text boxes values got clear. I don't want clear the values from any text boxes unless I hit the related command buttons. Also, I want to overwrite the existing values and starts with 1, 2, 3 as serial in the copied sheet's "Sd Sr No" ("Justified", "Deleted", "Deferred" - Column "A") . Thanks

Hi Joshi

I'm not clear on this request...post examples

I want to overwrite the existing values and starts with 1, 2, 3 as serial in the copied sheet's "Sd Sr No" ("Justified", "Deleted", "Deferred" - Column "A")

Regarding this

when I close and reopen the workbook and all of the text boxes values got clear. I don't want clear the values from any text boxes unless I hit the related command buttons

That's the way TextBoxes in a UserForm work...they return to their native state when the workbook is closed then re-opened. The only way this can be handled is to save the TextBox values to a worksheet and then, when the workbook is reopened, the UserForm TextBoxes are initialized from the saved data.

If this is the approach you wish to take let me know...it'll involve some not insignificant rewrite.

Hi John,

Yes please, I want fix the textboxes based on your below advice (One new worksheet can be created for this).

The only way this can be handled is to save the TextBox values to a worksheet and then, when the workbook is reopened, the UserForm TextBoxes are initialized from the saved data.

Regarding this (please see the attached workbook)

I want to overwrite the existing values and starts with 1, 2, 3 as serial in the copied sheet's "Sd Sr No" ("Justified", "Deleted", "Deferred" - Column "A")

.

Thanks in advance,

Hi Joshi

Why is Sd Sr No Column formatted as Date? Am I missing something? Should it be formatted perhaps as Text?

Dear John,

I don't know how it happened, it should be number or text format.

Thanks,
Joshi

Hi Joshi

Try the attached...I believe these issues are addressed

I want fix the textboxes based on your below advice (One new worksheet can be created for this).

A hidden worksheet called TextBoxValues has been placed in the Workbook. They stay there until cleared by the User. These values populate the TextBoxes in the UserForm Initialization.

want to overwrite the existing values and starts with 1, 2, 3 as serial in the copied sheet's "Sd Sr No" ("Justified", "Deleted", "Deferred" - Column "A")

The Sheets are updated with new Serial Numbers from 1 to the last row.

Let me know of issues.

Hi John,
I am unable open the userform1, getting debug message "Run-time error '9': Subscript out or range. (UserForm1.Show vbModeless).

Please help me to fix this.

Hi Joshi

Sorry about that...uploaded wrong file.

Hi John,

Excellent job. Thank you so much.

Really I appreciate your great work.

Thank you very much once again.

Joshi

You're welcome...glad I could help.