Enter Data into main worksheet and have it update separate WS
Closed
jamishore
Posts
9
Registration date
Wednesday August 23, 2017
Status
Member
Last seen
September 13, 2017
-
Updated on Aug 23, 2017 at 04:25 PM
jamishore Posts 9 Registration date Wednesday August 23, 2017 Status Member Last seen September 13, 2017 - Sep 7, 2017 at 10:42 AM
jamishore Posts 9 Registration date Wednesday August 23, 2017 Status Member Last seen September 13, 2017 - Sep 7, 2017 at 10:42 AM
Related:
- Enter Data into main worksheet and have it update separate WS
- Transfer data from one excel worksheet to another automatically - Guide
- How to enter @ in laptop - Guide
- How do i update my facebook account to new version and design - Guide
- Play store update - Guide
- Ps3 update - Guide
7 responses
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Aug 24, 2017 at 01:16 AM
Aug 24, 2017 at 01:16 AM
Hello Jamishore,
Could you upload a sample of your work book please. It would be easier for us to determine how to best help you solve this if we had a sample to test with.
You can upload a sample to a free file sharing site such as ge.tt or Drop Box then post the link to your file back here. Please use dummy data and clearly explain inputs and expected results.
Thanks Jami.
Cheerio,
vcoolio.
Could you upload a sample of your work book please. It would be easier for us to determine how to best help you solve this if we had a sample to test with.
You can upload a sample to a free file sharing site such as ge.tt or Drop Box then post the link to your file back here. Please use dummy data and clearly explain inputs and expected results.
Thanks Jami.
Cheerio,
vcoolio.
jamishore
Posts
9
Registration date
Wednesday August 23, 2017
Status
Member
Last seen
September 13, 2017
Aug 24, 2017 at 02:53 PM
Aug 24, 2017 at 02:53 PM
I have uploaded my dummy file here:
https://www.dropbox.com/s/oc3gpqhglht6lty/2017%20JCA%20Report.xlsm?dl=0
What I would like to do is update the "2017 Job Cost Analysis" worksheet with jobs throughout the year and have it automatically update the Project Type worksheet, with the entire row for each project as it is added. I have a Project Type assigned to each in column A.
Also, I only want it to add new information, not duplicate (which it is doing now).
As you can see, for some reason, the project total section is not pulling over for the first 14 rows and I am sure it's because of my summary part that I have listed on the "2017 Job Cost Analysis" worksheet.
Any assistance is greatly appreciated!
https://www.dropbox.com/s/oc3gpqhglht6lty/2017%20JCA%20Report.xlsm?dl=0
What I would like to do is update the "2017 Job Cost Analysis" worksheet with jobs throughout the year and have it automatically update the Project Type worksheet, with the entire row for each project as it is added. I have a Project Type assigned to each in column A.
Also, I only want it to add new information, not duplicate (which it is doing now).
As you can see, for some reason, the project total section is not pulling over for the first 14 rows and I am sure it's because of my summary part that I have listed on the "2017 Job Cost Analysis" worksheet.
Any assistance is greatly appreciated!
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Aug 25, 2017 at 09:40 AM
Aug 25, 2017 at 09:40 AM
Hello Jami,
Perhaps all you need is a Worksheet_Change event as follows:_
This type of code needs to be placed into the worksheet module. So, to implement the code:-
- Right click on the 2017 Job Cost Analysis tab.
- Select "view code" from the menu that appears.
- Paste the above code in the big white field that then appears.
Go back to the main worksheet.
Place all your data in the required cells but make your last entry from the drop down selections that you have in Column A. As soon as you select a value from the drop down, the relevant row of data will be transferred to its respective sheet. Only values will be transferred over.
Following is the link to your file with the code implemented:-
http://ge.tt/9OEvKIm2
Make a selection from the drop downs in Column A to have the relevant row of data transferred to its individual sheet.
I hope that this helps.
Cheerio,
vcoolio.
Perhaps all you need is a Worksheet_Change event as follows:_
Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Application.EnableEvents = False If Intersect(Target, Columns(1)) Is Nothing Then Exit Sub Target.EntireRow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues Sheets(Target.Value).Columns.AutoFit Application.CutCopyMode = False Application.EnableEvents = True Application.ScreenUpdating = True End Sub
This type of code needs to be placed into the worksheet module. So, to implement the code:-
- Right click on the 2017 Job Cost Analysis tab.
- Select "view code" from the menu that appears.
- Paste the above code in the big white field that then appears.
Go back to the main worksheet.
Place all your data in the required cells but make your last entry from the drop down selections that you have in Column A. As soon as you select a value from the drop down, the relevant row of data will be transferred to its respective sheet. Only values will be transferred over.
Following is the link to your file with the code implemented:-
http://ge.tt/9OEvKIm2
Make a selection from the drop downs in Column A to have the relevant row of data transferred to its individual sheet.
I hope that this helps.
Cheerio,
vcoolio.
jamishore
Posts
9
Registration date
Wednesday August 23, 2017
Status
Member
Last seen
September 13, 2017
Aug 25, 2017 at 11:05 AM
Aug 25, 2017 at 11:05 AM
Thank you for looking into this. It seems kind of buggy because it is only working sometimes, and I came across another issue. If you need to delete or change something on the 2017 Job Cost Analysis Tab, it doesn't update the corresponding worksheet. So if I accidentally chose, DAS in column A, but meant to choose Microwave, it will update the DAS worksheet and will not undo it.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Aug 26, 2017 at 05:24 AM
Aug 26, 2017 at 05:24 AM
Hello Jami,
Buggy? Are you receiving any error messages? If so, which one(s)?
Working sometimes? I've just run the code (in the sample you supplied) over a dozen times repeatedly and it works seamlessly. Is your actual workbook a little different to the sample you supplied?
A few more questions:-
- Is the Range(A14:A39) from which the copying/pasting is done fixed? Based on the Project Type Summary at the top of the sheet, this is how I understand the data set to be formatted.
- Are the Project Types just the six that you have listed or will there be more?
- If they are fixed at six types, will the only cells to be changed be from the non-formulated columns in the column range B:AF?
- If they are fixed at six types, do you want the data that is transferred to the individual sheets to overwrite that which already exists in the individual sheets? This would mean that you would only ever have one row of data in the individual sheets. Or, do you need new data to append to the bottom of the existing data in the individual sheets.
I'll await your reply before going any further.
Cheerio,
vcoolio.
It seems kind of buggy because it is only working sometimes
Buggy? Are you receiving any error messages? If so, which one(s)?
Working sometimes? I've just run the code (in the sample you supplied) over a dozen times repeatedly and it works seamlessly. Is your actual workbook a little different to the sample you supplied?
A few more questions:-
- Is the Range(A14:A39) from which the copying/pasting is done fixed? Based on the Project Type Summary at the top of the sheet, this is how I understand the data set to be formatted.
- Are the Project Types just the six that you have listed or will there be more?
- If they are fixed at six types, will the only cells to be changed be from the non-formulated columns in the column range B:AF?
- If they are fixed at six types, do you want the data that is transferred to the individual sheets to overwrite that which already exists in the individual sheets? This would mean that you would only ever have one row of data in the individual sheets. Or, do you need new data to append to the bottom of the existing data in the individual sheets.
I'll await your reply before going any further.
Cheerio,
vcoolio.
jamishore
Posts
9
Registration date
Wednesday August 23, 2017
Status
Member
Last seen
September 13, 2017
Aug 28, 2017 at 03:50 PM
Aug 28, 2017 at 03:50 PM
Buggy? Are you receiving any error messages? If so, which one(s)? No error messages, just entered info and it didn't always bring it over to the correct worksheet. See the attached. I chose the project type last and nothing copied over.
Working sometimes? I've just run the code (in the sample you supplied) over a dozen times repeatedly and it works seamlessly. Is your actual workbook a little different to the sample you supplied? I updated the sample to include more formatting. The columns E; F; I; J; M; N; Q; R; U; V; Y' Z' AC; AD are all locked cells with a formula to calculate the 2 columns before each.
A few more questions:-
- Is the Range(A14:A39) from which the copying/pasting is done fixed? No it is not fixed from what I can tell. Based on the Project Type Summary at the top of the sheet, this is how I understand the data set to be formatted. The Project Type Summary is what I created the drop down list from.
- Are the Project Types just the six that you have listed or will there be more? There may be more.
- If they are fixed at six types, will the only cells to be changed be from the non-formulated columns in the column range B:AF? We really only need to bring over everything in that row besides the formulated columns listed above.
- If they are fixed at six types, do you want the data that is transferred to the individual sheets to overwrite that which already exists in the individual sheets? This would mean that you would only ever have one row of data in the individual sheets. Or, do you need new data to append to the bottom of the existing data in the individual sheets I would like it to append the data.
FYI on the worksheet, it is not password protected.
https://www.dropbox.com/s/35qr8603x1qswai/2017%20JCA%20Report.xlsm?dl=0
Working sometimes? I've just run the code (in the sample you supplied) over a dozen times repeatedly and it works seamlessly. Is your actual workbook a little different to the sample you supplied? I updated the sample to include more formatting. The columns E; F; I; J; M; N; Q; R; U; V; Y' Z' AC; AD are all locked cells with a formula to calculate the 2 columns before each.
A few more questions:-
- Is the Range(A14:A39) from which the copying/pasting is done fixed? No it is not fixed from what I can tell. Based on the Project Type Summary at the top of the sheet, this is how I understand the data set to be formatted. The Project Type Summary is what I created the drop down list from.
- Are the Project Types just the six that you have listed or will there be more? There may be more.
- If they are fixed at six types, will the only cells to be changed be from the non-formulated columns in the column range B:AF? We really only need to bring over everything in that row besides the formulated columns listed above.
- If they are fixed at six types, do you want the data that is transferred to the individual sheets to overwrite that which already exists in the individual sheets? This would mean that you would only ever have one row of data in the individual sheets. Or, do you need new data to append to the bottom of the existing data in the individual sheets I would like it to append the data.
FYI on the worksheet, it is not password protected.
https://www.dropbox.com/s/35qr8603x1qswai/2017%20JCA%20Report.xlsm?dl=0
Didn't find the answer you are looking for?
Ask a question
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Aug 29, 2017 at 08:24 AM
Aug 29, 2017 at 08:24 AM
Hello Jami,
I've not tested the following as I'm short on time tonight, but, seeing that there are only certain columns to be populated in the individual sheets, try:-
You will need to unprotect the main sheet and convert the "Microwave" sheet back to a normal range----> Go to the Design tab at the top of the sheet, over in the Tools group select "Convert to Range" (but remember to first click anywhere in the "Microwave" sheet data set).
Also, remove the existing code that is presently in the main sheet module.
As there are a mass of formulae in all sheets, the code will probably take approx. 4 - 5 seconds to execute (even though the code turns off automatic calculation which is turned on again at the end of the code).
Cheerio,
vcoolio.
I've not tested the following as I'm short on time tonight, but, seeing that there are only certain columns to be populated in the individual sheets, try:-
Sub TransferData() Dim MySheet As String Dim c As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual On Error Resume Next For Each c In Sheet2.Range("A14:A39") MySheet = c.Value Range(Cells(c.Row, "A"), Cells(c.Row, "D")).Copy Sheets(MySheet).Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues Range(Cells(c.Row, "G"), Cells(c.Row, "H")).Copy Sheets(MySheet).Range("G" & Rows.Count).End(3)(2).PasteSpecial xlValues Range(Cells(c.Row, "K"), Cells(c.Row, "L")).Copy Sheets(MySheet).Range("K" & Rows.Count).End(3)(2).PasteSpecial xlValues Range(Cells(c.Row, "O"), Cells(c.Row, "P")).Copy Sheets(MySheet).Range("O" & Rows.Count).End(3)(2).PasteSpecial xlValues Range(Cells(c.Row, "S"), Cells(c.Row, "T")).Copy Sheets(MySheet).Range("S" & Rows.Count).End(3)(2).PasteSpecial xlValues Range(Cells(c.Row, "W"), Cells(c.Row, "X")).Copy Sheets(MySheet).Range("W" & Rows.Count).End(3)(2).PasteSpecial xlValues Range(Cells(c.Row, "AA"), Cells(c.Row, "AB")).Copy Sheets(MySheet).Range("AA" & Rows.Count).End(3)(2).PasteSpecial xlValues Range(Cells(c.Row, "AE"), Cells(c.Row, "AF")).Copy Sheets(MySheet).Range("AE" & Rows.Count).End(3)(2).PasteSpecial xlValues Sheets(MySheet).Columns.AutoFit Next c Application.Calculation = xlCalculationAutomatic Application.CutCopyMode = False Application.ScreenUpdating = True Sheet2.Select MsgBox "Done!", vbExclamation End Sub
You will need to unprotect the main sheet and convert the "Microwave" sheet back to a normal range----> Go to the Design tab at the top of the sheet, over in the Tools group select "Convert to Range" (but remember to first click anywhere in the "Microwave" sheet data set).
Also, remove the existing code that is presently in the main sheet module.
As there are a mass of formulae in all sheets, the code will probably take approx. 4 - 5 seconds to execute (even though the code turns off automatic calculation which is turned on again at the end of the code).
Cheerio,
vcoolio.
jamishore
Posts
9
Registration date
Wednesday August 23, 2017
Status
Member
Last seen
September 13, 2017
Aug 29, 2017 at 01:57 PM
Aug 29, 2017 at 01:57 PM
So to confirm, I would have to leave the main sheet unprotected?
Also, it does work, but I have to "Run" it each time and it pulls over the same data. So it will duplicate all data each time I "Run" it.
https://www.dropbox.com/s/5h3jy337pkaqzil/2017%20JCA%20Report.xlsm?dl=0
And I want to say I appreciate all your assistance on this.
Also, it does work, but I have to "Run" it each time and it pulls over the same data. So it will duplicate all data each time I "Run" it.
https://www.dropbox.com/s/5h3jy337pkaqzil/2017%20JCA%20Report.xlsm?dl=0
And I want to say I appreciate all your assistance on this.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Aug 30, 2017 at 05:35 AM
Aug 30, 2017 at 05:35 AM
Hello Jami,
Yes. For any code to work on any sheet, it must be unprotected.
However, to overcome the tedium of unprotecting/protecting the sheet each time you wish to use it, you can place the following line of code:-
at line 8 in the code above
and the following line of code:-
at line 38 in the code above.
If you don't have a password then just remove:-
Password:="Whatever your password is"
from both lines.
BTW, you have placed the code in the Worksheet module. Remove it from there and place it in a standard module and assign the code to a button.
The code is duplicating the data in the sample as that is the only data you have in the main sheet at present (nothing changed). I assume that you will be updating/changing the inputs daily. Duplicates can be removed if you find that static data is not changed regularly.
I hope that this helps.
Cheerio,
vcoolio.
Yes. For any code to work on any sheet, it must be unprotected.
However, to overcome the tedium of unprotecting/protecting the sheet each time you wish to use it, you can place the following line of code:-
Sheet2.Unprotect Password:="Whatever your password is"
at line 8 in the code above
and the following line of code:-
Sheet2.Protect Password:="Whatever your password is"
at line 38 in the code above.
If you don't have a password then just remove:-
Password:="Whatever your password is"
from both lines.
BTW, you have placed the code in the Worksheet module. Remove it from there and place it in a standard module and assign the code to a button.
The code is duplicating the data in the sample as that is the only data you have in the main sheet at present (nothing changed). I assume that you will be updating/changing the inputs daily. Duplicates can be removed if you find that static data is not changed regularly.
I hope that this helps.
Cheerio,
vcoolio.
jamishore
Posts
9
Registration date
Wednesday August 23, 2017
Status
Member
Last seen
September 13, 2017
Sep 7, 2017 at 10:42 AM
Sep 7, 2017 at 10:42 AM
I just wanted to get back to you and let you know that I am still playing around this workbook.
Thank you very much for your input it has been greatly appreciated.
Thank you very much for your input it has been greatly appreciated.