# VBA coding for the question

#### vishwajeet_chakravorty

##### Board Regular
Dear Sir, Suppose I write any number (Example 100) in Cell A1, the cells A2 to A12 should be filled with the same number automatically. And again in cell A4 I write any other number, the cell from A5 to A12 should be filled with the same number automatically. How to write a VBA code for this thing to happen?

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### jproffer

##### Well-known Member
You could put this in your sheet code module....or if you want that on every sheet, convert to Workbook_SheetChange

Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rw As Long
Dim Col As Long
Dim i As Integer
Rw = Target.Row
Col = Target.Column

If Rw >= 12 Then Exit Sub

For i = Rw + 1 To 12
Cells(i, Col).Value = Cells(Rw, Col).Value
Next i
End Sub``````

#### VoG

##### Legend
Try this: right click the sheet tab, select View Code and paste in

Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row < 12 Then
Application.EnableEvents = False
Range(Cells(Target.Row + 1, 1), Cells(12, 1)).Value = Target.Value
Application.EnableEvents = True
End If
End Sub``````

#### krausr79

##### Board Regular
for x=2 to 12
cells(x,1).vale=cells(1,1).value
next

for x=5 to 12
cells(x,4).vale=cells(1,4).value
next

#### vishwajeet_chakravorty

##### Board Regular
Try this: right click the sheet tab, select View Code and paste in

Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row < 12 Then
Application.EnableEvents = False
Range(Cells(Target.Row + 1, 1), Cells(12, 1)).Value = Target.Value
Application.EnableEvents = True
End If
End Sub``````
Dear sir It is working in cell A1 to A12. Now my questions are
1)If I want to repeat the same action from C1 to C12 what will be the changes in the existing code?
2) If I want to do, not from A1 to A12 but from A4 to A15 them what will be the changes in the existing code? Actually I want to see the differences in coding and how it works.

#### VoG

##### Legend
This will work in any column for rows up to 12

Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 12 Then
Application.EnableEvents = False
Range(Cells(Target.Row + 1, Target.Column), Cells(12, Target.Column)).Value = Target.Value
Application.EnableEvents = True
End If
End Sub``````

#### vishwajeet_chakravorty

##### Board Regular
This will work in any column for rows up to 12

Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 12 Then
Application.EnableEvents = False
Range(Cells(Target.Row + 1, Target.Column), Cells(12, Target.Column)).Value = Target.Value
Application.EnableEvents = True
End If
End Sub``````
Dear Sir, If I do not want in all the columns. I want to do it in few columns like A1 to A12, C1 to C12, E1 to E12 and H1 to H12, then how should I change this code?

#### VoG

##### Legend
Try

Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 12 Then
Select Case Target.Column
Case 1, 3, 5, 8
Application.EnableEvents = False
Range(Cells(Target.Row + 1, Target.Column), Cells(12, Target.Column)).Value = Target.Value
Application.EnableEvents = True
End Select
End If
End Sub``````

#### vishwajeet_chakravorty

##### Board Regular
Try

Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 12 Then
Select Case Target.Column
Case 1, 3, 5, 8
Application.EnableEvents = False
Range(Cells(Target.Row + 1, Target.Column), Cells(12, Target.Column)).Value = Target.Value
Application.EnableEvents = True
End Select
End If
End Sub``````
Dear Sir,
Now one more question If I want to do the same from A5 to A15, C5 to C15 and F5 to F15, Then want will be the changes. Thanks for the quick reply.

#### VoG

##### Legend
You should be able to see a pattern emerging:

Rich (BB code):
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 15 Then
Select Case Target.Column
Case 1, 3, 6
Application.EnableEvents = False
Range(Cells(Target.Row + 1, Target.Column), Cells(15, Target.Column)).Value = Target.Value
Application.EnableEvents = True
End Select
End If
End Sub``````