Scenario:
Main Ledger sheet that has 4 columns and copies the rows to the respective "category" sheets. The 4 columns will appear the same on each category sheet and will be sorted by date. I have gotten the code working for only one category, but when I try to add in another category, the code breaks on ActiveCell.Offset(1, 0).Select in the Vehicle-Truck category. I'm a novice in Excel, but do have a programming background. It seems to me that the offset is trying to go down a row, but the ActiveCell is already at the bottom of the sheet. I appreciate any help!! Thanks.
Sub Runit()
Application.Run "CategorySheets"
'Back to Ledger Page
Sheets("Ledger").Activate
ActiveSheet.Range("A1").Select
End Sub
Public Sub CategorySheets()
'Clear Category Sheet
Sheets("Vehicle-Lexus").Cells.Clear
Sheets("Vehicle-Truck").Cells.Clear
'Copy header from Ledger sheet to category sheets
Sheets("Ledger").Range("A1").EntireRow.copy Sheets("Vehicle-Lexus").Range("A1")
Sheets("Ledger").Range("A1").EntireRow.copy Sheets("Vehicle-Truck").Range("A1")
Sheets("Ledger").Activate
Range("A2").CurrentRegion.Select 'select all data except header
Range("A2").Activate 'sort based on col A
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For Each cell In Range(("B2"), Range("B60000").End(xlUp))
Select Case cell.Value
Case "Vehicle-Lexus"
cell.EntireRow.copy Sheets("Vehicle-Lexus").Range("A60000").End(xlUp).Offset(1, 0)
Case "Vehicle-Truck"
cell.EntireRow.copy Sheets("Vehicle-Truck").Range("A60000").End(xlUp).Offset(1, 0)
End Select
Next
'important: col A need to have a proper series number for the macro to work
Range("A2").Activate 'sort based on col A
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'Vehicle-Lexus sheet Total on last row and in F1
Sheets("Vehicle-Lexus").Activate
Range("C2").End(xlDown).Select
MyRow_lex = ActiveCell.Row
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-" & MyRow_lex & "]C:R[-1]C)"
'Put Total
Range("E1") = "Total: "
Range("F1") = ActiveCell.Value
'Vehicle-Truck sheet Total on last row and in F1
Sheets("Vehicle-Truck").Activate
Range("C2").End(xlDown).Select
MyRow_tru = ActiveCell.Row
ActiveCell.Offset(1, 0).Select 'ERROR APPEARS HERE!!!!
ActiveCell.FormulaR1C1 = "=SUM(R[-" & MyRow_tru & "]C:R[-1]C)"
'Put Total
Range("E1") = "Total: "
Range("F1") = ActiveCell.Value
End Sub
Bookmarks