Hi All,

I download a file frequently that has data for multiple accounts. I'm trying to write a proc that will separate the data by account number and put the data for each account in its own worksheet. My approach was to copy active sheet to a new sheet apply a filter excluding all but one account and selecting and deleting all visible cells and then going back to the main worksheet and repeating for each account. What I have so far is not working. I'm getting errors on the "Rows("1:1").Delete Shift:=xlUp" line. When I comment that line out then I get an error on the next line. any help would be appreciated. Also If someone can tell me how to save each worksheet into a separate workbook that would be cool too.

Sub SeperateAccounts()

    ActiveSheet.Copy After:=Worksheets(Worksheets.Count)
    
   'First row is deleted because the source data has an unwanted first row.
   Rows("1:1").Delete Shift:=xlUp
    
    ActiveSheet.Range("A:Q").AutoFilter Field:=9, Criteria1:=Array( _
        "111520", "111535", "111540", "111599", "112004", "112710", "113401"), Operator:= _
        xlFilterValues
    
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Delete Shift:=xlUp
    
    'This is done because the operation above deletes the headding
    Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
 [A1] = "JV Unit"
 [B1] = "Journal ID"
 [C1] = "Date"
 [D1] = "Year"
 [E1] = "Period"
 [F1] = "Source"
 [G1] = "Line Unit"
 [H1] = "Oper Unit"
 [I1] = "Account"
 [J1] = "AcctGrp"
 [K1] = "Amount"
 [L1] = "Line Descr"
 [M1] = "Voucher"
 [N1] = "Invoice"
 [O1] = "Vendor"
 [P1] = "Name"
 [Q1] = "AP Descr"
    
End Sub