Filter Table save as new workbook in same folder

Alpacino

Well-known Member
Joined
Mar 16, 2011
Messages
511
Hi all,

I wonder if someone could help.

I have a table in Sheet “Final Filtered” range A1:S510. I like to create a new workbook and filter by manager which are in column G. Sheet “Names” rows 2 to 21 has all the unique managers names I need.

i also like to save the new workbook in the same folder as the current workbook.

Many thanks everyone

Al
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
The result is a single book?
Or do you want a book for each manager?
It is not clear to me. You could give an example of what you have on each sheet and what you expect from the result in the new book.
 
Upvote 0
Hi

Sorry for not being clear. I have one sheet with all the data. And one sheet(“Names”) in range A2:A21 with all the managers name I need.

I like to create a workbook for each manager with their own data, no one else from the table that is in Sheet(Final Filtered) range (A1:S510).

The column in the table with the managers name is column G.

Hope this makes sense.

Thank you
 
Upvote 0
Try this

Code:
Sub Test()
  Dim sh As Worksheet, c As Range, ky As Variant, wb As Workbook, wPath As String, lr As Long
  
  Application.SheetsInNewWorkbook = 1
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Set sh = Sheets("Final Filtered")
  wPath = ThisWorkbook.Path & "\"
  If sh.AutoFilterMode Then sh.AutoFilterMode = False
  lr = sh.Range("G" & Rows.Count).End(xlUp).Row
  With CreateObject("scripting.dictionary")
    For Each c In sh.Range("G2:G" & lr)
      .Item(c.Value) = Empty
    Next
    For Each ky In .Keys
      sh.Range("A1").AutoFilter 7, ky
      Set wb = Workbooks.Add
      sh.AutoFilter.Range.Range("A1:S" & lr).Copy Range("A1")
      wb.SaveAs wPath & ky
      wb.Close False
    Next
  End With
  sh.ShowAllData
End Sub
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top