The macro below works when it is in the file I'm running it on. When I put the macro in my personal.xls file it gives me error 400. When do I need to do to be able to include it in personal.xls so that I don't have to copy it over to my input file every time I run it?
Sub Reformat() 'Eliminate multiple rows Dim WBO As Workbook Dim WSO As Worksheet Dim WBN As Workbook Dim WSN As Worksheet Dim LastRow As Long Dim i As Long Application.ScreenUpdating = False Set WBO = ActiveWorkbook Set WSO = WBO.ActiveSheet Set WBN = Workbooks.Add(template:=xlWBATWorksheet) Set WSN = WBN.Worksheets(1) LastRow = WSO.Cells(Rows.Count, "A").End(xlUp).Row NextRow = 2 For i = 2 To LastRow If Application.CountIf(WSO.Range(WSO.Cells(2, "A"), WSO.Cells(i, "A")), WSO.Cells(i, "A")) = 1 Then WSO.Cells(i, "A").Resize(1, 37).Copy Destination:=WSN.Cells(NextRow, "A") WSO.Cells(i, "AM").Resize(Application.CountIf(WSO.Range(WSO.Cells(2, "A"), WSO.Cells(LastRow, "A")), WSO.Cells(i, "A")), 1).Copy WSN.Cells(NextRow, "AM").PasteSpecial Transpose:=True NextRow = NextRow + 1 End If Next i WSN.Cells(2, "A").Select Workbooks(1).Activate Application.CutCopyMode = False Application.ScreenUpdating = True End Sub
Last edited by a moderator: