Hi New with VBA,
I've spent some time trying to get his macro to work. There are few issues that I cant get around. This macro needs to work across all the workshhets in the workbook, but only portion funcitons. Also some this code is taken from macro's that I recorded that worked fine indivually but not as a whole. The other error is the AutoFilter portion. I get an error that stating an issue with the method.
Any help would be greatly appreciated
MZING81
I've spent some time trying to get his macro to work. There are few issues that I cant get around. This macro needs to work across all the workshhets in the workbook, but only portion funcitons. Also some this code is taken from macro's that I recorded that worked fine indivually but not as a whole. The other error is the AutoFilter portion. I get an error that stating an issue with the method.
Code:
[FONT=Calibri][SIZE=3][COLOR=#000000] SUB MREPORT[/COLOR][/SIZE][/FONT]
[SIZE=3][COLOR=#000000][FONT=Calibri]Application.ScreenUpdating = False[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Application.Calculation = xlCalculationManual[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] ' unmergenew Macro[/FONT][/COLOR][/SIZE]
[FONT=Calibri][SIZE=3][COLOR=#000000]For Each WS In Worksheets[/COLOR][/SIZE][/FONT]
[SIZE=3][COLOR=#000000][FONT=Calibri] With WS.UsedRange[/FONT][/COLOR][/SIZE]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.WorksheetFunction.Application.Goto Reference:="R1C1"[/COLOR][/SIZE][/FONT]
[SIZE=3][COLOR=#000000][FONT=Calibri] Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Selection.UnMerge[/FONT][/COLOR][/SIZE]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]
[SIZE=3][COLOR=#000000][FONT=Calibri] Next WS[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri]' filtersort Macro[/FONT][/COLOR][/SIZE]
[FONT=Calibri][SIZE=3][COLOR=#000000]For Each WS In Worksheets[/COLOR][/SIZE][/FONT]
[SIZE=3][COLOR=#000000][FONT=Calibri] With WS.UsedRange[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Application.WorksheetFunction.Application.Goto Reference:="R8C1"[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Rows("8:8").Select[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Selection.AutoFilter[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] ActiveWorkbook.Worksheets.AutoFilter.Sort.SortFields.Clear[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] ActiveWorkbook.Worksheets.AutoFilter.Sort.SortFields.Add Key:=Range("D8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] xlSortNormal[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] With ActiveWorkbook.Worksheets.AutoFilter.Sort[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] .Header = xlYes[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] .MatchCase = False[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] .Orientation = xlTopToBottom[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] .SortMethod = xlPinYin[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] .Apply[/FONT][/COLOR][/SIZE]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]
[SIZE=3][COLOR=#000000][FONT=Calibri]'Remove head count data macro[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] With WS.UsedRange[/FONT][/COLOR][/SIZE]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.WorksheetFunction.Cells.Find(What:="actual:", After:=ActiveCell, LookIn:=xlFormulas, lookat _[/COLOR][/SIZE][/FONT]
[SIZE=3][COLOR=#000000][FONT=Calibri] :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] False, SearchFormat:=False).Activate[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Rows.Select[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Selection.Delete Shift:=xlUp[/FONT][/COLOR][/SIZE]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]
[SIZE=3][COLOR=#000000][FONT=Calibri]'Remergeonly Macro[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] With WS.UsedRange[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Columns("A:C").Select[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Selection.Merge True[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Columns("K:L").Select[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Selection.Merge True[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri]Application.WorksheetFunction.Application.Goto Reference:="R1C16"[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Selection.Copy[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Application.WorksheetFunction.Application.Goto Reference:="R3C7"[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] ActiveSheet.Paste[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Range("G1:J3").Select[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Application.WorksheetFunction.Application.CutCopyMode = False[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Selection.Merge True[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Range("F1:J3").Select[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Selection.Merge True[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Range("F3:J3").Select[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] With Selection[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] .HorizontalAlignment = xlCenter[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] .VerticalAlignment = xlTop[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] .WrapText = True[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] .Orientation = 0[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] .AddIndent = False[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] .IndentLevel = 0[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] .ShrinkToFit = False[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] .ReadingOrder = xlContext[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] .MergeCells = True[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] End With[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] With Selection[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] .HorizontalAlignment = xlCenter[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] .VerticalAlignment = xlCenter[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] .WrapText = True[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] .Orientation = 0[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] .AddIndent = False[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] .IndentLevel = 0[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] .ShrinkToFit = False[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] .ReadingOrder = xlContext[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] .MergeCells = True[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Columns("O:P").Select[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Selection.Merge True[/FONT][/COLOR][/SIZE]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]
[SIZE=3][COLOR=#000000][FONT=Calibri] Dim FoundCell As Range[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Dim FirstAddress As String[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Dim PrevAddress As String[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Dim CurrAddress As String[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Dim SearchTerm As String[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] SearchTerm = "Manning Check Report"[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] With Columns("G:K")[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Set FoundCell = .Find(SearchTerm, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] If Not FoundCell Is Nothing Then[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] FoundCell.Name = "FirstAddress"[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Do[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] PrevAddress = FoundCell.Address[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] FoundCell.Resize(3).EntireRow.Insert[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] ActiveSheet.HPageBreaks.Add before:=Range(PrevAddress)[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Set FoundCell = .FindNext(FoundCell)[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Loop While FoundCell.Address <> Range("FirstAddress").Address[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Else[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] MsgBox "No search term found...", vbExclamation[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] End If[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Application.ScreenUpdating = True[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri] Application.Calculation = xlCalculationAutomatic[/FONT][/COLOR][/SIZE]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]
Any help would be greatly appreciated
MZING81