I have a single worksheet containing information related to cellular charges for our company. One of the columns is titled GL Dept, and I need to separate the spreadsheet out into multiple sheets. Each sheet needs to have only the rows for each unique department so they can be distributed to each department head.
Can anyone help with this scenario?
Re: Generate multiple worksheets from one worksheet
this sounds similar to one of the worksheets that I have done. Can you provide a small sample workbook with what you want to happen in detail?
Re: Generate multiple worksheets from one worksheet
Here you go....
Requirements in detail:
I need something (a macro, etc.) that will automatically generate a new worksheet for each department. Each department's worksheet should have all the data for each person belonging to that department. It would be nice for the worksheet name to also be the department name. In the end, a workbook containing multiple worksheets, each containing a separate department, would be nice to have.
Re: Generate multiple worksheets from one worksheet
I will take a look this evening and then I will post back unless somebody else chimes in.
JMAN
Re: Generate multiple worksheets from one worksheet
Thanks! It would be greatly appreciated!
Re: Generate multiple worksheets from one worksheet
Try this code.
Sub DistributeRows()
Dim wsAll As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim LastRow As Long
Dim LastRowCrit As Long
Dim I As Long
Set wsAll = Worksheets("Sheet1")
LastRow = wsAll.Range("A" & Rows.Count).End(xlUp).Row
Set wsCrit = Worksheets.Add
wsAll.Range("X1:X" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True
LastRowCrit = wsCrit.Range("A" & Rows.Count).End(xlUp).Row
For I = 2 To LastRowCrit - 1
Set wsNew = Worksheets.Add
wsNew.Name = wsCrit.Range("A2")
wsAll.Rows("1:" & LastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsCrit.Range("A1:A2"), _
CopyToRange:=wsNew.Range("A1"), Unique:=False
wsCrit.Rows(2).Delete
Next I
Application.DisplayAlerts = False
wsCrit.Delete
Application.DisplayAlerts = True
End SubRe: Generate multiple worksheets from one worksheet
Amazing!!! I created a macro that resizes a few of the columns, but other than that it works perfectly!
Re: Generate multiple worksheets from one worksheet
Hi,
I'm still beginner for VBA and I was trying the code provided above. It's amazing because now I can apply it to my own database.
Here4Help, can you also provide me with the code on the resizes of the columns. Thanks
Re: Generate multiple worksheets from one worksheet
nea
Try turning on the macro recorder and resizing the columns manually.
That should generate code which you can then adapt to do exactly what you want, ie resize columns.
Re: Generate multiple worksheets from one worksheet
nea,
Welcome to Ozgrid, and while we are pleased you have been provided with a solution to your problem, please do not pose your questions in threads started by others. Ozgrid forum rules are one question (or close follow-ups) by the Original Poster only. In future please start your own thread and if it helps explain your problem then post a link back to a previous thread.
Thank you.
Don’t have an account yet? Register yourself now and be a part of our community!