how to select rows depending on a cell value using VBA

[dpmg1991]

I am trying to copy and paste rows from one sheet (fill) of the workbook to another (BD). The rows I need to copy depend on the value of a cell. If such cell =3, I need 3 rows to be copied. If the cell =4 I need 4 rows copied to the other.
Is there a way of doing this with VBA?

 

[mrshl9898]

How does this suit?

Sub copyrows()


Dim rownum As Long


rownum = Sheets("fill").Range("A1").Value  'cell containing the number of rows to copy


Sheets("BD").Rows("1:" & rownum).Copy Sheets("fill").Rows("1:1" & rownum)


End Sub

 

[JLGWhiz]

You can use event code to have it automatically do the copy when you enter the number in a cell. Assume that the entry cell is on Sheet 'BD', cell B2 and the copy will begin on row three and be pasted to the next available row on Sheet 'fill'

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        Rows(3).Resize(Target.Value).Copy Sheets("fill").Cells(Rows.Count, 1).End(xlUp)(2)
    End If
Application.EnableEvents = True
End Sub

The code should be copied to the worksheet code module of the sheet to be copied from that has the cell specifying the number or rows to copy.

 

[DanteAmor]

Try this. Change the data in red with your data

Sub Copy_Rows()
    Dim wRows As Variant
    Dim ws1  As Worksheet, ws2 As Worksheet
    Dim ini As Double
    
    Set ws1 = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")  'sourde sheet
    Set ws2 = Sheets("[COLOR=#ff0000]Sheet2[/COLOR]")  'target sheet
    ini = [COLOR=#ff0000]4[/COLOR]                     'initial row to copy
    wRows = ws1.Range("[COLOR=#ff0000]A3[/COLOR]").Value   'cell withrows number to copy
    
    If wRows = "" Or Not IsNumeric(wRows) Then
        MsgBox "Enter rows number to copy"
        Exit Sub
    End If
    
    ws1.Rows(ini & ":" & ini + wRows).Copy ws2.Range("[COLOR=#ff0000]A2[/COLOR]")  'target cell
    
    MsgBox "Rows copied"
End Sub

 

[dpmg1991]

I think I didnt explained myself...

I have two worksheets: Fill and BD
On the Fill worksheet I am filling information. Cell A3 is the sum of the rows that have values on them.
I want to copy only the number of rows that A3 has. So if A3 = 10, i want to select rows from A4 to A14 and paste them on the nex available row in the BD sheet.

 

[JLGWhiz]

OK, then maybe you want to use a button instead of event code. You can attach this to a button.

Sub t()    
Sheets("fill").Rows(4).Resize(Range("A3").Value).Copy Sheets("BD").Cells(Rows.Count, 1).End(xlUp)(2)   
End Sub

BTW, your example in Post #5 would copy 11 rows, not 10.