assign or copy paste values of some sheet1 cells to exact columns in sheet2 using loop which counter is value entered in column eg:A1 cell

Question

hope someone can help i have this code bellow working fine because i determined the range and exactly gave A1 numeric value as loop counter and starting point.

Private Sub Worksheet_Change(ByVal Target As Range)

    If (Range("A1") <> "") And (IsNumeric(Range("A1"))) And (Range("A1") > 0) Then
         Dim X As Integer
         If Not Intersect(Target, Range("A1")) Is Nothing Then
             For X = 1 To Range("A1").Value
                 Sheet4.Range("b" & X).Value = Range("A1").Value
             Next X
             MsgBox "done"
         Else
         End If
     Else
         MsgBox "no numeric"
     End If
End Sub

now

i want to expand this code above so when user fill sheet1 A1 by 5 then paste values to 5 cells in sheet2 starting from first empty cell in sheet2 eg: b1:b5 or b10:b15 respectifly. in next time i dont know in which cell in sheet1 column A will be filled may be A2' A3'A10'A80 or any A column cells so when it filled next time do the same thing loop for entered value times and paste or assign values to sheet2 b first empty cell and next to loop count cells.

Answer 1

the solution is

Option Explicit Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range Set KeyCells = Range("H:H") If (Target.Value <> "") And (IsNumeric(Target.Value)) And (Target.Value > 0) And ((Target.HasFormula) = False) Then

If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then Dim X As Integer Sheets("sheet2").Activate For X = Sheets("sheet2").Range("G100").End(xlUp).Row To Sheets("sheet2").Range("G100").End(xlUp).Row + Target.Value - 1 Sheets("sheet2").Range("B" & X + 1).Value = Sheets("sheet1").Range("B" & Target.Row) Sheets("sheet2").Range("C" & X + 1).Value = Sheets("sheet1").Range("C" & Target.Row) Sheets("sheet2").Range("D" & X + 1).Value = Sheets("sheet1").Range("D" & Target.Row) Sheets("sheet2").Range("E" & X + 1).Value = Sheets("sheet1").Range("E" & Target.Row)

Sheets("sheet2").Range("G" & X + 1).Value = "Enter serial" Next X 'MsgBox Target.Address MsgBox "done" & X Else End If Else MsgBox "Wrong Value! You Must Enter Number greater Than 0 " End If End Sub

but now how can i update the rows in sheet2 if a user change the value on sheet1 Range("H:H") i need a way to insert new rows if the user entered greater value than the first he entered. or i need a way to delete extra rows if the user entered smaller value than the first he entered.