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!

 

[Michael M]

Can you give us more detail than " the hide macros are already messed up."
Is there an error message ?
If not, what is happening ?

 

[Dan_W]

It would be helpful if you can explain how it's not working - because we can't see what's happening, we need to try and recreate the problem on our end. And because we don't have your data, that's a bit tricky.

There is a lot of code in there that you don't need (like when you press down on the scroll bar, for example), but that's pretty standard when it comes to recording macros. The superfluous code can sometimes cause problems, but not always - without further information about what's going wrong, it's hard to say. A few things jump out at me: (1) It seems that you're using relative referencing (I think) - which is where it will make changes to cells relative to whichever cell is the 'active' one. This means that in order to perfectly reproduce the intended effect, you need to have 'started' having selected the cell that was active when you recorded it; (2) the sort order is based on PinYin - is that intended?

But other than that it should, at a minimum, hide certain cells. In the code, it has:

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

which, although slightly verbose, will do exactly what it says on the tin.

Sometimes it helps to 'step through' the code. This means that you make VBA run the code on a line by line basis each time you press a button (F8). If you go through it that way, you might be able to get a clearer understanding of where things go wrong.

 

[YIDA]

Can you give us more detail than " the hide macros are already messed up."
Is there an error message ?
If not, what is happening ?

I tried to hide these cells, and I double checked the code it's correct.

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

But when I tried to run it only column AA:AB are shown, the rest are all hidden.
Like this:

So far I have only been able to identified the first part of the issue, the rest of the codes I'll need to go through it again once I get this fixed.

It would be helpful if you can explain how it's not working - because we can't see what's happening, we need to try and recreate the problem on our end. And because we don't have your data, that's a bit tricky.

There is a lot of code in there that you don't need (like when you press down on the scroll bar, for example), but that's pretty standard when it comes to recording macros. The superfluous code can sometimes cause problems, but not always - without further information about what's going wrong, it's hard to say. A few things jump out at me: (1) It seems that you're using relative referencing (I think) - which is where it will make changes to cells relative to whichever cell is the 'active' one. This means that in order to perfectly reproduce the intended effect, you need to have 'started' having selected the cell that was active when you recorded it; (2) the sort order is based on PinYin - is that intended?

But other than that it should, at a minimum, hide certain cells. In the code, it has:

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

which, although slightly verbose, will do exactly what it says on the tin.

Sometimes it helps to 'step through' the code. This means that you make VBA run the code on a line by line basis each time you press a button (F8). If you go through it that way, you might be able to get a clearer understanding of where things go wrong.

(1) Yes, I agree with you I am using relative referencing, I tried recording and run again but the same issue pops up.
(2) No, it isn't. The dates are sort based on Oldest to Newest.

 

[Michael M]

Works for me.....

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

 

[Dan_W]

But when I tried to run it only column AA:AB are shown, the rest are all hidden.

Like @Michael M, that works for me too. When you say the other columns are all hidden, do you meant the columns AV to AY and BM to BO? If so, are the other columns visible before you run the code? Because this code won't make hidden columns visible. To do that, the following code will make all columns on the sheet visible. You just need to put it at the start of corrected code you just posted above:

Cells.Columns.Hidden = False

Remember when running VBA code, you won't be able to undo it, so it's best to practice on sample data/workbooks.

 

[Alex Blakenburg]

Sorry Yida but I have to ask, what happens when you click on the 3 in the top left hand corner ?

The code shown above does not show any statements containing ".Columns.Group"

 

[YIDA]

Sorry Yida but I have to ask, what happens when you click on the 3 in the top left hand corner ?

The code shown above does not show any statements containing ".Columns.Group"

View attachment 42316

It expands some hidden columns, it's kind of like a default setting for the file.

 

[Alex Blakenburg]

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

But when I tried to run it only column AA:AB are shown, the rest are all hidden.

 

[YIDA]

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

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