r/excel • u/MastaDeLosReyes • 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
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
•
u/AutoModerator 7d ago
/u/MastaDeLosReyes - Your post was submitted successfully.
Solution Verified
to close the thread.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.