Autofill below row based above row ...

[maabadi]

Hi
I want a macro to when I change or add value in one cell (e.g: D5) then auto-fill the below row ( row no. 6 from column A to M) and convert the formula in above row (row no. 4 from column A to M) convert to number to decrease size of file.
I recorded this macro in excel but it only work for one row.
Please Help me and correct it.

Sub Macro2()
'
' Macro2 Macro
'

'
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "45"
    ActiveCell.Offset(0, -4).Range("A1:M1").Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:M2"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:M2").Select
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveCell.Offset(-1, 0).Range("A1:M1").Select
    Selection.Copy
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

[maabadi]

I have paste in new "worksheet_change" event. and its name is "Change".
In new excel file also name of module is "change".
This is my workbook change event:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Intersect(Target, Sh.Range("B2")) Is Nothing Then
        Call RefreshFilter
    End If
End Sub

and this is RefreshFilter Macro:

Sub RefreshFilter()

    ' Defines variable
    Dim Tbl As Object

    ' For each table in the active sheet
    For Each Tbl In ActiveSheet.ListObjects
        ' Refresh table filter
        Tbl.Range.AutoFilter Field:=2, Criteria1:="<>"
    ' Next table
    Next Tbl
    
End Sub

 

[Joe4]

In new excel file also name of module is "change".

If that is where you placed the code I gave you, it won't work. Worksheet_Change event procedures MUST go in Sheet modules.

The directions I gave you will get you there exactly - do NOT try to use ALT-F11 to get there!
Whatever sheet you want this to apply do, right-click on the sheet tab name at the bottom of the screen and select View Code. This will take you directly to the Sheet module behind the sheet you want to apply this to, which is exactly where that code needs to go.

 

[maabadi]

Thanks Joe4
It is work great.

Thanks again

 

[Joe4]

You are welcome!

Yes, Event Procedure VBA code is very helpful, but the rules are very strict. They must be in the proper module, and they must be named a certain way, or else they won't work.
Here is a good write-up Chip Pearson did on them so years ago: http://www.cpearson.com/excel/Events.aspx