SOLVED
Home

VBA - Splitting worksheet by Dept

%3CLINGO-SUB%20id%3D%22lingo-sub-227345%22%20slang%3D%22en-US%22%3EVBA%20-%20Splitting%20worksheet%20by%20Dept%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-227345%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22%22%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20complicated%20task%20that%20requires%20a%20VBA%20expert.%26nbsp%3B%20I%20have%20attached%20a%20file%20for%20you%20to%20review.%20What%20I%20would%20like%20is%20a%20VBA%20code%20to%20be%20able%20to%20take%20the%20data%20and%20split%20it%20into%20separate%20worksheet%20per%20dept.%26nbsp%3B%20So%20I%20want%20to%20filter%20out%20one%20dept%20at%20a%20time%20on%20one%20worksheet.%26nbsp%3B%20Importantly%20I%20want%20the%20subtotal%20at%20the%20bottom%20to%20remain.%26nbsp%3B%20So%20the%20subtotal%20would%20show%20the%20filtered%20total%20per%20dept.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAlso%20is%20it%20possible%20to%20rename%20the%20new%20worksheets%20with%20the%20Dept%20name%20as%20well%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20let%20me%20know%20if%20you%20can%20assist%20with%20this%20as%20it%20will%20save%20me%20so%20much%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EZia%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-227345%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Esplitting%20worksheet%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EVba%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-309235%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20-%20Splitting%20worksheet%20by%20Dept%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309235%22%20slang%3D%22en-US%22%3Eagrgar%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-234178%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20-%20Splitting%20worksheet%20by%20Dept%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-234178%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20remove%20it%20from%20this%20section%3CBR%20%2F%3EI'm%20sending%20a%20photo%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-234174%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20-%20Splitting%20worksheet%20by%20Dept%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-234174%22%20slang%3D%22en-US%22%3EThank%20you%20for%20helping%20me%20and%20for%20the%20time%20that%20you%20spent%20for%20me%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-234172%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20-%20Splitting%20worksheet%20by%20Dept%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-234172%22%20slang%3D%22en-US%22%3EI've%20created%20an%20Excel%20file%20and%20added%20two%20functions%20to%20it%3CBR%20%2F%3EFunctions%20other%20than%20the%20standard%20Excel%20functions%2C%20these%20functions%20are%20in%20the%20official%20language%20of%20my%20country.%3CBR%20%2F%3E%3CBR%20%2F%3EThese%20functions%20are%20visible%20in%20the%20Visual%20Basic%20section%20that%20is%20displayed%20with%20EFT%20and%20F11.%3CBR%20%2F%3EBut%20now%20I'm%20trying%20to%20remove%20these%20provided%20functions%3CBR%20%2F%3EWhich%20is%20registered%20in%20this%20section%20but%20the%20deletion%20option%20is%20inactive%20in%20this%20section%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-234169%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20-%20Splitting%20worksheet%20by%20Dept%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-234169%22%20slang%3D%22en-US%22%3EIn%20the%20Visual%20Basic%20section%20of%20the%20Excel%20file%3CBR%20%2F%3EI%20added%20a%20plugin%3CBR%20%2F%3EAnd%20now%20I%20need%20to%20clean%20it%20up%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-227457%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20-%20Splitting%20worksheet%20by%20Dept%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-227457%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20some%20basic%20knowledge%20in%20computer%20programming%20in%20learning%20Pascal%20when%20I%20were%20a%20student.%20So%20that%20I%20have%20concepts%20on%20for-loop%20%2F%20while-loop.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20the%20VBA%20syntax%2C%20I%20just%20search%20the%20internet%20for%20information%20and%20then%20learn%20it%20little%20by%20little.%20Another%20way%20is%20to%20record%20the%20macro%20so%20that%20I%20know%20some%20keyword%20in%20some%20operation%20and%20then%2C%20again%2C%20search%20the%20internet.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EActually%2C%20there%20is%20a%20lot%20of%20thing%20in%20VBA%20that%20I%20did%20not%20know.%26nbsp%3B%3C%2FP%3E%3CHR%20%2F%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CHR%20%2F%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-227441%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20-%20Splitting%20worksheet%20by%20Dept%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-227441%22%20slang%3D%22en-US%22%3E%3CP%3EMan%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20assistance%20today.%26nbsp%3B%20I%20appreciate%20it.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20did%20you%20learn%20so%20much%20about%20VBA%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EZia%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-227397%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20-%20Splitting%20worksheet%20by%20Dept%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-227397%22%20slang%3D%22en-US%22%3E%3CP%3EI%20do%20not%20modified%20the%20program%20but%20just%20insert%20more%20comment%20for%20the%20changes.%20Hope%20you%20can%20learn%20it.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-227365%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20-%20Splitting%20worksheet%20by%20Dept%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-227365%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Man%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20reply.%26nbsp%3B%20I%20have%20tried%20to%20follow%20your%20instructions%20but%20I%20can't%20get%20it%20to%20work.%26nbsp%3B%20I%20have%20changed%20the%20location%20of%20the%20data.%26nbsp%3B%20Can%20you%20have%20a%20look%20and%20tell%20me%20what%20need%20to%20change%20in%20order%20to%20get%20this%20to%20work%20again.%26nbsp%3B%20%26nbsp%3BPlease%20could%20you%20add%20additional%20comments%20to%20the%20VBA%20code%20so%20I%20can%20see%20what%20you%20are%20changing%20in%20order%20to%20make%20the%20VBA%20code%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EZia%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-227363%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20-%20Splitting%20worksheet%20by%20Dept%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-227363%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20have%20to%20understand%20the%20range%20and%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERange(%22J4%22)%20means%20the%20cell%20J4%3C%2FP%3E%3CP%3ERange(%22E%22%20%26amp%3B%20r_CS%20%26amp%3B%22%3AH%22%20%26amp%3B%20r_CS)%20represents%20the%20cells%20from%20column%20E%20to%20column%20H%20in%20the%20r_CS-th%20row.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20other%20hand%2C%20we%20may%20consider%20cells%20as%20well.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECells(1%2C2)%20means%20the%20cell%20in%201st%20row%20and%202nd%20column%2C%20i.e.%20the%20cell%20B1%3C%2FP%3E%3CP%3EYou%20may%20also%20represent%20it%20as%20Cells(1%2C%22B%22)%20in%20case%20you%20don't%20want%20to%20count%20the%20order%20of%20letters.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-227356%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20-%20Splitting%20worksheet%20by%20Dept%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-227356%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Man%3C%2FP%3E%3CP%3EThank%20you%20for%20this%20I%20am%20very%20grateful.%26nbsp%3B%20It%20works%20brilliantly.%26nbsp%3B%20I%20have%20a%20few%20questions%20regarding%20this%20code.%26nbsp%3B%20The%20data%20file%20was%20a%20test%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20I%20ask%20when%20I%20am%20working%20with%20these%20sort%20of%20files%20the%20range%20of%20the%20data%20will%20be%20different%20ie%20the%20data%20might%20start%20in%20cell%20A1%20or%20P1.%26nbsp%3B%20%26nbsp%3BThe%20number%20of%20columns%20used%20would%20be%20different%20ie%20the%20data%20may%20go%20from%20column%20K%20to%20column%20U.%26nbsp%3B%20The%20number%20of%20rows%20will%20also%20vary%20depending%20on%20the%20data.%26nbsp%3B%20The%20subtotal%20as%20a%20result%20would%20be%20in%20different%20cells%20each%20time%20I%20run%20the%20report.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20part%20of%20this%20code%20needs%20to%20change%20in%20order%20to%20reflect%20the%20above%20variations.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELook%20forward%20to%20your%20reply.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EZia%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-227347%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20-%20Splitting%20worksheet%20by%20Dept%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-227347%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20may%20refer%20to%20the%20attached%20file.%20(Just%20wonder%20that%20xlsm-file%20are%20allowed%20to%20upload)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20program%20will%20determine%20whether%20a%20department%20is%20%22new%22%20and%20then%20create%20the%20corresponding%20worksheets.%20I%20myself%20prefer%20to%20create%20a%20blank%20template%20and%20then%20copy%20the%20template%20in%20case%20it%20is%20a%20%22new%20department%22%20so%20that%20you%20need%20not%20to%20concern%20the%20formatting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20each%20output%2C%20the%20cell%20J4%20is%20counting%20the%20row%20number%20in%20the%20sheet.%20Since%20I%20am%20not%20sure%20that%20your%20real%20file%20consists%20of%20other%20worksheets%20or%20not%2C%20I%20cannot%20clear%20that%20contents.%20You%20may%20delete%20it%20manually.%20This%20procedure%20can%20be%20solved%20if%20you%20already%20have%20the%20list%20of%20departments.%20In%20this%20case%2C%20you%20can%20create%20worksheets%20(or%20copy%20from%20template)%20according%20to%20the%20list%20and%20then%20at%20end%20delete%20J4%20for%20each%20worksheet%20corresponds%20to%20the%20department.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20it%20is%20helpful%20to%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Zia Siddique
New Contributor

Hi

 

This is a complicated task that requires a VBA expert.  I have attached a file for you to review. What I would like is a VBA code to be able to take the data and split it into separate worksheet per dept.  So I want to filter out one dept at a time on one worksheet.  Importantly I want the subtotal at the bottom to remain.  So the subtotal would show the filtered total per dept.

 

 

Also is it possible to rename the new worksheets with the Dept name as well?

 

Please let me know if you can assist with this as it will save me so much time.

 

Thank you in advance.

 

Zia

12 Replies

You may refer to the attached file. (Just wonder that xlsm-file are allowed to upload)

 

The program will determine whether a department is "new" and then create the corresponding worksheets. I myself prefer to create a blank template and then copy the template in case it is a "new department" so that you need not to concern the formatting.

 

For each output, the cell J4 is counting the row number in the sheet. Since I am not sure that your real file consists of other worksheets or not, I cannot clear that contents. You may delete it manually. This procedure can be solved if you already have the list of departments. In this case, you can create worksheets (or copy from template) according to the list and then at end delete J4 for each worksheet corresponds to the department. 

 

Hope that it is helpful to you. 

Hi Man

Thank you for this I am very grateful.  It works brilliantly.  I have a few questions regarding this code.  The data file was a test file.

 

Can I ask when I am working with these sort of files the range of the data will be different ie the data might start in cell A1 or P1.   The number of columns used would be different ie the data may go from column K to column U.  The number of rows will also vary depending on the data.  The subtotal as a result would be in different cells each time I run the report.

 

What part of this code needs to change in order to reflect the above variations.

 

Look forward to your reply.

 

Zia

You have to understand the range and cells.

 

Range("J4") means the cell J4

Range("E" & r_CS &":H" & r_CS) represents the cells from column E to column H in the r_CS-th row. 

 

On the other hand, we may consider cells as well. 

 

Cells(1,2) means the cell in 1st row and 2nd column, i.e. the cell B1

You may also represent it as Cells(1,"B") in case you don't want to count the order of letters. 

 

 

Hi Man

Thank you for the reply.  I have tried to follow your instructions but I can't get it to work.  I have changed the location of the data.  Can you have a look and tell me what need to change in order to get this to work again.   Please could you add additional comments to the VBA code so I can see what you are changing in order to make the VBA code work.

 

Thank you in advance.

 

Zia

Solution

I do not modified the program but just insert more comment for the changes. Hope you can learn it. 

Man 

Thank you for your assistance today.  I appreciate it.  

 

How did you learn so much about VBA?

 

Zia

I have some basic knowledge in computer programming in learning Pascal when I were a student. So that I have concepts on for-loop / while-loop. 

 

For the VBA syntax, I just search the internet for information and then learn it little by little. Another way is to record the macro so that I know some keyword in some operation and then, again, search the internet. 

 

Actually, there is a lot of thing in VBA that I did not know. 


 


 

In the Visual Basic section of the Excel file
I added a plugin
And now I need to clean it up
I've created an Excel file and added two functions to it
Functions other than the standard Excel functions, these functions are in the official language of my country.

These functions are visible in the Visual Basic section that is displayed with EFT and F11.
But now I'm trying to remove these provided functions
Which is registered in this section but the deletion option is inactive in this section
Thank you for helping me and for the time that you spent for me

I'm trying to remove it from this section
I'm sending a photo

agrgar
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies