Hi, I have code that filters my data and then copies the visible cells and pastes it into a new workbook and saves it. However, I need to generate multiple workbooks with different criteria (from the filter), so I just reused the same code. Issue is my code works once and generates a report (let's say I filtered for A), but then the second report (supposed to filter for B) will give me the "excel has run out of memory" error. Not sure why that's happening or if my code is bad. Many thanks in advance for any advice you guys may have!
Code:
Sub AllReports()
Call First
Call Second
End Sub
Sub First()
Dim LastRow As Integer
With Sheets("Sheet1")
LastRow = .Range("G" & .Rows.Count).End(xlUp).Row
Sheets("Sheet1").Select
ActiveSheet.AutoFilterMode = False
Range("A1:EK1").AutoFilter
'FILTER HERE
ActiveSheet.Range("$A$1:$EK$" & LastRow).AutoFilter field:=7, Criteria1:="FIRST"
Dim newBook As Excel.Workbook
Dim rng As Excel.Range
Set newBook = Workbooks.Add
Set rng = ThisWorkbook.Worksheets("Sheet1").Cells.SpecialCells(xlCellTypeVisible)
rng.Copy newBook.Worksheets("Sheet1").Range("A1")
'FILENAME HERE
ActiveWorkbook.SaveAs Filename:="C:\Users\Documents\First.xls"
End With
End Sub
Sub Second()
Dim LastRow As Integer
With Sheets("Sheet1")
LastRow = .Range("G" & .Rows.Count).End(xlUp).Row
Sheets("Sheet1").Select
ActiveSheet.AutoFilterMode = False
Range("A1:EK1").AutoFilter
'FILTER HERE
ActiveSheet.Range("$A$1:$EK$" & LastRow).AutoFilter field:=6, Criteria1:="SECOND"
Dim newBook As Excel.Workbook
Dim rng As Excel.Range
Set newBook = Workbooks.Add
Set rng = ThisWorkbook.Worksheets("Sheet1").Cells.SpecialCells(xlCellTypeVisible)
rng.Copy newBook.Worksheets("Sheet1").Range("A1")
'FILENAME HERE
ActiveWorkbook.SaveAs Filename:="C:\Users\Documents\Second.xls"
End With
End Sub