Forum Discussion
VBA - Splitting worksheet by Dept
I do not modified the program but just insert more comment for the changes. Hope you can learn it.
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
I'm trying to remove it from this section
I'm sending a photo- In the Visual Basic section of the Excel file
I added a plugin
And now I need to clean it up 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.
- agrgar
