Hello,
I am trying to transfer a row of data from one sheet to another in the same workbook but my code is not working could anyone help me
thanks Jess
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest As Range
Set rngDest = Worksheets("Worksheet 1").Range("rngDest")
' Limit the trap area to range of cells in which completed dates are entered as defined above
If Not Intersect(Target, Range("rngTrigger")) Is Nothing Then
' Only trigger if the value entred is a date or is recognizable as a valid date
If IsDate(Target) Then
'Ensure subsequent deletion of 'moved' row does NOT cause the Change Event to run again and get itself in a loop!
Application.EnableEvents = False
Target.EntireRow.Select
Selection.Cut
rngDest.Insert Shift:=xlDown
Selection.Delete
' Reset EnableEvents
Application.EnableEvents = True
End If
End If
Upload a sample of your work book (be careful with any sensitive data) to a free file sharing site such as DropBox, ge.tt or SpeedyShare and then post the link to your file back here.
We'll then have a rummage around it and sort it out for you.
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
If Not Application.Intersect(Target, Range("H:H")) Is Nothing Then
If IsDate(Target) Then
Range(Cells(Target.Row, "A"), Cells(Target.Row, lCol)).Copy
Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteAll
Target.EntireRow.Delete
End If
End If
Sheet2.Columns.AutoFit
Sheet2.Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
I've made the following assumptions as to what you are attempting to do:-
- Sheet1 is your "input" sheet where you enter your daily data.
- When an order is completed you then enter the completion date into the relevant cell in Column H.
- When the date is entered in Column H, you would like the entire relevant row of data immediately transferred to sheet2 as a type of archive.
- You would like the same row of data to be then cleared from sheet1.
The above code should then do the task for you. It is a Worksheet Change event which means that the code will execute as soon as something happens in Column H. In this case, only dates will work in Column H. Any other values that may be inadvertantly placed in Column H will just show a blank result in sheet2.
Just to explain the code a little for you, mainly lines 11 - 14:-
If the target value is a date (the target being all cells in Column H), it will copy the row of the target cell from Column A to the last used column (lCol) which in this case is Column H and transfer it to sheet2 into the next available row. The target row is then deleted from sheet1.
Using lCol instead of a fixed column reference will give you some flexibility should your data ever need to expand further out to the right. Column H can remain as your "trigger" column. You won't need to adjust the code (unless you change the "trigger" column, Column H).
Following is the link to my test work book, based on the sample that you supplied, for you to peruse:-
Enter a date in any cell in Column H and click away (or press enter or down arrow) and the relevant row of data will be transferred to sheet2. The code will take you directly to sheet2 on each transfer. Should this become annoying (and it just might!) just remove line 19 from the code.
In the test work book, I have removed the sheet which you had named "Codes Used".
To implement the code into your work book:-
- Righ click on the sheet1 tab.
- Select "view code" from the menu that appears.
- In the big white field that then appears, paste the above code.
- Go back to your Input sheet (sheet1) and test it out.
It would be a good idea to test the code in a copy of your work book first.
If you have any more questions, just come back anytime.
Hi Vcoolio
Sorry to bother you again but can ew put a 20 second delay in or be able to stay on sheet 1 until i enter everything on the same line every time I make an entry and hit tab to go from column a to column b the code takes me to sheet 2 I go back to sheet 1 enter in column c and I go to sheet two any way for it not to change until I put in Complete Date I really can't use it the way it is now after I started enter data
The simplest way is to remove line 19 from the code, but a more correct method would be to:-
1) Replace line 9 with the following:-
If Intersect(Target, Columns("H:H")) Is Nothing Then Exit Sub
2) Add the following line just above it:-
If Target.Value = vbNullString then Exit Sub
3) Remove one of the End IF statements.
Another method would be to place basically the same code in a standard module and assign the code to a button. Absolutely nothing will then happen until you click on the button. Using a button will allow you to enter as much data as you like without interruption. Data will only transfer when you click on the button.
Sub TransferData()
Application.ScreenUpdating = False
Dim lr As Long
Dim lCol As Long
Dim cell As Range
lr = Range("H" & Rows.Count).End(xlUp).Row
lCol = Cells(1, Columns.Count).End(xlToLeft).Column
For Each cell In Range("H2:H" & lr)
If IsDate(cell.Value) Then
Range(Cells(cell.Row, "A"), Cells(cell.Row, lCol)).Copy
Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteAll
'Range(Cells(cell.Row, "A"), Cells(cell.Row, lCol)).Delete
End If
Next
Sheet2.Columns.AutoFit
Sheet2.Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
As you can see, the above code is slightly different than the other one. It is a "normal" sub routine and needs to be placed in a standard module. To implement the code into your work book:-
- Press Alt + F11 which will take you to the Visual Basic Editor.
- Up the top, select Insert and then select Module from the menu that appears.
- In the big white field that then appears, paste the above code.
Go back to your main sheet (sheet1), create a button somewhere on the sheet. Right click on the button and select "assign macro" from the menu that appears. In the Assign Macro window that appears, select the macro name in the larger field (in this case its named "TransferData") then click OK. Done!
Following is the link to the updated test work book for you to play with:-
In case you were wondering, I usually create my own button as follows:-
- In your main sheet, select the Insert tab up the top and, in the Illustrations Group, select Shapes.
- A window will appear showing many different shapes. I usually select a rectangular one.
- When you have selected a shape, your cursor will turn into a "cross-hair" type cursor. Pick a spot on your sheet and draw the shape with the cursor.
- When you have drawn the shape, you'll notice that a Format tab will light up which will give you many options on how you would like your button to look. Click on the down arrow in the Shape Styles Group and have fun!
Have a play with this function in a blank sheet first just to get used to what you can do or to see how creative you can be. The above steps may seem somewhat drawn out but it actually only takes a few seconds to create a button once you have the routine under control.
When you are satisfied, assign the macro to the button as mentioned further up this post.
You could also use the inbuilt function to create a button by going to the Developer tab, selecting Insert in the Controls group and then selecting the Form Control button to which you can assign the macro to.
As mentioned in my post#9, you'll be able to input data undisturbed until you are ready to transfer data to sheet2 by clicking on the button.
One more thing. If you look at line #15 in the above code (in green font), you'll notice an apostrophe at the begining of the line. If you wish to delete data from sheet1 once it has been transferred to sheet2, then just remove the apostrophe.
I hope that this sorts it all out for you. Let us know how it all works out for you.
Any issues that you are having are directly related to the fact that you are using a Mac and not a PC. If you have access to a PC, then try the code on that. You'll see that it works just fine. The code at the DropBox link and in my personal file works just as it should.
I have absolutely no experience with Macs but am aware of the continuing compatibilty issues that Excel on a Mac experiences.
With every change of version of Excel( 2000 to 2003, 2010 to 2013 and now to 2016) there are always changes made to the vba coding methods. On Macs, this is an even a bigger issue as they have added and reduced the functionalities over and over again. Much of the code that works on Office for Windows does not work on Macs as the functionality of the Mac OS and the Windows OS are completely different. Basically, the compatibility issues provide a large resource for errors. Example: A path separator in Windows is "\" and ":" for Macs. So, you can see that the simplest of differences between the two systems will give you untold heart-ache.
I cannot guide you any further as my experience with Macs is virtually non-existent and I would just be leading you "up the garden path". You may need to have your IT Department (should you work with one) work through this with you (or change to a PC!!??!!).
Also, just referring back to your post#6, its odd that it worked OK then but now it has all fallen in a heap! Could you not go back to that code with the modifications I suggested in post#9?
Sorry I couldn't sort the last bit out for you. If you eventually do, please post back and let us know the result. Any insight into these type of compatibility issues will be a bonus for anyone who drops in here over time.
some wrong i am using a del notebook and having the same problem of data moving to sheet 2 and stays on sheet 1 I have downloaded the sample and it does the same thing may be I messed up the code somehow if it is working for you could you send me the code again and I will paste it in and work with it on the notebook that should fix the problem thanks
Jess123!
If you go back to the code in post#12, you'll see that line 15 is in green font. At the beginning of the line, you'll see an apostrophe. Remove the apostrophe and all will work as it should. If you have already pasted the code into your work book, make sure that the apostrophe has been removed.
If you mean that you just want to use or fill in more columns to the right of your current last column, then you don't need to do anything. If you intend to change the current criteria column (Column H), then you'll need to change the column reference in line 11 of the code (post#12).
From my post#5 above:-
Using lCol instead of a fixed column reference will give you some flexibility should your data ever need to expand further out to the right. Column H can remain as your "trigger" column. You won't need to adjust the code (unless you change the "trigger" column, Column H).
It didn't work I am lost I do not know what to do
Thanks Jess123!