Copy paste and fill down.

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Copy paste and fill down.

Post by adam »

Hi anyone,

The last used range in my sheet's column E is row 12. I want the text in txtTime to get copied to the last empty row of column E and the the copied text to fill down to the number of rows mentioned in textbox txtRow.

Let's say for example the txtTime has the text "One" and the textbox txtRow has 5. I want the code to copy the text "One" 5 times to column E filling down to five rows from row 13.

I hope I've made my question clear. Any help would be kindly appreciated.

Code: Select all

Private Sub CommandButton1_Click()
    
    Dim n           As Long
    Dim ws          As Worksheet
    
    Set ws = Worksheets("AP")
    
    n = ws.Range("E" & Application.Rows.Count).End(xlUp).Row
    
    ws.Range("E" & n + 1).Value = Me.txtOne.Value
    
    Cells(Rows.Count, "E").End(xlUp).Resize(11).FillDown
    
End Sub
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy paste and fill down.

Post by HansV »

You already have code. What is the problem with it?
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Copy paste and fill down.

Post by adam »

The code at present is set to fill down the copied text to 10 rows.

I want the code to fill down to number of rows mentioned in the textbox txtRow.

I want to change the following line for the above purpose

Cells(Rows.Count, "E").End(xlUp).Resize(11).FillDown
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy paste and fill down.

Post by HansV »

You can replace 11 in Resize(11)
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Copy paste and fill down.

Post by adam »

Did you mean
Cells(Rows.Count, "E").End(xlUp).Resize(txt.Row).FillDown
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy paste and fill down.

Post by HansV »

Not txt.Row but txtRow. I'd try it and check the result carefully.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Copy paste and fill down.

Post by adam »

Here's my final code. This code does work fine if the user writes more than 1 rows to be filled in txtOne text box.

But if the user writes 1 in the txtOne textbox, it copies the heading in row 11 down to one row instead of copying the text in the label.

How could I avoid this?

Any help on this would be kindly appreciated.

Code: Select all

Private Sub txtOne_AfterUpdate()
Dim n           As Long
Dim ws          As Worksheet
    
    Set ws = Worksheets("mysheet")
    
    n = ws.Range("E" & Application.Rows.Count).End(xlUp).Row
    
    ws.Range("E" & n + 1).Value = Label1.Caption
    
    Cells(Rows.Count, "E").End(xlUp).Resize(txtOne).FillDown
End Sub
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy paste and fill down.

Post by HansV »

Perhaps

Code: Select all

Private Sub txtOne_AfterUpdate()
    Dim n           As Long
    Dim ws          As Worksheet
    
    Set ws = Worksheets("mysheet")
    
    n = ws.Range("E" & Application.Rows.Count).End(xlUp).Row
    
    ws.Range("E" & n + 1).Value = Label1.Caption
    
    If txtOne > 1 Then
        Cells(Rows.Count, "E").End(xlUp).Resize(txtOne).FillDown
    End If
End Sub
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Copy paste and fill down.

Post by adam »

Thanks for the help Hans. It worked fine.
Best Regards,
Adam