Excel - Creating a simplified "view" of a more complex table (macro?)

Introduction: Sheet1 in my excel workbook contains a "complex" table with about 130 columns and 2,5k rows.

First issue: Based on this table i want to create a simplified table("View") in Sheet2, which contains only e.g. columns A,C,F,G,I from the table in Sheet1.

This new "simplified" table should be updated when the table in Sheet1 is changed, e.g. in the form of a cell beeing edited, or a new row beeing inserted. Another way of approaching this would be to auto generate the table in Sheet2 everytime the user saves the workbook, or something similar.

Second issue: The rows which contains a specific value in column F, lets say the value "XXX", should not be included in the Sheet2 table.

I'm not very experienced in more "advanced" excel functionality, but after researching the matter im leaning towards approaching this by creating a macro. Is this assumption correct? Or does excel have any other functionality to perform this task?

If creating a macro is the best approach, then any help would be much appreciated. I am currently playing arround with a test table with 4 r开发者_如何学运维ows, and copying two of these into a new worksheet. But i am having troubles with how to do this automatically, and exluding the rows with a specific value(Issue #2)

Try the following code: (Placed in the simple sheets module)

Private Sub Worksheet_Activate()

    Application.ScreenUpdating = False

    With ActiveSheet
        .UsedRange.ClearContents
        '// Copy from data sheet too presentation sheet
        Sheet1.UsedRange.Copy .[a1]
        '// Filter all rows with xxx
        .Range("F:F").AutoFilter Field:=1, Criteria1:="xxx", Operator:=xlAnd
        '// Delete all rows with xxx excluding the header
        .UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        '// Remove the autofilter
        If .AutoFilterMode Then .AutoFilterMode = False
        '// Delete unwanted columns
        .Range("B:B,D:E,H:H").Delete
     End With

    Application.ScreenUpdating = True

End Sub

The way I do this is using some code in the on_activate method of the worksheet.

That way, as soon as the user selects that sheet, the data is refreshed.

Here's pseudo code:

option explicit 'but thats just me.

Private Sub Worksheet_Activate()  <<-- event of the simplified sheet.
    Worksheets("simplesheet").Cells.ClearContents
    Worksheets("alldata").Columns("A:A").Copy
    Worksheets("simplesheet").Columns("A:A").Select
    Worksheets("simplesheet").Paste
    'etc for all the relevant rows
End Sub

Second issue: The rows which contains a specific value in column F, lets say the value "XXX", should not be included in the Sheet2 table.

Add the following to the event

    dim cel as range

    'for each goes in the wrong direction, we need to start down and go up.
    '1-select the REAL last cell in a range.
    Worksheets("simplesheet").Range("F65536").End(xlup).Select 'edit for Excel 2007
    Set cel = selection
    while cel.row > 0 
      if cel.value = "xxxx" then cel.entirerow.delete
      set cel = cel.offset(-1,0)
    wend
    Worksheets("simplesheet").Range("a1").select