Need data from one Excel Sheet separated by 'Dept' and saved as new files with 'Dept' name

Can you attach a small sample workbook, showing what you are trying to achieve mocked-up.

Go Advanced -> Manage Attachments -> Upload

.
This is one method :

Please Login or Register  to view this content.

I have uploaded an example

I uploaded an example of what I am needing to accomplish, but it did not show as a reply to your post. Thank you in advance for any help =)

Thank you for this information; however, I am trying to get the information separated into new workbook files instead of sheets within the same workbook. If that cannot be achieved, your code will help me to narrow it down even more!

Originally Posted by HospitalAccountant

Thank you for this information; however, I am trying to get the information separated into new workbook files instead of sheets within the same workbook. If that cannot be achieved, your code will help me to narrow it down even more!

Is it ok to assume that if column C is blank then that row is the last row of each department?
For example cell C5 is blank, does it mean we won't find NORTH 004 dept below it?

.
This will save each sheet as a separate .XLSX workbook in the same location as the main workbook :

Please Login or Register  to view this content.

Try

Please Login or Register  to view this content.

Originally Posted by Akuini

Is it ok to assume that if column C is blank then that row is the last row of each department?
For example cell C5 is blank, does it mean we won't find NORTH 004 dept below it?

Yes, that is correct

Originally Posted by Logit

.
This will save each sheet as a separate .XLSX workbook in the same location as the main workbook :

Please Login or Register  to view this content.

Thank you so much! I am going to try all of the suggestions as soon as I get to work and report back.

Originally Posted by daboho

Please Login or Register  to view this content.

Thank you for your help! I am going to try these when I get to work and will report back!

Originally Posted by jindon

Try

Please Login or Register  to view this content.

Everywhere you have a 2, say if on the file at work it was column D, I would just change the 2s to a 4? I will be trying this as soon as I get to work. Thank you so much for your help!

.
You are welcome. Let me know how it turns out.

Change all 2s to 4 except for loop counter
i.e

Please Login or Register  to view this content.

This 2 should stay as it is.

.

It appears from the new example workbook, we need to review both the NORTH and SOUTH sheets from which to copy the data to the individual new sheets ?

Also instead of the B Column for the Dept Code, you require the D Column ?

I'll work on those with the new workbook and if there is any other adjustments, please advise.

Originally Posted by Logit

.

It appears from the new example workbook, we need to review both the NORTH and SOUTH sheets from which to copy the data to the individual new sheets ?

Also instead of the B Column for the Dept Code, you require the D Column ?

I'll work on those with the new workbook and if there is any other adjustments, please advise.

Yes, we have a NORTH folder and a SOUTH folder...I do not mind at all copying the whole South tab tho and pasting to a new workbook in order to run the code separately, or I can send the files to new folders from the master folder once it's ran as I know which dept #s go where =) I have to go through and add NORTH/SOUTH to all the filenames anyhow.

Correct, the Dept Code is column D.

I'm sorry for all the confusion. I thought I had my sample workbook set up correctly last night at home. I can take the word TOTAL out of the last row of each Dept too if that makes it easier and I can just move it to column E so as to filter easier. I just know there is a way for this to be done each month besides copying and pasting 108 different departments data to email to each individual one. That is another duty of mine is creating an Access query to email these individual files automatically :/

.
I believe the edits will accomplish your goal. Let me know.

If you think it would be of value, we can add the ability to delete all the sheets within the workbook except NORTH and SOUTH, prior to creating/copying to the different sheets.

That way you won't need to manually delete those sheets before creating new.

Originally Posted by Logit

.
Here is the next version that deletes sheets previously created before creating new.

You are amazing!!!!! The only thing is the NORTH and SOUTH departments need to be separate, but I can try and work on this =) I have got to learn this code, it would help us SO much in my department. Thank you for replying and helping me so much!

Here is the final code to do what we were looking for. Thank everyone for all of their help! Logit, you are awesome!!

Sub parse_data()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 3
Set ws = Sheets("NORTH")
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = "A5:R5"
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
Sheets(myarr(i) & "").Columns.AutoFit
Next
ws.AutoFilterMode = False
ws.Activate

MyPath = "C:\Users\mh26702\My Documents\VBA Practice\NORTH"
For Each sht In ThisWorkbook.Sheets
sht.Copy
ActiveSheet.Cells.Copy
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats
ThisFile = sht.Name & " NORTH BUDGET " & "FY" & Format(Date, "yy")
ActiveWorkbook.SaveAs _
Filename:=MyPath & "\" & ThisFile & ".xlsx"
ActiveWorkbook.Close savechanges:=False
Next sht

Dim xWs As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In Application.ActiveWorkbook.Worksheets
If xWs.Name <> "NORTH" And xWs.Name <> "SOUTH" Then
xWs.Delete
End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub