Split Excel file into several files

joslaz

Board Regular
Joined
May 24, 2018
Messages
76
Hello!


I send a large data set to a colleague every week. This must filter this data and distribute it into the corresponding departments / sharepoint folders.


How would you best automate this?


I have already tried the first approaches with the macro recorder, but without much success.


Of course, one possibility would be to use PowerQuery to filter the stanza per department and load it into the table.
How would the export run in the SharePoint folder?




I am grateful for every idea!




greeting
 
Last edited:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
filter it yourself first and Email to the relevant departments - or - use an access database to hold the data then multi users can simultaneously use queries on it....
 
Upvote 0
Get HJSplit Free File Spiller on Sender and Reciever

Spit File on sender and Merge on reciever

It will do what you need
 
Upvote 0
I would approach it by doing something along the lines of

Copy filter data to another sheet. Remove duplicates on that data.
Then for that range of data, filter the main sheet, copy to new workbook, save and send to relevant department by email.
Repeat for all rows in the de-duplicated data range.
If you are able to save to the sharepoint folders than save there and just send an update email that new file is available.?

I would record macros to see what Excel generates for the steps, then tweak as needed.

HTH
 
Upvote 0
I saw a macro by MickG, that has inspiration, but wasn't exactly what is needed here.
I think a macro that loops through each distinct value of an appropriate column using the Advanced filter to copy results to a new Sheet or Workbook.
I already have a macro that sends sheets to Workbooks individually, so personally, I would prefer a new sheet for each.

There is a method of utilizing a Pivot table's drill down feature that would facilitate a manual method, but could still get tedious depending on the number of distinct values to go through. Wouldn't be good for my use as that would be 300-500 drill downs to perform.
 
Upvote 0
Hello!
...Of course, one possibility would be to use PowerQuery to filter the stanza per department and load it into the table.
How would the export run in the SharePoint folder?

Each department could have their own file with distinct Query and Pivot, but the security of the source file and Query may be a concern. I haven't gotten to that need but have seen some of the security concerns addressed on P3, PowerPivotPro.com

Other authors: Gil Raviv, Mike Girvin, Avi Singh, Rob Collie, Matt Allington
 
Upvote 0
This is modification for my testing of Dennis Wallentin's macro as found in Bill Jelen's 2013 VBA book.
You have to copy your criteria list, in your case the Departments, to column AA. My loop begins at 2 since I retain the header. I threw in variable Check1 for debugging at first but then used it to define the Sheet Names.
My source data had 16 Columns
My list of Unique values went from row 2 to row 416
I prefixed the new sheets with "CC" for Cost Center.

Code:
  Sub Filter_NewSheet()
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim rnStart As Range, rnData As Range
    Dim i As Long
 
  Set wbBook = ActiveWorkbook
  Set wsSheet = wbBook.ActiveSheet
 
  With wsSheet
      'Make sure that the first row contains headings.
      Set rnStart = .Range("A2")
      Set rnData = .Range(.Range("A2"), .Cells(.Rows.Count, [COLOR=#00ff00]16[/COLOR]).End(xlUp))
  End With
  
   
  Application.ScreenUpdating = True
  For i = 2 To [COLOR=#ffa500]416 [/COLOR]'i range must corrspond to the rows of values in the list
      'Here we filter the data with the first criterion.
      'The Values for the filter must be created in the corresponding Range
      Check1 = wsSheet.Range("AA" & i).Value
      rnStart.AutoFilter Field:=3, Criteria1:=wsSheet.Range("AA" & i).Value
      'Copy the filtered list
      'rnData.SpecialCells(xlCellTypeVisible).Copy
      wsSheet.Range("A1").CurrentRegion.Copy
      'Add a new worksheet to the active workbook.
      Worksheets.Add Before:=wsSheet
      'Name the added new worksheets.
      ActiveSheet.Name = "[COLOR=#0000ff]CC[/COLOR]" & Check1
      'Paste the filtered list.
      Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
  Next i
 
  'Reset the list to its  original status.
  rnStart.AutoFilter Field:=3
 
  With Application
      'Reset the clipboard.
      .CutCopyMode = False
      .ScreenUpdating = False
  End With
    Application.ScreenUpdating = True
  End Sub
 
Upvote 0
I *always* calculate the last row of a range, never hard code?

Code:
' Range has to be a column that will *** always *** have data
    lLastRow = Range("B" & Rows.Count).End(xlUp).Row

HTH
 
Upvote 0
I *always* calculate the last row of a range, never hard code?

Code:
' Range has to be a column that will *** always *** have data
    lLastRow = Range("B" & Rows.Count).End(xlUp).Row

HTH

I agree, but I was lazy at the time and didn't change that piece.:p
While there is a lot of good code in that book, most of the code doesn't always follow such simple better/best practices.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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