Vba insert new cell to a dynamic range

[Closed]
Report
Posts
1
Registration date
Saturday June 8, 2013
Status
Member
Last seen
June 8, 2013
-
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
-
Hello,

Hi,
Could anyone plz help me to achieve what I need ?

I want a code to insert and autofill a new cell to the range depending on what I enter in Cell A1
i.e. If i choose Field1 .. a new cell is added to the range "Field1" containing the no. 4
If I choose Field2 .. a new cell is added to the range "Field1" containing the no. 2
And so on

Thank u in advance.

[URL=http://imageshack.us/photo/my-images/23/682013110051am.png/][IMG]http://img23.imageshack.us/img23/7334/682013110051am.png[/IMG][/URL]

Uploaded with [URL=https://imageshack.com/]ImageShack.us[/URL]

1 reply

Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
Hi Tornado,

Here you go:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub

If Target.Value = "Field1" Then
Range("E" & Rows.Count).End(xlUp).Copy Range("E" & Rows.Count).End(xlUp).Offset(1, 0)
Range("E" & Rows.Count).End(xlUp).Value = Range("E" & Rows.Count).End(xlUp).Value + 1
End If

If Target.Value = "Field2" Then
Range("F" & Rows.Count).End(xlUp).Copy Range("F" & Rows.Count).End(xlUp).Offset(1, 0)
Range("F" & Rows.Count).End(xlUp).Value = Range("F" & Rows.Count).End(xlUp).Value + 1
End If

If Target.Value = "Field3" Then
Range("G" & Rows.Count).End(xlUp).Copy Range("G" & Rows.Count).End(xlUp).Offset(1, 0)
Range("G" & Rows.Count).End(xlUp).Value = Range("G" & Rows.Count).End(xlUp).Value + 1
End If

If Target.Value = "Field4" Then
Range("H" & Rows.Count).End(xlUp).Copy Range("H" & Rows.Count).End(xlUp).Offset(1, 0)
Range("H" & Rows.Count).End(xlUp).Value = Range("H" & Rows.Count).End(xlUp).Value + 1
End If

End Sub

Please implement code by right-clicking the sheets' tab and pasting the code in the big white field.

Best regards,
Trowa
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month