arehman1289
New Member
- Joined
- Dec 10, 2020
- Messages
- 22
- Office Version
- 365
- Platform
- Windows
Hello,
I have a worksheet in which a user inputs a number 'x' and depending on it lines 16-27 are copied 'x' times in the rows after. I would please ask for help in three issues:
1. I have three worksheets of this sort, and would require the same number 'x' to be copied in each worksheet after the users input and eventually duplicate lines. However at the moment I am having the user enter the number 'x' three different times in each worksheet. Is it possible for the user to just enter it once in the first worksheet and it gets updated automatically in the next two sheets ?
2. As an example, if the user enters 'x' as 3 i would like lines 16-27 copied 3 times. However, if in the next run he enters 2, i would like the third copied set to be deleted. How would I do that ?
3. Once again if the user enters 'x' as 3 i would like to save a specific cell value to be compared with the same cell value in next run in case he enters 'x' as 2. Is there anyway to do the comparison ?
Attached is the code for workheet 1:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim nr As Long
Dim b As Long
' Exit if multiple cells updating simultaneously
If Target.CountLarge > 1 Then Exit Sub
' Only run if cell B1 updated
If Target.Address = Range("C2").Address Then
' Only run if numeric value entered
If IsNumeric(Target.Value) And (Target.Value > 0) Then
Application.ScreenUpdating = False
Application.EnableEvents = False
' Set default value of next row
nr = 28
' Loop through number of times to copy
For i = 1 To (Int(Target.Value) - 1)
' Copy values from A2:B5 to next range
Range("A16:D27").Copy Cells(nr, "A")
For b = 0 To 11
Cells((nr + b), "A").Interior.ColorIndex = nr
Cells((nr + b), "B").Interior.ColorIndex = nr
Cells((nr + b), "C").Interior.ColorIndex = nr
Cells((nr + b), "D").Interior.ColorIndex = nr
Next b
' Increment next start by 4 rows
nr = nr + 12
Next i
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End If
End Sub
Please help !! Thank you so much in advance for the help.
I have a worksheet in which a user inputs a number 'x' and depending on it lines 16-27 are copied 'x' times in the rows after. I would please ask for help in three issues:
1. I have three worksheets of this sort, and would require the same number 'x' to be copied in each worksheet after the users input and eventually duplicate lines. However at the moment I am having the user enter the number 'x' three different times in each worksheet. Is it possible for the user to just enter it once in the first worksheet and it gets updated automatically in the next two sheets ?
2. As an example, if the user enters 'x' as 3 i would like lines 16-27 copied 3 times. However, if in the next run he enters 2, i would like the third copied set to be deleted. How would I do that ?
3. Once again if the user enters 'x' as 3 i would like to save a specific cell value to be compared with the same cell value in next run in case he enters 'x' as 2. Is there anyway to do the comparison ?
Attached is the code for workheet 1:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim nr As Long
Dim b As Long
' Exit if multiple cells updating simultaneously
If Target.CountLarge > 1 Then Exit Sub
' Only run if cell B1 updated
If Target.Address = Range("C2").Address Then
' Only run if numeric value entered
If IsNumeric(Target.Value) And (Target.Value > 0) Then
Application.ScreenUpdating = False
Application.EnableEvents = False
' Set default value of next row
nr = 28
' Loop through number of times to copy
For i = 1 To (Int(Target.Value) - 1)
' Copy values from A2:B5 to next range
Range("A16:D27").Copy Cells(nr, "A")
For b = 0 To 11
Cells((nr + b), "A").Interior.ColorIndex = nr
Cells((nr + b), "B").Interior.ColorIndex = nr
Cells((nr + b), "C").Interior.ColorIndex = nr
Cells((nr + b), "D").Interior.ColorIndex = nr
Next b
' Increment next start by 4 rows
nr = nr + 12
Next i
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End If
End Sub
Please help !! Thank you so much in advance for the help.