Hello,

I want to have a totaly automatic macro. With that I mean that everytime i
change
in cell A1 the macro should run. What I´m trying to do is a tabel that will
automatic
sort after changed parameters. Do anyone know how to do this?
I´l be wery grateful!

BR
Mia

Mia,

You can create a table of formulas that will sort your data based on a ranking that you devise: put
a column of RANK functions as the first column of your data, and key your VLOOKUPs to that column to
extract (and sort) the data automatically.

You can also use the worksheet change event to sort your data whenever cell A1 is changed.


HTH,
Bernie
MS Excel MVP


"Mia" wrote in message
...
Hello,

I want to have a totaly automatic macro. With that I mean that everytime i
change
in cell A1 the macro should run. What I´m trying to do is a tabel that will
automatic
sort after changed parameters. Do anyone know how to do this?
I´l be wery grateful!

BR
Mia

Mia, I'm using Excel 2007.

Getting Started With Events

The easiest way to start with events and event procedures is to allow the
VBA editor to build the shell code for you. In Excel, right click one of the
sheet tabs at the bottom of the main window and choose View Code from the
pop-up menu. This will open the VBA Editor to the code module associated with
that worksheet. In that code window you will see two drop down boxes at the
top of the code window.

Change the (General) setting to Worksheet and then change SeletionChange to
Change. This will add the event procedure declaration for the Change event to
the code module, as shown below:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Within the Worksheet_Change procedure, you can add any code that you want to
take place when a cell value is changed. This event is raised automatically
by Excel when a cell's value is changed either by user input or by other VBA
code. It is not raised if the value of the cell is changed by a formula
calculation in that cell.

See my final code below.

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.ListObjects("Table1").AutoFilter.Apply Filter
With ActiveWorkbook.Worksheets("Sheet1").ListObjects("T able1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

You need to "Table1" with the name of your table and "Sheet1" with the name
of your sheet. You also have to establish a Sort to the Filter.

Hope that helps.

"Mia" wrote:

Hello,

I want to have a totaly automatic macro. With that I mean that everytime i
change
in cell A1 the macro should run. What I´m trying to do is a tabel that will
automatic
sort after changed parameters. Do anyone know how to do this?
I´l be wery grateful!

BR
Mia

Thanks!


I tryed your code but I have som questions.

If I want the table to sort in column 2 and it should be sorted
depending whats in cell A1? What code should I use?
Do I have to make another module with that macro.

BR
Mia

My code so far:
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.ListObjects("Tabell101226").AutoFilter .ApplyFilter
With ActiveWorkbook.Worksheets("Betalningsuppföljning
total").ListObjects("Tabell101226").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub








"h2fcell" skrev:

Mia, I'm using Excel 2007.

Getting Started With Events

The easiest way to start with events and event procedures is to allow the
VBA editor to build the shell code for you. In Excel, right click one of the
sheet tabs at the bottom of the main window and choose View Code from the
pop-up menu. This will open the VBA Editor to the code module associated with
that worksheet. In that code window you will see two drop down boxes at the
top of the code window.

Change the (General) setting to Worksheet and then change SeletionChange to
Change. This will add the event procedure declaration for the Change event to
the code module, as shown below:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Within the Worksheet_Change procedure, you can add any code that you want to
take place when a cell value is changed. This event is raised automatically
by Excel when a cell's value is changed either by user input or by other VBA
code. It is not raised if the value of the cell is changed by a formula
calculation in that cell.

See my final code below.

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.ListObjects("Table1").AutoFilter.Apply Filter
With ActiveWorkbook.Worksheets("Sheet1").ListObjects("T able1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

You need to "Table1" with the name of your table and "Sheet1" with the name
of your sheet. You also have to establish a Sort to the Filter.

Hope that helps.

"Mia" wrote:

Hello,

I want to have a totaly automatic macro. With that I mean that everytime i
change
in cell A1 the macro should run. What I´m trying to do is a tabel that will
automatic
sort after changed parameters. Do anyone know how to do this?
I´l be wery grateful!

BR
Mia

Hi,
All you need to do is manually sort the table with the conditions you need,
and save the file. The statement:

ActiveSheet.ListObjects("Tabell101226").AutoFilter .ApplyFilter
With ActiveWorkbook.Worksheets("Betalningsuppföljning
total").ListObjects("Tabell101226").Sort

Is reapplying the Filter with the established Sort conditions.

"Mia" wrote:

Thanks!


I tryed your code but I have som questions.

If I want the table to sort in column 2 and it should be sorted
depending whats in cell A1? What code should I use?
Do I have to make another module with that macro.

BR
Mia

My code so far:
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.ListObjects("Tabell101226").AutoFilter .ApplyFilter
With ActiveWorkbook.Worksheets("Betalningsuppföljning
total").ListObjects("Tabell101226").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub








"h2fcell" skrev:

Mia, I'm using Excel 2007.

Getting Started With Events

The easiest way to start with events and event procedures is to allow the
VBA editor to build the shell code for you. In Excel, right click one of the
sheet tabs at the bottom of the main window and choose View Code from the
pop-up menu. This will open the VBA Editor to the code module associated with
that worksheet. In that code window you will see two drop down boxes at the
top of the code window.

Change the (General) setting to Worksheet and then change SeletionChange to
Change. This will add the event procedure declaration for the Change event to
the code module, as shown below:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Within the Worksheet_Change procedure, you can add any code that you want to
take place when a cell value is changed. This event is raised automatically
by Excel when a cell's value is changed either by user input or by other VBA
code. It is not raised if the value of the cell is changed by a formula
calculation in that cell.

See my final code below.

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.ListObjects("Table1").AutoFilter.Apply Filter
With ActiveWorkbook.Worksheets("Sheet1").ListObjects("T able1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

You need to "Table1" with the name of your table and "Sheet1" with the name
of your sheet. You also have to establish a Sort to the Filter.

Hope that helps.

"Mia" wrote:

Hello,

I want to have a totaly automatic macro. With that I mean that everytime i
change
in cell A1 the macro should run. What I´m trying to do is a tabel that will
automatic
sort after changed parameters. Do anyone know how to do this?
I´l be wery grateful!

BR
Mia

Aha,

Thank you so much! I´ve got it to work!
It´s sorting the way I want.

You saved my day!

Br
Mia




"h2fcell" skrev:

Hi,
All you need to do is manually sort the table with the conditions you need,
and save the file. The statement:

ActiveSheet.ListObjects("Tabell101226").AutoFilter .ApplyFilter
With ActiveWorkbook.Worksheets("Betalningsuppföljning
total").ListObjects("Tabell101226").Sort

Is reapplying the Filter with the established Sort conditions.

"Mia" wrote:

Thanks!


I tryed your code but I have som questions.

If I want the table to sort in column 2 and it should be sorted
depending whats in cell A1? What code should I use?
Do I have to make another module with that macro.

BR
Mia

My code so far:
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.ListObjects("Tabell101226").AutoFilter .ApplyFilter
With ActiveWorkbook.Worksheets("Betalningsuppföljning
total").ListObjects("Tabell101226").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub








"h2fcell" skrev:

Mia, I'm using Excel 2007.

Getting Started With Events

The easiest way to start with events and event procedures is to allow the
VBA editor to build the shell code for you. In Excel, right click one of the
sheet tabs at the bottom of the main window and choose View Code from the
pop-up menu. This will open the VBA Editor to the code module associated with
that worksheet. In that code window you will see two drop down boxes at the
top of the code window.

Change the (General) setting to Worksheet and then change SeletionChange to
Change. This will add the event procedure declaration for the Change event to
the code module, as shown below:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Within the Worksheet_Change procedure, you can add any code that you want to
take place when a cell value is changed. This event is raised automatically
by Excel when a cell's value is changed either by user input or by other VBA
code. It is not raised if the value of the cell is changed by a formula
calculation in that cell.

See my final code below.

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.ListObjects("Table1").AutoFilter.Apply Filter
With ActiveWorkbook.Worksheets("Sheet1").ListObjects("T able1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

You need to "Table1" with the name of your table and "Sheet1" with the name
of your sheet. You also have to establish a Sort to the Filter.

Hope that helps.

"Mia" wrote:

Hello,

I want to have a totaly automatic macro. With that I mean that everytime i
change
in cell A1 the macro should run. What I´m trying to do is a tabel that will
automatic
sort after changed parameters. Do anyone know how to do this?
I´l be wery grateful!

BR
Mia