Related:
- Transfer a row of data from one sheet to another same workbook
- Auto. copy a row of data onto another sheet using conditions ✓ - Forum - Excel
- Automatically transfer data from one sheet to another in excel - How-To - Excel
- Transfer Data Between Excel Spreadsheets - How-To - Excel
- How to automatically transfer & save data from one sheet to another in excel ✓ - Forum - Excel
- Excel - Transfer data between several sheets - How-To - Excel
20 replies
vcoolio
- Posts
- 1269
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- January 4, 2021
Hello Jess,
See if you can adapt the code in the following thread to suit your needs:-
https://ccm.net/forum/affich-897790-auto-update-data-between-sheets
You'll obviously only need to use it in one sheet (your Input sheet).
Let us know how it works out for you.
Cheerio,
vcoolio.
See if you can adapt the code in the following thread to suit your needs:-
https://ccm.net/forum/affich-897790-auto-update-data-between-sheets
You'll obviously only need to use it in one sheet (your Input sheet).
Let us know how it works out for you.
Cheerio,
vcoolio.
vcoolio
- Posts
- 1269
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- January 4, 2021
Hello Jess,
Its ok. Don't panic!
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.
Cheerio,
vcoolio.
Its ok. Don't panic!
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.
Cheerio,
vcoolio.
Jess123!
- Posts
- 7
- Registration date
- Wednesday May 18, 2016
- Status
- Member
- Last seen
- May 27, 2016
Hello vcoolio
Here is the Link
http://speedy.sh/qTzrJ/order-location1.xls
And thank again for all the help I am receiving it is greatly appreciated
Thanks
Jess123!
Here is the Link
http://speedy.sh/qTzrJ/order-location1.xls
And thank again for all the help I am receiving it is greatly appreciated
Thanks
Jess123!
vcoolio
- Posts
- 1269
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- January 4, 2021
Hello Jess,
Here we go:-
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:-
https://www.dropbox.com/s/6huvrw8lbjpfl6c
/Jess%28Worksheet_Change%20event%29.xlsm?dl=0
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.
I hope that this helps.
Cheerio,
vcoolio.
Here we go:-
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:-
https://www.dropbox.com/s/6huvrw8lbjpfl6c
/Jess%28Worksheet_Change%20event%29.xlsm?dl=0
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.
I hope that this helps.
Cheerio,
vcoolio.
vcoolio
- Posts
- 1269
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- January 4, 2021
Hello Jess,
You're welcome. I'm glad that I was able to help you.
Cheerio,
vcoolio.
You're welcome. I'm glad that I was able to help you.
Cheerio,
vcoolio.
Jess123!
- Posts
- 7
- Registration date
- Wednesday May 18, 2016
- Status
- Member
- Last seen
- May 27, 2016
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
Thanks for all your help
Jess123!
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
Thanks for all your help
Jess123!
vcoolio
- Posts
- 1269
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- January 4, 2021
Hello Jess,
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:-
2) Add the following line just above it:-
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.
Let me know what you decide.
Cheerio,
vcoolio.
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.
Let me know what you decide.
Cheerio,
vcoolio.
vcoolio
- Posts
- 1269
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- January 4, 2021
Hey Jess,
Here is the link to the updated test file showing how it all works using steps 1, 2 & 3 from above.
https://www.dropbox.com/s/6huvrw8lbjpfl6c/Jess%28Worksheet_Change%20event%29.xlsm?dl=0
Cheerio,
vcoolio
Here is the link to the updated test file showing how it all works using steps 1, 2 & 3 from above.
https://www.dropbox.com/s/6huvrw8lbjpfl6c/Jess%28Worksheet_Change%20event%29.xlsm?dl=0
Cheerio,
vcoolio
Jess123!
Hi vcoolio
I think the button would be the better idea if it is not to much trouble
Thanks,
Jess123!
I think the button would be the better idea if it is not to much trouble
Thanks,
Jess123!
vcoolio
- Posts
- 1269
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- January 4, 2021
Hi Jess,
Here we go:-
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:-
https://www.dropbox.com/s/bw8f0ccbt18cma8/Jess%282%29.xlsm?dl=0
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.
Cheerio,
vcoolio.
Here we go:-
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:-
https://www.dropbox.com/s/bw8f0ccbt18cma8/Jess%282%29.xlsm?dl=0
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.
Cheerio,
vcoolio.
Jess123!
Hey Vcoolio
when I try the sample it moves to the next page but stays on the first too
Jess123!
when I try the sample it moves to the next page but stays on the first too
Jess123!
vcoolio
- Posts
- 1269
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- January 4, 2021
Hello Jess,
Do you mean that the data is being transferred to sheet 2 but your view stays on sheet1? If so, check that you haven't removed line 20 from the code.
The sample at the DropBox link works just fine with the view shifting to sheet2 when the data is transferred. The same with my personal sample file.
Check again and let us know.
Cheerio,
vcoolio.
Do you mean that the data is being transferred to sheet 2 but your view stays on sheet1? If so, check that you haven't removed line 20 from the code.
The sample at the DropBox link works just fine with the view shifting to sheet2 when the data is transferred. The same with my personal sample file.
Check again and let us know.
Cheerio,
vcoolio.
vcoolio
- Posts
- 1269
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- January 4, 2021
Hello Jess,
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?
My apologies Jess.
Cheerio,
vcoolio.
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?
My apologies Jess.
Cheerio,
vcoolio.
vcoolio
- Posts
- 1269
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- January 4, 2021
Hi Jess,
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.
Cheerio,
vcoolio.
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.
Cheerio,
vcoolio.
Jess123!
- Posts
- 7
- Registration date
- Wednesday May 18, 2016
- Status
- Member
- Last seen
- May 27, 2016
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!
Jess123!
vcoolio
- Posts
- 1269
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- January 4, 2021
Hello Jess,
Are you using the code from post #12 above (the one which is assigned to the button)?
Do you mean that the data is being transferred to sheet 2 but the view stays on sheet 1?
or
Do you mean that the data is being transferred to sheet2 but not being deleted from sheet 1 once transferred?
Cheerio,
vcoolio.
Are you using the code from post #12 above (the one which is assigned to the button)?
Do you mean that the data is being transferred to sheet 2 but the view stays on sheet 1?
or
Do you mean that the data is being transferred to sheet2 but not being deleted from sheet 1 once transferred?
Cheerio,
vcoolio.
vcoolio
- Posts
- 1269
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- January 4, 2021
Hello Jess,
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.
Let us know if this has sorted it for you.
Cheerio.
vcoolio.
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.
Let us know if this has sorted it for you.
Cheerio.
vcoolio.
vcoolio
- Posts
- 1269
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- January 4, 2021
Hi Jess,
That's excellent! Nothing like a temperamental Mac!
No, you haven't been a pain. As volunteers here, we do what we can to help.
So, have a good day (or night) and all the best.
Cheerio,
vcoolio.
That's excellent! Nothing like a temperamental Mac!
No, you haven't been a pain. As volunteers here, we do what we can to help.
So, have a good day (or night) and all the best.
Cheerio,
vcoolio.
Jess123!
- Posts
- 7
- Registration date
- Wednesday May 18, 2016
- Status
- Member
- Last seen
- May 27, 2016
Hi vcoolio
How hard would it be add columns to the spreadsheet what code do I have to change
Thanks,
Jess123!
How hard would it be add columns to the spreadsheet what code do I have to change
Thanks,
Jess123!
vcoolio
- Posts
- 1269
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- January 4, 2021
Hi Jess,
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:-
I hope that this answers your query.
Cheerio,
vcoolio.
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).
I hope that this answers your query.
Cheerio,
vcoolio.
Subscribe To Our Newsletter!
It didn't work I am lost I do not know what to do
Thanks Jess123!