Copy certain number of rows using VBA

Excel934 2 Posts Sunday July 15, 2018Registration date July 15, 2018 Last seen - Jul 15, 2018 at 03:09 PM - Latest reply: vcoolio 1200 Posts Thursday July 24, 2014Registration dateModeratorStatus October 12, 2018 Last seen
- Jul 15, 2018 at 10:01 PM
Hello,

Hello I am trying to copy a certain number of rows from sheet 1 to sheet 2 this number is dependent on a value i have set in sheet 3 A1.
The number of rows to be copied is supposed to start from the top. how can i write that in vba

See more 

Your reply

2 replies

Excel934 2 Posts Sunday July 15, 2018Registration date July 15, 2018 Last seen - Jul 15, 2018 at 03:24 PM
0
Thank you
Hello again,

Can you please help me with the following:

I am trying yo copy a certain number of rows from sheet 1 to sheet 2 using VBA. This number is dependent on a value I have set in Sheet 3 A 1.

The number of rows to be copied is supposed to start from the Top.

I am very new to VBA and would really appreciate assistance in writing this in VBA.

Please let me know if it is not clear.

Thank you
Respond to Excel934
vcoolio 1200 Posts Thursday July 24, 2014Registration dateModeratorStatus October 12, 2018 Last seen - Jul 15, 2018 at 10:01 PM
0
Thank you
Hello Excel934,

A WorkSheet_Change event code, as follows, may do the task for you:-


Private Sub Worksheet_Activate()
MsgBox "Please enter into the SEARCH box the number of rows of data to be transferred.", vbExclamation, "WARNING!"
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = "SEARCH" Then Exit Sub

        Sheet1.Rows(2 & ":" & Target.Value + 1).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
        Sheet1.Rows(2 & ":" & Target.Value + 1).Delete
        Target.Value = "SEARCH"

Application.CutCopyMode = False
Application.ScreenUpdating = True

Sheet2.Select

End Sub


There are actually two codes above. The WorkSheet_Activate code just brings up a message box asking the user to enter the number of rows of data to be transferred. This can be removed if you don't require it.

The WorkSheet_Change code does the business for you. Once you enter a numeric value in the search box in cell A1 in Sheet3, the code will take that many rows from Sheet1 and transfer the data to Sheet2. The same amount of rows are then deleted from Sheet1.
As you requested, the rows of data to be transferred will always start from the top (in this case from row2 as I have assumed that you have headings in row1).

So enter a value in cell A1, Sheet3 then press enter and the code will execute.

To implement the code:_

- Right click on the Sheet3 tab.
- Select "View Code" from the menu that appears.
- In the big white field that then appears, paste the above code.

Please test the code in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.
Respond to vcoolio