Recorded Macro but isn't working properly?

[YIDA]

Hello Excel Pros, I am working in Production and everyday I will received a Work In Progress excel sheet

Everyday I had to go over the same action just to show me the relevant data I needed

Until recently I found out about recording Macro and decided to try it

I started recording, click stop recording, tried to run it. But everything went wrong.

How can I fix this?

The purpose of my recording is to

1. Hide a few cells that are not relevant to me

2. Sort date oldest to newest

3. Filter out WIP for shift A (there are 2 shifts)

4. Sum up 2 rows and filter out the numbers (less than or equal to) 50

I tried to find the problem of where went wrong by breaking down into 4 recordings, and I found out that the Step 1: the hide macros are already messed up. Below is the code.

' wip Macro

'

'

Range("K1247").Select

ActiveCell.FormulaR1C1 = "a"

Columns("A:C").Select

Selection.EntireColumn.Hidden = True

Rows("7:7").Select

Selection.UnMerge

ActiveWorkbook.Worksheets("07-03").AutoFilter.Sort.SortFields.Clear

ActiveWorkbook.Worksheets("07-03").AutoFilter.Sort.SortFields.Add Key:=Range( _

"D7:D1248"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

xlSortNormal

With ActiveWorkbook.Worksheets("07-03").AutoFilter.Sort

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

ActiveSheet.Range("$A$7:$CV$1248").AutoFilter Field:=11, Criteria1:="A"

Columns("AC:AU").Select

Selection.EntireColumn.Hidden = True

Columns("AZ:BK").Select

Selection.EntireColumn.Hidden = True

Columns("BP:CE").Select

Selection.EntireColumn.Hidden = True

Range("CU10").Select

ActiveCell.FormulaR1C1 = "=RC[-33]+RC[-49]"

Range("CU10").Select

Selection.FillDown

ActiveSheet.Range("$A$7:$CV$1248").AutoFilter Field:=99, Criteria1:="<=-50" _

, Operator:=xlAnd

ActiveWindow.ScrollColumn = 28

ActiveWindow.ScrollColumn = 24

ActiveWindow.ScrollColumn = 13

ActiveWindow.ScrollColumn = 12

ActiveWindow.ScrollColumn = 10

ActiveWindow.SmallScroll Down:=51

End Sub

Please help!
Many thanks!

 

[Alex Blakenburg]

I think the post got sent before you finished it.

 

[YIDA]

So when you say "the rest are hidden". Can you confirm that its not the grouping that is hiding the rest ?

No it is not. When I clicked on it, it expands on the column which are hidden by the author, it does not affect my recorded macro.

However, I am starting to think that due to certain formatting or formula that has done by the author, my recorded macro cannot properly adjust to what I want it to do. Therefore, the error.
Is there anyway to reset the formatting or formula before I start my recording again?

 

[Alex Blakenburg]

No it is not. When I clicked on it, it expands on the column which are hidden by the author, it does not affect my recorded macro.

However, I am starting to think that due to certain formatting or formula that has done by the author, my recorded macro cannot properly adjust to what I want it to do. Therefore, the error.
Is there anyway to reset the formatting or formula before I start my recording again?

The most likely cause of your issue is that you have merged cells. When you are selecting a column it is picking up "all" the columns involved in the merge.

Try hiding the columns without using the select statement.
(If there are other similar statements elsewhere in the code make sure you make the change there too)

So replace these:-

    Columns("A:C").Select
    Selection.EntireColumn.Hidden = True

    Columns("AC:AU").Select
    Selection.EntireColumn.Hidden = True
    Columns("AZ:BK").Select
    Selection.EntireColumn.Hidden = True
    Columns("BP:CE").Select
    Selection.EntireColumn.Hidden = True

With this

    Columns("A:C").Hidden = True

    Columns("AC:AU").Hidden = True
    Columns("AZ:BK").Hidden = True
    Columns("BP:CE").Hidden = True