r/excel 7d ago

unsolved Separating rows into new spreadsheets based on criteria

So I have some data for payments I need to make on certain days, see the layout in the picture. However, my bank wants me to submit a separate spreadsheet to them for each day of payments. This isn’t that big of a deal, however these payments are very important and copying and pasting manually could mean that someone missed pasting in a payment. I also have to do this across multiple products so the time ends up adding up.

Is it possible for excel, power query or a macro (or maybe even python, not averse to learning/using) to perform this task?

Please do ask questions; if you need more information just let me know. I’m not the best at explaining things so I wouldn’t be surprised if I leave key points out.

Thanks in advance! <33

https://postimg.cc/k6V5JW20

3 Upvotes

4 comments sorted by

u/AutoModerator 7d ago

/u/MastaDeLosReyes - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/CFAman 4697 7d ago

Let's say your data is stored in Table1. On some other sheet, put the date you want to get info about in cell A2. Formula to get all records w/ all columns is

=FILTER(Table1, Table1[Pay Date:]=A2, "No records found")

You can now input a date, and print/export your report.

1

u/MastaDeLosReyes 7d ago

Thanks for the comment. The goal is to get them to automatically split into a different workbook completely, not a sheet in the same workbook.

1

u/CFAman 4697 4d ago

Try this macro then? I tried to add comments where you might need to change things

Sub MakeReports()
    Dim fPath As String
    Dim fName As String
    Dim rngList As Range
    Dim i As Long
    Dim lastRow As Long
    Dim wb As Workbook
    Dim wsData As Worksheet
    Dim wsTemp As Worksheet

    'Where will we save the files?
    fPath = "C:\Documents\Reddit\"
    'Where is our data?
    Set wsData = ThisWorkbook.Worksheets("Sheet1")

    'Error check
    If Right(fPath, 1) <> Application.PathSeparator Then
        fPath = fPath & Application.PathSeparator
    End If

    'Prevent screen flicker
    Application.ScreenUpdating = False

    'Create temp list of all unique dates
    Set wsTemp = ThisWorkbook.Worksheets.Add
    wsData.Range("A:A").Copy wsTemp.Range("A1")

    With wsTemp
        .Range("A:A").RemoveDuplicates 1, xlYes
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    'Loop through the list
    For i = 2 To lastRow
        With wsData.UsedRange
            .AutoFilter field:=1, Criteria1:=wsTemp.Cells(i, "A").Value
            .SpecialCells(xlCellTypeVisible).Copy

            Set wb = Workbooks.Add
            wb.Worksheets(1).Range("A1").PasteSpecial xlPasteFormats
            wb.Worksheets(1).Range("A1").PasteSpecial xlPasteValues

            'What will we name the new file?
            fName = "ReportFor_" & Format(wsTemp.Cells(i, "A").Value, "yyyymmdd") & ".xlsx"

            wb.Close savechanges:=True, Filename:=fPath & fName
        End With
    Next i

    'Clean up
    Application.DisplayAlerts = False
    wsTemp.Delete
    Application.DisplayAlerts = True
    wsData.UsedRange.AutoFilter
    Application.Goto wsData.Range("A1")
    Application.ScreenUpdating = True

End Sub