Split Excel file into several files

[joslaz]

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

 

[oldbrewer]

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....

 

[joslaz]

I am sorry, this approach is not possible.

Does anyone else have any suggestions?

 

[Hjemmet]

Get HJSplit Free File Spiller on Sender and Reciever

Spit File on sender and Merge on reciever

It will do what you need

 

[welshgasman]

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

 

[SpillerBD]

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.

 

[SpillerBD]

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

 

[SpillerBD]

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.

  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

 

[welshgasman]

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

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

HTH

 

[SpillerBD]

I *always* calculate the last row of a range, never hard 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.
While there is a lot of good code in that book, most of the code doesn't always follow such simple better/best practices.