2 replies
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
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
- Posts
- 1206
- Registration date
- Thursday July 24, 2014
- Status
Hello Excel934,
A WorkSheet_Change event code, as follows, may do the task for you:-
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.
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.