VBA If cell contains a number X. Copy Row Range and paste X times

[menor59]

hello all.

I think this is a Simple Request...

On a sheet i have in C10 a Value default 0

Lets say i put 6 there
On C10's Value of 6 copy F3:Y3 below it 5 times...

If C10 is lets say 10, On C10's Value of 10 and copy 9 times below F3:Y3

IF C10 is 0 Clear Everything below F3:Y3

(F3:Y3) is the template row Basically...there is some conditional formating, Formulas, and Data Validation there also that needs to populate also with the value in C10

Thoughts or comments?

 

[Peter_SSs]

Assuming that we can tell how many rows of data in columns F:Y from column F then you could try this Worksheet_Change event code. Post back if you need instructions to implement it.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim myR As Range
  
  Set myR = Range("C10")
  If Not Intersect(Target, myR) Is Nothing Then
    Application.EnableEvents = False
    Range("F3:Y" & Range("F" & Rows.Count).End(xlUp).Row).Offset(1).Clear
    On Error Resume Next
    If myR.Value >= 1 Then Range("F3:Y3").Copy Destination:=Range("F3:Y3").Resize(myR.Value)
    On Error GoTo 0
    Application.EnableEvents = True
  End If
End Sub

 

[menor59]

OK, That Cleared all of F3:Y3 when i put a number in C10

 

[menor59]

OK, That Cleared all of F3:Y3 when i put a number in C10

F3:Y3 Should Stay because thats where the default data is that i am copying down if its set to 0...

 

[menor59]

Fixed it...Thank you!!!

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim myR As Range
  
  Set myR = Range("C10")
  If Not Intersect(Target, myR) Is Nothing Then
    Application.EnableEvents = False
    Range("F4:Y" & Range("F" & Rows.Count).End(xlUp).Row).Offset(2).Clear
    On Error Resume Next
    If myR.Value >= 1 Then Range("F3:Y3").Copy Destination:=Range("F3:Y3").Resize(myR.Value)
    On Error GoTo 0
    Application.EnableEvents = True
  End If
End Sub

 

[menor59]

I dont know why it fixed it thou...I changed...

Range("F4:Y" & Range("F" & Rows.Count).End(xlUp).Row).Offset(2).Clear

 

[Peter_SSs]

I dont know why it fixed it thou...

What was in F3 before you ran the code?

I had assumed that there was some value or formula in F3 before the code was run.

I don't know if it matters, but with your code if you enter, say 8, then you will have something in rows 3:10. If you then enter 2 in C10 you will have data in rows 3:5, not 3:4

Should any old data be cleared from rows 4, 5, 6, ... before row 3 is copied again when a new number is entered in C10?

 

[menor59]

Hello Peter and Folks...The above Code works Perfectly...but...theres a but...

 Dim myR As Range
  
  Set myR = Range("C10")
  If Not Intersect(Target, myR) Is Nothing Then
    Application.EnableEvents = False
    Range("F4:Y4" & Range("F" & Rows.Count).End(xlUp).Row).Offset(0).Clear
    On Error Resume Next
    If myR.Value >= 1 Then Range("F3:Y3").Copy Destination:=Range("F3:Y3").Resize(myR.Value)
    On Error GoTo 0
    Application.EnableEvents = True
  End If

say the Value in C10 is 3...

And It copies whats in F3:Y3 (Blank) Correctly... Into F4:Y4 3 times Blanks....

Now...Lets say i put data Into those cells...

I come back later and i need to have C10 a different number than 3....Lets say 10....

Its taking the data thats in F3:Y3 and copying it down into F4:Y4 10 times...

what i want to do is...

If C10 was Changed from 3...to 10....

Give me empty Cells (without text from F3:Y3) 7 times below the the last cell range that containes data...Does that make sense?

 

[menor59]

From this:

To this:

Keeping the Data Above...But taking the Formating and pulldowns from The First Range..