Macro needed
Closed
nadge1974
Posts
1
Registration date
Thursday April 7, 2016
Status
Member
Last seen
April 7, 2016
-
Apr 7, 2016 at 10:22 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Apr 8, 2016 at 03:32 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Apr 8, 2016 at 03:32 AM
Related:
- Macro needed
- Spell number in excel without macro - Guide
- Macro excel download - Download - Spreadsheets
- Automatically run a macro when opening a worksheet - Guide
- Excel macro to create new sheet based on value in cells - Guide
- Run macro when cell value changes - Guide
1 response
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Apr 8, 2016 at 03:32 AM
Apr 8, 2016 at 03:32 AM
Hello Nadge1974,
The following code may be what you are after:-
When you enter a value in a cell in Column D and click away (or press enter or down arrow), the relevant row of data will be transferred to sheet 2. The "used" row of data in sheet 1 will be deleted.
The code is a Worksheet_Change event and will need to be placed in the sheet1 module (or the Void Master sheet module in your case). To do this, right click on the sheet1 tab, select "view code" from the menu that appears then paste the above code in the big white field that appears. Go back to your work sheet, enter a value in any cell in column D and click away and the row of data will be transferred to sheet2.
On each transfer, the code will take you directly to sheet2. If this becomes a little annoying (and I'm sure it will), then just remove line 20 of the above code.
Following is the link to my test work book for you to peruse:-
https://www.dropbox.com/s/9talcy8tx4fwkzo/Nadge1974.xlsm?dl=0
I hope that this helps.
Cheerio,
vcoolio.
The following code may be what you are after:-
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim lCol As Long
lCol = Cells(1, Columns.Count).End(xlToLeft).Column
If Target.Count > 1 Then Exit Sub ' this stops code error if more than one cell is changed at once
If Not Application.Intersect(Target, Range("D:D")) Is Nothing Then
If Target <> vbNullString Then
Range(Cells(Target.Row, "A"), Cells(Target.Row, lCol)).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
Target.EntireRow.Delete
End If
End If
Sheet2.Columns.AutoFit
Sheet2.Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
When you enter a value in a cell in Column D and click away (or press enter or down arrow), the relevant row of data will be transferred to sheet 2. The "used" row of data in sheet 1 will be deleted.
The code is a Worksheet_Change event and will need to be placed in the sheet1 module (or the Void Master sheet module in your case). To do this, right click on the sheet1 tab, select "view code" from the menu that appears then paste the above code in the big white field that appears. Go back to your work sheet, enter a value in any cell in column D and click away and the row of data will be transferred to sheet2.
On each transfer, the code will take you directly to sheet2. If this becomes a little annoying (and I'm sure it will), then just remove line 20 of the above code.
Following is the link to my test work book for you to peruse:-
https://www.dropbox.com/s/9talcy8tx4fwkzo/Nadge1974.xlsm?dl=0
I hope that this helps.
Cheerio,
vcoolio.
