Add entries from a template to a masterlist
Closed
Tami
-
Jun 11, 2015 at 09:00 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jun 26, 2015 at 07:35 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jun 26, 2015 at 07:35 AM
Related:
- Add entries from a template to a masterlist
- 2007 microsoft office add-in microsoft save as pdf or xps - Download - Other
- How to add @ in laptop - Guide
- How to add songs to sound picker - Guide
- A5 template word - Guide
- How to add a tick in word - Guide
14 responses
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Jun 12, 2015 at 03:04 AM
Jun 12, 2015 at 03:04 AM
Hello there Tami,
Your post is a little confusing. Do you want certain cells or rows or just columns of data transferred to a Master sheet?
It would be ideal if you could upload a sample of your work book (be careful with any sensitive data) so that we can assess exactly what you want to do and how. Someone should then be able to help you.
You can upload a sample by using a free file sharing site such as DropBox or ge.tt
Thank you!.
Cheerio,
vcoolio.
Your post is a little confusing. Do you want certain cells or rows or just columns of data transferred to a Master sheet?
It would be ideal if you could upload a sample of your work book (be careful with any sensitive data) so that we can assess exactly what you want to do and how. Someone should then be able to help you.
You can upload a sample by using a free file sharing site such as DropBox or ge.tt
Thank you!.
Cheerio,
vcoolio.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Jun 17, 2015 at 01:28 AM
Jun 17, 2015 at 01:28 AM
Hello Tami,
Its still a little hard without seeing your own work book because I don't know the dimensions of your data set but I believe that the following code will do the task for you (the code needs to be entered into a standard module):-
I have attached my test work book for you to peruse here:-
https://www.dropbox.com/s/fw4qbthyskv9gfc/Tami%28Score%20Card%20%26%20Master%29.xlsm?dl=0
Basically, you enter your data into the "Score Card" sheet and when you are done, click on the "Send to Master" button and the data will be transferred to the "Master" sheet. The code will clear the "Score Card" entries on transfer of the data ready for your next lot of entries. Hence, the Master list will grow after each transfer. Add some entries in the "Score Card" sheet and have a general play with the test work book.
As you can see from the test work book, I've used Columns A:G as an example. I don't know what your actual format is.
I hope that this helps.
Cheerio,
vcoolio.
Its still a little hard without seeing your own work book because I don't know the dimensions of your data set but I believe that the following code will do the task for you (the code needs to be entered into a standard module):-
Sub CopyIt()
Application.ScreenUpdating = False
Dim lRow As Long
lRow = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Score Card").Select
Range("A2:G" & lRow).Copy
Sheets("Master").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Sheets("Score Card").Range("A2:G" & lRow).ClearContents
Application.ScreenUpdating = True
Application.CutCopyMode = False
Sheets("Master").Select
End Sub
I have attached my test work book for you to peruse here:-
https://www.dropbox.com/s/fw4qbthyskv9gfc/Tami%28Score%20Card%20%26%20Master%29.xlsm?dl=0
Basically, you enter your data into the "Score Card" sheet and when you are done, click on the "Send to Master" button and the data will be transferred to the "Master" sheet. The code will clear the "Score Card" entries on transfer of the data ready for your next lot of entries. Hence, the Master list will grow after each transfer. Add some entries in the "Score Card" sheet and have a general play with the test work book.
As you can see from the test work book, I've used Columns A:G as an example. I don't know what your actual format is.
I hope that this helps.
Cheerio,
vcoolio.
Thanks a million vcoolio for all your help and time. I greatly appreciate it.
I have attached the spreadsheet that I am working on https://www.dropbox.com/s/8mz0uwvbk8njo2l/Scorecard%20Example.xlsm?dl=0
You are correct in your response above (btw you summarised it better than me). I would like to have all blue highlighted fields in the Scorecard to move to the MasterList under the appropriate headings (which are the same in the Scorecard) when submitted. Once the information has been submitted I would like the information in the blue cells and the orange cells to be cleared and the radio buttons to be all unchecked.
I hope I am a little clearer this time and apologise if I am not.
I hate to be painful but is it also possible that the same process is reversed? I have created a third sheet called Standby (which has the same fields as the blue cells of the Scorecard) but in this case I am using the Standby to record what has been received but not ready to be assessed therefore there may be multiple entries (ie multiple rows). When ready i would like to upload the information in each row into the Scorecard into the matching field so that the assessment can be completed and then submit into the MasterList. Once the row from the Standby sheet has been uploaded into the Scorecard, I would like the row to be cleared to avoid duplication. Ideally I would like to be able to select the next row to be uploaded as opposed to the next entry on the sheet (just in case one is more important or urgent the other) but either is fine - ie. next row or selected row.
Again I am very grateful for your assistance. A trillion thank you's to you.
Cheers and forever grateful to you
Tami
I have attached the spreadsheet that I am working on https://www.dropbox.com/s/8mz0uwvbk8njo2l/Scorecard%20Example.xlsm?dl=0
You are correct in your response above (btw you summarised it better than me). I would like to have all blue highlighted fields in the Scorecard to move to the MasterList under the appropriate headings (which are the same in the Scorecard) when submitted. Once the information has been submitted I would like the information in the blue cells and the orange cells to be cleared and the radio buttons to be all unchecked.
I hope I am a little clearer this time and apologise if I am not.
I hate to be painful but is it also possible that the same process is reversed? I have created a third sheet called Standby (which has the same fields as the blue cells of the Scorecard) but in this case I am using the Standby to record what has been received but not ready to be assessed therefore there may be multiple entries (ie multiple rows). When ready i would like to upload the information in each row into the Scorecard into the matching field so that the assessment can be completed and then submit into the MasterList. Once the row from the Standby sheet has been uploaded into the Scorecard, I would like the row to be cleared to avoid duplication. Ideally I would like to be able to select the next row to be uploaded as opposed to the next entry on the sheet (just in case one is more important or urgent the other) but either is fine - ie. next row or selected row.
Again I am very grateful for your assistance. A trillion thank you's to you.
Cheers and forever grateful to you
Tami
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Jun 18, 2015 at 02:04 AM
Jun 18, 2015 at 02:04 AM
Hello Tami,
Your work book is not quite what I had imagined! You can now probably understand the value of uploading a sample with the very first post.
Anyway, it would appear that:-
- You would rather start entries in the Standby sheet first and transfer rows of data, depending on their importance, to the Scorecard sheet for assessment.
- Then transfer these, once assessed, from the Scorecard sheet to the Master sheet (the Master sheet will be your permanent record, perhaps?).
- The rows of data in the Standby sheet need to be cleared on transfer to the Scorecard sheet and the data from the Scorecard sheet needs to be cleared once transferred to the Master sheet.
Have I got the process right?
This could take a while, so please be patient!
I'll be back.
Cheerio,
vcoolio.
Your work book is not quite what I had imagined! You can now probably understand the value of uploading a sample with the very first post.
Anyway, it would appear that:-
- You would rather start entries in the Standby sheet first and transfer rows of data, depending on their importance, to the Scorecard sheet for assessment.
- Then transfer these, once assessed, from the Scorecard sheet to the Master sheet (the Master sheet will be your permanent record, perhaps?).
- The rows of data in the Standby sheet need to be cleared on transfer to the Scorecard sheet and the data from the Scorecard sheet needs to be cleared once transferred to the Master sheet.
Have I got the process right?
This could take a while, so please be patient!
I'll be back.
Cheerio,
vcoolio.
Hi vcoolio
Thanks for your prompt response.
I would like to have the option of starting it in the standby or just keying straight into the scorecard - if possible. If it is too difficult then I am happy to have it as you described above.
Other than that, all other details in your response is correct.
I appreciate your assistance, and grateful for your time and expertise.
Cheers
Tami
Thanks for your prompt response.
I would like to have the option of starting it in the standby or just keying straight into the scorecard - if possible. If it is too difficult then I am happy to have it as you described above.
Other than that, all other details in your response is correct.
I appreciate your assistance, and grateful for your time and expertise.
Cheers
Tami
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Jun 19, 2015 at 06:59 AM
Jun 19, 2015 at 06:59 AM
Good evening Tami,
I think its all covered now:-
https://www.dropbox.com/s/w9ibde1bpmw9l24/Tami%282%29.xlsm?dl=0
You have both options now (your last post #6).
To transfer to the Master and reset the radio buttons, we have the following code:-
To transfer from the Standby sheet to the scorecard sheet, we have the following code:-
Place the codes in their own standard modules.
There won't be any duplication from Standby to Scorecard as each new entry into the Scorecard sheet replaces the previous which would have been transferred to the Master anyway. You can select whichever row of data you wish to transfer from the Standby sheet to the Scorecard sheet by placing the Supplier Name in the Input Box that pops up. It doesn't matter where in your data set the Supplier's name is.
I hope that all this helps.
Good luck!
Cheerio,
vcoolio.
I think its all covered now:-
https://www.dropbox.com/s/w9ibde1bpmw9l24/Tami%282%29.xlsm?dl=0
You have both options now (your last post #6).
To transfer to the Master and reset the radio buttons, we have the following code:-
Sub TransferData()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim ws1 As Worksheet
Set ws = Worksheets("Scorecard")
Set ws1 = Worksheets("MasterList")
nextrow = ThisWorkbook.Sheets("MasterList").Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Select
Range("B4").Copy
ws1.Range("A" & nextrow).PasteSpecial xlPasteValues
Range("B5").Copy
ws1.Range("B" & nextrow).PasteSpecial xlPasteValues
Range("B6").Copy
ws1.Range("C" & nextrow).PasteSpecial xlPasteValues
Range("B7").Copy
ws1.Range("D" & nextrow).PasteSpecial xlPasteValues
Range("B8").Copy
ws1.Range("E" & nextrow).PasteSpecial xlPasteValues
Range("B9").Copy
ws1.Range("F" & nextrow).PasteSpecial xlPasteValues
Range("F4").Copy
ws1.Range("G" & nextrow).PasteSpecial xlPasteValues
Range("F5").Copy
ws1.Range("H" & nextrow).PasteSpecial xlPasteValues
Range("F6").Copy
ws1.Range("I" & nextrow).PasteSpecial xlPasteValues
Range("F7").Copy
ws1.Range("J" & nextrow).PasteSpecial xlPasteValues
Range("F8").Copy
ws1.Range("K" & nextrow).PasteSpecial xlPasteValues
Range("F9").Copy
ws1.Range("L" & nextrow).PasteSpecial xlPasteValues
Range("J3").Copy
ws1.Range("M" & nextrow).PasteSpecial xlPasteValues
Range("J5").Copy
ws1.Range("N" & nextrow).PasteSpecial xlPasteValues
Range("J7").Copy
ws1.Range("O" & nextrow).PasteSpecial xlPasteValues
Range("J9").Copy
ws1.Range("P" & nextrow).PasteSpecial xlPasteValues
Range("B4:B9").ClearContents
Range("F4:F9").ClearContents
Range("J3").ClearContents
Range("J5").ClearContents
Range("J7").ClearContents
Range("J9").ClearContents
Range("J12:J34").ClearContents
ResetRadioButtons
Application.ScreenUpdating = True
Application.CutCopyMode = False
ws1.Select
End Sub
Sub ResetRadioButtons()
Dim sh As Shape
For Each sh In ActiveSheet.Shapes
If sh.Type = msoOLEControlObject Then
If TypeName(sh.OLEFormat.Object.Object) = "OptionButton" Then _
sh.OLEFormat.Object.Object.Value = False
End If
If sh.Type = msoFormControl Then
If sh.FormControlType = xlOptionButton Then sh.OLEFormat.Object.Value = False
End If
Next sh
End Sub
To transfer from the Standby sheet to the scorecard sheet, we have the following code:-
Sub StandbyToSc()
Application.ScreenUpdating = False
Dim lRow As Long
Dim SupplierName As String
Sheets("Standby").Select
lRow = Range("A" & Rows.Count).End(xlUp).Row
SupplierName = InputBox("Please enter the Supplier Name you wish to transfer.")
For Each cell In Range("B4:B" & lRow)
If cell = SupplierName Then
Range(Cells(cell.Row, "B"), Cells(cell.Row, "G")).Copy
Sheets("Scorecard").Range("B4").PasteSpecial xlPasteValues, Transpose:=True
If cell = SupplierName Then
Range(Cells(cell.Row, "H"), Cells(cell.Row, "M")).Copy
Sheets("Scorecard").Range("F4").PasteSpecial xlPasteValues, Transpose:=True
End If
End If
Next cell
Application.ScreenUpdating = True
Application.CutCopyMode = False
Sheets("Scorecard").Select
End Sub
Place the codes in their own standard modules.
There won't be any duplication from Standby to Scorecard as each new entry into the Scorecard sheet replaces the previous which would have been transferred to the Master anyway. You can select whichever row of data you wish to transfer from the Standby sheet to the Scorecard sheet by placing the Supplier Name in the Input Box that pops up. It doesn't matter where in your data set the Supplier's name is.
I hope that all this helps.
Good luck!
Cheerio,
vcoolio.
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
Jun 19, 2015 at 10:00 AM
Jun 19, 2015 at 10:00 AM
Hello Tami,
In the StandbyToSc code above, add this line of code directly after line 20:-
Apologies, I missed this part in your post #4 (and my post #5) about deleting the used rows of data from the Standby sheet.
I also had to unmerge Column F in the Scorecard sheet as merged cells create havoc with VBA codes. You may want to do this in your actual work book also as its a sure bet that you'll end up receiving a number of error messages.
Cheerio,
vcoolio.
In the StandbyToSc code above, add this line of code directly after line 20:-
Range(Cells(cell.Row, "B"), Cells(cell.Row, "M")).Delete
Apologies, I missed this part in your post #4 (and my post #5) about deleting the used rows of data from the Standby sheet.
I also had to unmerge Column F in the Scorecard sheet as merged cells create havoc with VBA codes. You may want to do this in your actual work book also as its a sure bet that you'll end up receiving a number of error messages.
Cheerio,
vcoolio.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Jun 20, 2015 at 02:09 AM
Jun 20, 2015 at 02:09 AM
Hello Tami,
Just another question.
In the Standby sheet, will you have the same Supplier name entered more than once perhaps?
Cheerio,
vcoolio.
Just another question.
In the Standby sheet, will you have the same Supplier name entered more than once perhaps?
Cheerio,
vcoolio.
Hi vcoolio
Wow!!! I dont know how to thank you - you are a genius.
Firstly to answer your question - it is possible to have the same supplier name in the Standby sheet. It could also have the same factory name and location as well. It might however have a different report number and date. I have proposed a change to the action on the button, please see point 4.
I have had a play around in the spreadsheet but, am having a couple of problems:
1) when transferring the data from Standby to the Scorecard, a message comes up to say "The operation will cause some merged cells to unmerge. Do you wish to continue?" - when I click OK, the formatting on the scorecard changes and the 'number of issues' and 'audit company' cells on the scorecard disappeared.
2) when I transfer the information from the scorecard to the masterlist (whether I had transferred the information from the Standby sheet or keyed into the scorecard manually), only the supplier name transfers to the masterlist.
3) once information is transferred from the standby to scorecard the entry is not deleting. The information on the scorecard remains I need to manually delete the information.
4) in the standby sheet you have to key the name as it appears (case sensitive) or it just returns blank entry. Can we make the field the row column and have the row column automatically renumber if a line disappears. Or it reads the number field and the number is manually entered. This is only because the name can be very long and with some names there can be mix of both lower case or upper case within the word.
SORRY - I have amended the Scorecard (Scorecard New sheet) . I'm so sorry but as the radio buttons were possibly the cause I changed it to a drop down box and changed the formatting around.
https://www.dropbox.com/s/wg02f587mhmiivm/Tami%283%29.xlsm?dl=0
Therefore I would be grateful if you can help me move all blue highlighted fields to the Masterlist and after transferred - can the data in the blue and orange cells delete.
Is it also possible to add a delete button on the scorecard which removes the blue highlighted and orange highlighted. Can I also add an email button on the scorecard sheet - that emails just the scorecard page to a specific person?
Is it possible to have a save to calendar (outlook) based on the follow-up audit date. Can the details of the scorecard just the top blue cells be included in the calendar appointment and/or the subject be "Follow-up: Supplier Name, Factory Name and Report Number"??
Many thanks for your assistance and your time. I promise no more changes.
You're a legend
Tami
Wow!!! I dont know how to thank you - you are a genius.
Firstly to answer your question - it is possible to have the same supplier name in the Standby sheet. It could also have the same factory name and location as well. It might however have a different report number and date. I have proposed a change to the action on the button, please see point 4.
I have had a play around in the spreadsheet but, am having a couple of problems:
1) when transferring the data from Standby to the Scorecard, a message comes up to say "The operation will cause some merged cells to unmerge. Do you wish to continue?" - when I click OK, the formatting on the scorecard changes and the 'number of issues' and 'audit company' cells on the scorecard disappeared.
2) when I transfer the information from the scorecard to the masterlist (whether I had transferred the information from the Standby sheet or keyed into the scorecard manually), only the supplier name transfers to the masterlist.
3) once information is transferred from the standby to scorecard the entry is not deleting. The information on the scorecard remains I need to manually delete the information.
4) in the standby sheet you have to key the name as it appears (case sensitive) or it just returns blank entry. Can we make the field the row column and have the row column automatically renumber if a line disappears. Or it reads the number field and the number is manually entered. This is only because the name can be very long and with some names there can be mix of both lower case or upper case within the word.
SORRY - I have amended the Scorecard (Scorecard New sheet) . I'm so sorry but as the radio buttons were possibly the cause I changed it to a drop down box and changed the formatting around.
https://www.dropbox.com/s/wg02f587mhmiivm/Tami%283%29.xlsm?dl=0
Therefore I would be grateful if you can help me move all blue highlighted fields to the Masterlist and after transferred - can the data in the blue and orange cells delete.
Is it also possible to add a delete button on the scorecard which removes the blue highlighted and orange highlighted. Can I also add an email button on the scorecard sheet - that emails just the scorecard page to a specific person?
Is it possible to have a save to calendar (outlook) based on the follow-up audit date. Can the details of the scorecard just the top blue cells be included in the calendar appointment and/or the subject be "Follow-up: Supplier Name, Factory Name and Report Number"??
Many thanks for your assistance and your time. I promise no more changes.
You're a legend
Tami
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Jun 21, 2015 at 09:33 AM
Jun 21, 2015 at 09:33 AM
Hello Tami,
Just had a quick look at your post. I'll go through it all properly tomorrow sometime.
How about we create a double click event? You can just double click on the Supplier name to transfer that particular row of data.
Hence my comment in post #8:-
You now have experienced how merged cells create havoc with codes.
I seriously recommend that you re-format everything without merged cells. The code will love you if you do!
Anyway, I'll be back.
Cheerio,
vcoolio.
Just had a quick look at your post. I'll go through it all properly tomorrow sometime.
Firstly to answer your question - it is possible to have the same supplier name in the Standby sheet. It could also have the same factory name and location as well. It might however have a different report number and date. I have proposed a change to the action on the button, please see point 4.
How about we create a double click event? You can just double click on the Supplier name to transfer that particular row of data.
1) when transferring the data from Standby to the Scorecard, a message comes up to say "The operation will cause some merged cells to unmerge. Do you wish to continue?" - when I click OK, the formatting on the scorecard changes and the 'number of issues' and 'audit company' cells on the scorecard disappeared.
Hence my comment in post #8:-
I also had to unmerge Column F in the Scorecard sheet as merged cells create havoc with VBA codes. You may want to do this in your actual work book also as its a sure bet that you'll end up receiving a number of error messages.
You now have experienced how merged cells create havoc with codes.
I seriously recommend that you re-format everything without merged cells. The code will love you if you do!
Anyway, I'll be back.
Cheerio,
vcoolio.
Hi vcoolio
Thank you for your prompt reply.
I went to add the line but it was already in the code. Maybe I stuffed up somewhere.
I have taken on your feedback and removed all merged fields. The formatting error still occurred. So, I changed the spreadsheet to remove the radio buttons and created a drop down box instead. I have placed a copy on dropbox.
https://www.dropbox.com/s/wg02f587mhmiivm/Tami%283%29.xlsm?dl=0 - please see Scorecard New. I no longer require Scorecard sheet, but kept it there for your reference.
With regards to your question on the supplier name - I am happy to do whatever is the easiest for you. Double clicking is absolutely fine.
As always, I really appreciate your assistance.
Cheers
Tami
Thank you for your prompt reply.
I went to add the line but it was already in the code. Maybe I stuffed up somewhere.
I have taken on your feedback and removed all merged fields. The formatting error still occurred. So, I changed the spreadsheet to remove the radio buttons and created a drop down box instead. I have placed a copy on dropbox.
https://www.dropbox.com/s/wg02f587mhmiivm/Tami%283%29.xlsm?dl=0 - please see Scorecard New. I no longer require Scorecard sheet, but kept it there for your reference.
With regards to your question on the supplier name - I am happy to do whatever is the easiest for you. Double clicking is absolutely fine.
As always, I really appreciate your assistance.
Cheers
Tami
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Jun 22, 2015 at 06:58 AM
Jun 22, 2015 at 06:58 AM
Good evening Tami,
Awww. You don't want the radio buttons anymore. :-(
Anyway, I think we have it now.
Based on your new work book:-
https://www.dropbox.com/s/fhaqo7x82rcee8g/Tami%283%29.xlsm?dl=0
- You can enter data into the "Scorecard New" sheet and when you are done with it, transfer the data to the "Master List" sheet via the "Send to Master" button.
- You can enter data into the "Standby" sheet and transfer it to the "Scorecard New" sheet (and in turn, transfer it to the "Master List" sheet as required). I have left the Input Box in place but this time just enter the relevant number from Column A to transfer the data. This will keep things on an individual basis.
- When entries are dealt with, they will be deleted from the source sheet (either the "Scorecard New" and "Standby" sheets). The numbers in Column A of the "Standby" sheet will always remain sequential even after an entry is transferred and deleted.
All of the above has worked smoothly for me in the above test work book so I assume it will for you also assuming that the above test work book is a replica of your actual work book.
Delete the old Scorecard sheet as it is no longer required.
Don't merge any cells, just re-format to suit your needs.
The amended codes are as follows:-
"Scorecard New" to "Master LIst":-
"Standby" to " Scorecard New":-
You'll see them in Modules 3 and 4.
If you don't want the colour formatting any more, just remove it by high-lighting the cells first, going to the Home tab--->Font group--->Fill bucket and select "No fill".
As for the e-mail button just use the inbuilt Excel mail function:-
- At the top of your sheet, above the tabs, you'll see a little down arrow icon. Click on this and select e-mail to activate the e-mail function.
- Immediately to the left of this icon, you see the attachment icon. When you click on this, it will bring up your e-mail dialogue area with the Subject and Attachment boxes already filled in for you. Just do your thing here.
Excel is a very powerful object and has many, many functions inbuilt ready to help any User, just with one mouse click. However, a lot of the time, most of these functions are ignored. They are designed to keep things simple.
Another simple little trick that I use in my own work books is to keep a list of regular e-mail recipients in a corner of the spread sheet somewhere. Excel automatically activates these (you'll see them change to a blue colour) and when you need to send an e-mail to a particular recipient, just click on that recipient's e-mail address and away you go.
I'm working on an array type code for the "Scorecard New" to "Master List" code above just in an attempt to trim it down a little. I'll see how it goes and let you know if its functional or not.
You can wake up now, Tami!
I hope that all this helps.
Cheerio,
vcoolio.
Awww. You don't want the radio buttons anymore. :-(
Anyway, I think we have it now.
Based on your new work book:-
https://www.dropbox.com/s/fhaqo7x82rcee8g/Tami%283%29.xlsm?dl=0
- You can enter data into the "Scorecard New" sheet and when you are done with it, transfer the data to the "Master List" sheet via the "Send to Master" button.
- You can enter data into the "Standby" sheet and transfer it to the "Scorecard New" sheet (and in turn, transfer it to the "Master List" sheet as required). I have left the Input Box in place but this time just enter the relevant number from Column A to transfer the data. This will keep things on an individual basis.
- When entries are dealt with, they will be deleted from the source sheet (either the "Scorecard New" and "Standby" sheets). The numbers in Column A of the "Standby" sheet will always remain sequential even after an entry is transferred and deleted.
All of the above has worked smoothly for me in the above test work book so I assume it will for you also assuming that the above test work book is a replica of your actual work book.
Delete the old Scorecard sheet as it is no longer required.
Don't merge any cells, just re-format to suit your needs.
The amended codes are as follows:-
"Scorecard New" to "Master LIst":-
Sub SendToMaster()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim ws1 As Worksheet
Set ws = Worksheets("Scorecard New")
Set ws1 = Worksheets("MasterList")
nextrow = ThisWorkbook.Sheets("MasterList").Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Select
Range("C5").Copy
ws1.Range("A" & nextrow).PasteSpecial xlPasteValues
Range("C6").Copy
ws1.Range("B" & nextrow).PasteSpecial xlPasteValues
Range("C7").Copy
ws1.Range("C" & nextrow).PasteSpecial xlPasteValues
Range("C8").Copy
ws1.Range("D" & nextrow).PasteSpecial xlPasteValues
Range("C9").Copy
ws1.Range("E" & nextrow).PasteSpecial xlPasteValues
Range("C10").Copy
ws1.Range("F" & nextrow).PasteSpecial xlPasteValues
Range("C13").Copy
ws1.Range("M" & nextrow).PasteSpecial xlPasteValues
Range("F5").Copy
ws1.Range("G" & nextrow).PasteSpecial xlPasteValues
Range("F6").Copy
ws1.Range("H" & nextrow).PasteSpecial xlPasteValues
Range("F7").Copy
ws1.Range("I" & nextrow).PasteSpecial xlPasteValues
Range("F8").Copy
ws1.Range("J" & nextrow).PasteSpecial xlPasteValues
Range("F9").Copy
ws1.Range("K" & nextrow).PasteSpecial xlPasteValues
Range("F10").Copy
ws1.Range("L" & nextrow).PasteSpecial xlPasteValues
Range("F13").Copy
ws1.Range("N" & nextrow).PasteSpecial xlPasteValues
Range("J1").Copy
ws1.Range("O" & nextrow).PasteSpecial xlPasteValues
Range("J2").Copy
ws1.Range("P" & nextrow).PasteSpecial xlPasteValues
Range("J3").Copy
ws1.Range("Q" & nextrow).PasteSpecial xlPasteValues
Range("D17").Copy
ws1.Range("R" & nextrow).PasteSpecial xlPasteValues
Range("D23").Copy
ws1.Range("S" & nextrow).PasteSpecial xlPasteValues
Range("D29").Copy
ws1.Range("T" & nextrow).PasteSpecial xlPasteValues
Range("D35").Copy
ws1.Range("U" & nextrow).PasteSpecial xlPasteValues
Range("D41").Copy
ws1.Range("V" & nextrow).PasteSpecial xlPasteValues
Range("D47").Copy
ws1.Range("W" & nextrow).PasteSpecial xlPasteValues
Range("D53").Copy
ws1.Range("X" & nextrow).PasteSpecial xlPasteValues
Range("D59").Copy
ws1.Range("Y" & nextrow).PasteSpecial xlPasteValues
Range("D65").Copy
ws1.Range("Z" & nextrow).PasteSpecial xlPasteValues
Range("D71").Copy
ws1.Range("AA" & nextrow).PasteSpecial xlPasteValues
Range("D77").Copy
ws1.Range("AB" & nextrow).PasteSpecial xlPasteValues
Range("D83").Copy
ws1.Range("AC" & nextrow).PasteSpecial xlPasteValues
Range("C5:C13").ClearContents
Range("F5:F13").ClearContents
Range("D17:F1000").ClearContents
Range("J5").ClearContents
Range("J7").ClearContents
Range("J9").ClearContents
Range("J12:J34").ClearContents
ws1.Columns.AutoFit
Application.ScreenUpdating = True
Application.CutCopyMode = False
ws1.Select
End Sub
"Standby" to " Scorecard New":-
Sub StandbyToSc()
Application.ScreenUpdating = False
Dim lRow As Long
Dim SupplierID As String
Sheets("Standby").Select
SupplierID = InputBox("Please enter the Supplier ID you wish to transfer.")
If SupplierID = vbNullString Then Exit Sub
lRow = Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Range("A4:A" & lRow)
If cell = SupplierID Then
Range(Cells(cell.Row, "B"), Cells(cell.Row, "G")).Copy
Sheets("Scorecard New").Range("C5").PasteSpecial xlPasteValues, Transpose:=True
If cell = SupplierID Then
Range(Cells(cell.Row, "H"), Cells(cell.Row, "M")).Copy
Sheets("Scorecard New").Range("F5").PasteSpecial xlPasteValues, Transpose:=True
Range(Cells(cell.Row, "B"), Cells(cell.Row, "M")).Delete
End If
End If
Next cell
Application.ScreenUpdating = True
Application.CutCopyMode = False
Sheets("Scorecard New").Select
End Sub
You'll see them in Modules 3 and 4.
Is it also possible to add a delete button on the scorecard which removes the blue highlighted and orange highlighted. Can I also add an email button on the scorecard sheet - that emails just the scorecard page to a specific person?
If you don't want the colour formatting any more, just remove it by high-lighting the cells first, going to the Home tab--->Font group--->Fill bucket and select "No fill".
As for the e-mail button just use the inbuilt Excel mail function:-
- At the top of your sheet, above the tabs, you'll see a little down arrow icon. Click on this and select e-mail to activate the e-mail function.
- Immediately to the left of this icon, you see the attachment icon. When you click on this, it will bring up your e-mail dialogue area with the Subject and Attachment boxes already filled in for you. Just do your thing here.
Excel is a very powerful object and has many, many functions inbuilt ready to help any User, just with one mouse click. However, a lot of the time, most of these functions are ignored. They are designed to keep things simple.
Another simple little trick that I use in my own work books is to keep a list of regular e-mail recipients in a corner of the spread sheet somewhere. Excel automatically activates these (you'll see them change to a blue colour) and when you need to send an e-mail to a particular recipient, just click on that recipient's e-mail address and away you go.
I'm working on an array type code for the "Scorecard New" to "Master List" code above just in an attempt to trim it down a little. I'll see how it goes and let you know if its functional or not.
You can wake up now, Tami!
I hope that all this helps.
Cheerio,
vcoolio.
Hi vcoolio
Sorry for my late response, I have been unwell.
Thank you very much for your assistance.
I'm not sure if it is me but when i transfer data from the Standby to the scorecard, line 12 gets distorted. When I then transfer the data from the Scorecard to the Masterlist only the Supplier Name and Factory Name comes across. Under no. of issues in the Masterlist it states Report No.
Sorry for being painful and I really appreciate your assistance.
cheers & many thanks
T
Sorry for my late response, I have been unwell.
Thank you very much for your assistance.
I'm not sure if it is me but when i transfer data from the Standby to the scorecard, line 12 gets distorted. When I then transfer the data from the Scorecard to the Masterlist only the Supplier Name and Factory Name comes across. Under no. of issues in the Masterlist it states Report No.
Sorry for being painful and I really appreciate your assistance.
cheers & many thanks
T
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
>
Tami
Jun 24, 2015 at 11:02 PM
Jun 24, 2015 at 11:02 PM
Hello Tami,
Is this just happening in your actual work book? The sample one above still works perfectly on my end.
I hope that you are feeling better now.
Cheerio,
vcoolio.
Is this just happening in your actual work book? The sample one above still works perfectly on my end.
I hope that you are feeling better now.
Cheerio,
vcoolio.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
>
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
Jun 24, 2015 at 11:08 PM
Jun 24, 2015 at 11:08 PM
BTW Tami,
In the "Scorecard New" sheet that you supplied, row 4 is blank. The details start on row 5.
Row 12 is also blank.
Cheerio,
vcoolio.
In the "Scorecard New" sheet that you supplied, row 4 is blank. The details start on row 5.
Row 12 is also blank.
Cheerio,
vcoolio.
Hi vcoolio
So sorry for the repeat messages, one last thing i promise (try to anyway).
Once the info is transferred from the scorecard to the masterlist, the information on the scorecard does not clear (name details, and rating information - all highlighted fields except those in the cells I1 to J3 as these auto calculate).
Cheers and many thanks in advance.
Cheers
Tami
So sorry for the repeat messages, one last thing i promise (try to anyway).
Once the info is transferred from the scorecard to the masterlist, the information on the scorecard does not clear (name details, and rating information - all highlighted fields except those in the cells I1 to J3 as these auto calculate).
Cheers and many thanks in advance.
Cheers
Tami
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Jun 24, 2015 at 11:38 PM
Jun 24, 2015 at 11:38 PM
Hello Tami,
As I asked above, are you referring to your actual work book?
The test work book at the last DropBox link above works perfectly and smoothly. No errors of any sort.
Cheerio,
vcoolio.
As I asked above, are you referring to your actual work book?
The test work book at the last DropBox link above works perfectly and smoothly. No errors of any sort.
Cheerio,
vcoolio.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Jun 25, 2015 at 12:23 AM
Jun 25, 2015 at 12:23 AM
Hello Tami,
Sorry, but I am totally lost!
Row 5 is where the Supplier's Name goes. Row 4 above is blank.
Row 5 stays as it should when data is transferred from the Standby sheet to the Scorecard New sheet.
Row 5 only clears when data is transferred to the Master List.
Row 13 stays perfectly fine when data is transferred from the Standby sheet to the Scorecard New sheet.
Again, all is working perfectly. Are you sure that you are not referencing the old Scorecard sheet? Have a close look at the set out of the Standby sheet in the last DropBox link compared to the one you supplied with the new Scorecard (following):-
https://www.dropbox.com/s/wg02f587mhmiivm/Tami%283%29.xlsm?dl=0
You'll see that you omitted the Report No. from the Master List sheet. You will also notice that I changed the order of the No. of Zero Tolerances, No. of High and No. of Issues in the Master List to match the ascending order of the titles in the Scorecard New sheet.
I really need to get back to work now, so have a play with the newest version of the work book:-
https://www.dropbox.com/s/fhaqo7x82rcee8g/Tami%283%29.xlsm?dl=0
and I'll have another look at it for you later this evening.
Cheerio,
vcoolio.
Sorry, but I am totally lost!
"Row 5 is blank - for looks purpose - but when i transfer data from standby to scorecard row 5 deletes. can I have it so that it doesnt."
Row 5 is where the Supplier's Name goes. Row 4 above is blank.
Row 5 stays as it should when data is transferred from the Standby sheet to the Scorecard New sheet.
Row 5 only clears when data is transferred to the Master List.
Row 13 stays perfectly fine when data is transferred from the Standby sheet to the Scorecard New sheet.
"I just tried completing the scorecard and transferring to the masterlist and only the supplier name and factory name are coming across. No. of issues is bringing over Report no. and info on scorecard does not delete. Can i have a button on the scorecard that clears the data - all highlighted fields except those in the cells I1 to J3 as these auto calculate."
Again, all is working perfectly. Are you sure that you are not referencing the old Scorecard sheet? Have a close look at the set out of the Standby sheet in the last DropBox link compared to the one you supplied with the new Scorecard (following):-
https://www.dropbox.com/s/wg02f587mhmiivm/Tami%283%29.xlsm?dl=0
You'll see that you omitted the Report No. from the Master List sheet. You will also notice that I changed the order of the No. of Zero Tolerances, No. of High and No. of Issues in the Master List to match the ascending order of the titles in the Scorecard New sheet.
I really need to get back to work now, so have a play with the newest version of the work book:-
https://www.dropbox.com/s/fhaqo7x82rcee8g/Tami%283%29.xlsm?dl=0
and I'll have another look at it for you later this evening.
Cheerio,
vcoolio.
Hi vcoolio
Sorry for the confusion and being a pain.
To make it easier, I have attached what i see when using the workbook.
https://www.dropbox.com/s/tcwc42555e1w1eh/Tami%283%29%20-%20AA.xlsm?dl=0
https://www.dropbox.com/s/dlv8qgajvpz86ut/Tami%283%29%20-%20BB.xlsm?dl=0
The second file shows distortion when transferring a second entry from the Standby to the Scorecard.
I have deleted the old scorecard just to be sure.
Appreciate your assistance.
Have fun at work.
Cheers and many many thanks
Tami
Sorry for the confusion and being a pain.
To make it easier, I have attached what i see when using the workbook.
https://www.dropbox.com/s/tcwc42555e1w1eh/Tami%283%29%20-%20AA.xlsm?dl=0
https://www.dropbox.com/s/dlv8qgajvpz86ut/Tami%283%29%20-%20BB.xlsm?dl=0
The second file shows distortion when transferring a second entry from the Standby to the Scorecard.
I have deleted the old scorecard just to be sure.
Appreciate your assistance.
Have fun at work.
Cheers and many many thanks
Tami
Hi vcoolio
I think I have gotten the worksheet to work. I am forever grateful to you.
If you could help me with one last thing, I would greatly appreciate it.
I've created a separate button to delete entries (rows) in the standby sheet, the code I have used is
Sub Delete()
Dim RowNum As Long
RowNum = InputBox("Enter the EXCEL ROW you wish to delete")
Range(Cells(RowNum, 2), Cells(RowNum, 15)).Delete Shift:=xlUp
End Sub
Instead of me keying in the excel row, I would like to key in the supplier ID (similar to the send to scorecard button). Are you able to assist me in improving this code?
Many thanks
Tami
I think I have gotten the worksheet to work. I am forever grateful to you.
If you could help me with one last thing, I would greatly appreciate it.
I've created a separate button to delete entries (rows) in the standby sheet, the code I have used is
Sub Delete()
Dim RowNum As Long
RowNum = InputBox("Enter the EXCEL ROW you wish to delete")
Range(Cells(RowNum, 2), Cells(RowNum, 15)).Delete Shift:=xlUp
End Sub
Instead of me keying in the excel row, I would like to key in the supplier ID (similar to the send to scorecard button). Are you able to assist me in improving this code?
Many thanks
Tami
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Jun 25, 2015 at 06:02 AM
Jun 25, 2015 at 06:02 AM
Hello Tami,
Well, I'm still at a total loss!
The two samples you supplied above work perfectly on my machine! I have downloaded them to four different locations on my machine and they worked nicely from all locations. I believe that you are using Excel 2013 whereas I'm using 2010 but this should not make any difference.
The distortion you mention above may just be a formatting issue on your machine. There is no distortion at all on my end. I noticed that you have squeezed some rows together so, for any formatting changes, make sure that you save any changes before you do anything else.
In the two above samples, you have the titles beginning on Row 4 in the Scorecard sheet yet in the new sample that you supplied (with the "Scorecard New" sheet) the titles begin on Row 5.
If you have access to a different PC, try downloading to that and see if it makes any difference. If not, make a copy of your actual work book, remove any other codes that you may have in the work book (but not formulae), install the codes that I have supplied to you and see what happens from there.
Let me know what transpires.
Cheerio,
vcoolio.
Well, I'm still at a total loss!
The two samples you supplied above work perfectly on my machine! I have downloaded them to four different locations on my machine and they worked nicely from all locations. I believe that you are using Excel 2013 whereas I'm using 2010 but this should not make any difference.
The distortion you mention above may just be a formatting issue on your machine. There is no distortion at all on my end. I noticed that you have squeezed some rows together so, for any formatting changes, make sure that you save any changes before you do anything else.
In the two above samples, you have the titles beginning on Row 4 in the Scorecard sheet yet in the new sample that you supplied (with the "Scorecard New" sheet) the titles begin on Row 5.
If you have access to a different PC, try downloading to that and see if it makes any difference. If not, make a copy of your actual work book, remove any other codes that you may have in the work book (but not formulae), install the codes that I have supplied to you and see what happens from there.
Let me know what transpires.
Cheerio,
vcoolio.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Jun 25, 2015 at 06:15 AM
Jun 25, 2015 at 06:15 AM
Hello again Tami,
Just an update.
I just downloaded the sample to my wife's laptop. No issues. All is working nicely on the laptop also.
The code is obviously not at fault, so I am stumped!
Cheerio,
vcoolio.
Just an update.
I just downloaded the sample to my wife's laptop. No issues. All is working nicely on the laptop also.
The code is obviously not at fault, so I am stumped!
Cheerio,
vcoolio.
Hi vcoolio
I just tried the file on my mac computer and it works perfect. Im not sure why there is a difference....Im lost for words.
I redownloaded the file from dropbox and tried it again - and the issues noted above still appear.
The laptop I am using is the laptop I use for work and is the one it needs to work on - as my colleagues have the same notebook.
Im puzzled.
I have developed a work around, and would appreciate your assistance in improving the following code so that it deletes by supplier id and not excel row number:
I've created a separate button to delete entries (rows) in the standby sheet, the code I have used is
Sub Delete()
Dim RowNum As Long
RowNum = InputBox("Enter the EXCEL ROW you wish to delete")
Range(Cells(RowNum, 2), Cells(RowNum, 15)).Delete Shift:=xlUp
End Sub
Many thanks
Tami
I just tried the file on my mac computer and it works perfect. Im not sure why there is a difference....Im lost for words.
I redownloaded the file from dropbox and tried it again - and the issues noted above still appear.
The laptop I am using is the laptop I use for work and is the one it needs to work on - as my colleagues have the same notebook.
Im puzzled.
I have developed a work around, and would appreciate your assistance in improving the following code so that it deletes by supplier id and not excel row number:
I've created a separate button to delete entries (rows) in the standby sheet, the code I have used is
Sub Delete()
Dim RowNum As Long
RowNum = InputBox("Enter the EXCEL ROW you wish to delete")
Range(Cells(RowNum, 2), Cells(RowNum, 15)).Delete Shift:=xlUp
End Sub
Many thanks
Tami
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Jun 25, 2015 at 10:37 AM
Jun 25, 2015 at 10:37 AM
Hello Tami,
Looks like we are both scratching our heads and I'm running out of hair!!
Seems like it could be a settings issue. There are a lot of Excel Gurus out there (and I'm not one of them!) who use Excel 2013 and don't have any problems. So, it could be worth you checking some settings:-
Click on the File tab in your work sheet (top left) then select Options. Next, select Trust Centre and then click on the Trust Centre settings button. On the left hand side of the dialogue box that pops up, you will see a list of categories. Go through each one, but mainly:-
- Macro Settings
- File Block Settings
- Protected View
- External Content
- Trusted Documents
and check that all settings allow you access to basically everything. All default settings should allow you to do whatever you need to do.
I've been told that also which is probably why I stayed with 2010. I believe that there is a new version coming out later this year. Bye bye hair!!
You shouldn't need to do that as the code already has the delete line in it which works:-
You may actually create new issues by adding code that does the same task.
But, if you must:-
This will also delete the Supplier ID because when a row is deleted, Excel automatically moves rows up which means that the next Supplier on your list will have the wrong ID.
Anyway, time for bed now.
Cheerio,
vcoolio.
Looks like we are both scratching our heads and I'm running out of hair!!
Seems like it could be a settings issue. There are a lot of Excel Gurus out there (and I'm not one of them!) who use Excel 2013 and don't have any problems. So, it could be worth you checking some settings:-
Click on the File tab in your work sheet (top left) then select Options. Next, select Trust Centre and then click on the Trust Centre settings button. On the left hand side of the dialogue box that pops up, you will see a list of categories. Go through each one, but mainly:-
- Macro Settings
- File Block Settings
- Protected View
- External Content
- Trusted Documents
and check that all settings allow you access to basically everything. All default settings should allow you to do whatever you need to do.
"I've been told that Excel 2013 is not macro friendly and not all macros work properly in 2013."
I've been told that also which is probably why I stayed with 2010. I believe that there is a new version coming out later this year. Bye bye hair!!
Sub Delete()
Dim RowNum As Long
RowNum = InputBox("Enter the EXCEL ROW you wish to delete")
Range(Cells(RowNum, 2), Cells(RowNum, 15)).Delete Shift:=xlUp
End Sub
You shouldn't need to do that as the code already has the delete line in it which works:-
Range(Cells(cell.Row, "B"), Cells(cell.Row, "M")).Delete
You may actually create new issues by adding code that does the same task.
But, if you must:-
Sub DeleteIt()
Dim SupplierID As String
SupplierID = InputBox("Enter the Supplier ID you wish to delete")
Range("A1:A" & Range("A" & Rows.Count).End(3).Row).AutoFilter 1, SupplierID
Range("A1:A" & Range("A" & Rows.Count).End(3).Row).Offset(1).EntireRow.Delete
ActiveSheet.AutoFilterMode = False
End Sub
This will also delete the Supplier ID because when a row is deleted, Excel automatically moves rows up which means that the next Supplier on your list will have the wrong ID.
Anyway, time for bed now.
Cheerio,
vcoolio.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Jun 26, 2015 at 07:35 AM
Jun 26, 2015 at 07:35 AM
Hello Tami,
Good luck is all I can say now! Its still all working nicely on this end.
Cheerio,
vcoolio.
Good luck is all I can say now! Its still all working nicely on this end.
Cheerio,
vcoolio.
Jun 16, 2015 at 08:15 AM
Sorry for any confusion.
I want entries in a template to add to a masterlist. The template will be completed for each entry but instead of saving each template I want the entries to go to a masterlist. The masterlist will keep growing with every entry in the template.
I am fine with the masterlist being in the same spreadsheet but each template (called scorecard) i complete needs to be able to be refreshed removing the entries in the sheet but recorded in the masterlist.
I hope this makes sense. Similar to what you would see in access database, where you fill out a form and it places the entries into a spreadsheet.
Sorry I dont know how to upload the file.
Many thanks for your assistance
Cheers
Tami