Need Help with Pasting a cell X times on same sheet based on Value

[berenger]

I have a user form where the user will enter in how many labels they want printed. This value shows up on the spreadsheet in column Q2:Q640. Depending on what the value is I want it to copy the data in column C2:C640 to the next cell below. For instance if there is a quantity in Q2 of 5 and the data in C2 is 123456 I want C2 to be copied and pasted 4 more times into C3,C4,C5,C6 and this to happen on each row after that based on the value in Column Q. The code I have been trying isn't copying or pasting for me. Any help or guidance in the right direction would help me out a great deal

Private Sub LabelQTY_Change()
Dim y As Long
    If TargetAddress = "$Q$2" Then
        If IsNumeric(Target) And Not IsEmpty(Target) Then
            Application.EnableEvents = False
            y = Range("Q2:Q640").Value
            Range("C2:C640").AutoFill Destination:=Range("C2:C" & 2 + i), _
            Type:=xlFillDefault
            Application.EnableEvents = True
        End If
        On Error Resume Next
    End If
End Sub

 

[berenger]

Here is how I have my code written.

Private Sub CommandButton1_Click()
sheet4.Activate
    If IsEmpty(Description) Or Description = "" Then
        MsgBox "No such product found. Please enter a valid UPC or Item #."
        Exit Sub
    End If
    
    'set quantity of labels
Dim rng     As Range
Dim Dn      As Range
Dim rws     As Integer
Dim c       As Long
Dim Ray As Variant
Set rng = Range(Range("Q2"), Range("Q" & Rows.Count).End(xlUp))
Ray = Application.Transpose(Range(Range("C2"), Range("C" & Rows.Count).End(xlUp)))
rws = 0
For Each Dn In rng
    c = c + 1
    If IsNumeric(Dn) And Dn > "" Then
        Dn.Offset(rws, -14).Resize(Dn) = Ray(c)
        rws = rws + Dn - 1
    End If
Next Dn
    'set label counter
Dim i       As Integer
  Range("A2").Select
    i = 1 'set as the first ID
    'check to see the next available blank row start at cell A2...
    Do Until ActiveCell.Value = Empty
        ActiveCell.Offset(1, 0).Select 'move down 1 row
        i = i + 1 'keep a count of the ID for later use
    Loop
    
    'Populate the new data values into the 'Scan Here' worksheet.
    ActiveCell.Value = i 'Next ID number
    ActiveCell.Offset(0, 1).Value = Me.UPCNum.Value 'set col B
    ActiveCell.Offset(0, 2).Value = Me.ItemNum.Value 'set col C
    ActiveCell.Offset(0, 16).Value = Me.LabelQTY.Value 'set col Q
    ActiveCell.Offset(0, 14).Value = OptionButton1.Value 'Set col O
    ActiveCell.Offset(0, 15).Value = OptionButton2.Value 'Set col P
   
    'Clear down the values ready for the next record entry...
    Me.UPCNum.Value = Empty
    Me.ItemNum.Value = Empty
    Me.Description.Text = Empty
    Me.Quantity.Value = Empty
    Me.Price.Value = Empty
    Me.LabelQTY.Value = Empty
    Me.OptionButton1.Value = Empty
    Me.OptionButton2.Value = Empty
    'Me.ItemNum.SetFocus
    Me.LabelQTY.SetFocus 'positions the cursor for next record entry
       
   LabelCnt.Value = Range("A" & Rows.Count).End(xlUp).Value
   LastItem.Value = Range("C" & Rows.Count).End(xlUp).Value

 

[MickG]

I see from the code your activating "Sheet(4)" at the top of the code, if this is not the sheet that my code should runs on, you will have a problem.
What is the sheet name that my code is suppose to work on ???
It's very rarely that you need to activate the sheet you want the code to run on, its far better to refer to the sheet from within the code. Like so:-

With sheets("Sheet1")
Set rng = .Range(.Range("Q2"), .Range("Q" & Rows.Count).End(xlUp))
Ray = Application.Transpose(.Range(.Range("C2"), .Range("C" & Rows.Count).End(xlUp
End With

Mick

 

[berenger]

Sheet 4 is the sheet that it will be running on.

 

[MickG]

As my code is at the top of your code, so obviously runs first, you should be able to "Comment out" out all the other code and just try running my code.That would at leased eliminate any other problem.
To do this if you view the code in the VB Editer, then Select (with cursor)all the code that you don't want at the moment below mine and up to (but not inclucding) "End Sub", then at the far right hand end of the VB ToolBar you will see two icons with Short horizontal lines, one is Comment Out and the other is Uncomment. Click comment out.
Try "Commented" code on data.
Mick