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
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.
