Copy certain number of rows using VBA
Closed
Excel934
Posts
2
Registration date
Sunday July 15, 2018
Status
Member
Last seen
July 15, 2018
-
Jul 15, 2018 at 03:09 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jul 15, 2018 at 10:01 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jul 15, 2018 at 10:01 PM
Related:
- Copy certain number of rows using VBA
- Vba case like - Guide
- Number to words in excel formula without vba - Guide
- Vba check if value is in array - Guide
- Vba color index - Guide
- How to open vba in excel - Guide
2 responses
Excel934
Posts
2
Registration date
Sunday July 15, 2018
Status
Member
Last seen
July 15, 2018
Jul 15, 2018 at 03:24 PM
Jul 15, 2018 at 03:24 PM
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
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Jul 15, 2018 at 10:01 PM
Jul 15, 2018 at 10:01 PM
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.
