Add entries from a template to a masterlist [Closed]

Report
-
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
-
Hello,

I have a excel template scorecard which I would like the answers from certain cells to record entries from the scorecard to a separate excel spreadsheet which will be the masterlist of all scorecards completed.

Therefore I need the masterlist to keep adding entries based on the completion of scorecard.

Ideally I would like the masterlist to tell me or able to filter by clicking on a cell of contents on the same sheet on the masterlist. Put simply I would like a column in a sheet which contains the entries from the scorecard to list and filter by a particular column (as the entries will be limited to 10 possible entries). I dont want to use the excel filter.

I hope this makes sense and someone is able to assist me.

Many thanks in advance.

Tami

14 replies

Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230
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.
Thanks vcoolio for you response.

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
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230
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):-


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
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230
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.
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
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230
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:-

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.
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230
Hello Tami,

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.
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230
Hello Tami,

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
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230
Hello Tami,

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
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230
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":-

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.
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230 > Tami
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.
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230 >
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021

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.
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
Hi vcoolio

this is happening in the copy i downloaded from the dropbox.

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.

Sorry i got the wrong row, i meant row 13 distorts when info is transferred from standby to scorecard - and not row 12.

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.

Im not sure why it is working for you and not me.

Sorry for being so annoying and difficult.

Im feeling a lot better....damn weather took its toll on me.

Cheers
T
Hi vcoolio

I have been using the workbook in the dropbox. I have made no changes to it.

I downloaded it from the dropbox and tested it using the data you had. Made no changes.

Cheers

Tami
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230
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.
Hi vcoolio

I have been using the workbook in the dropbox. I have made no changes to it.

I downloaded it from the dropbox and tested it using the data you had - i then keyed in sample data and submitted the form. Made no changes.

Cheers

Tami
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230
Hello Tami,

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
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
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230
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.
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230
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.
thanks vcoolio.

I will try on another laptop and see what happens. It is a little strange that it works on your system but not on mine.

I will let you know how I go.

Thanks again for all your help. Your a gem.

Cheers

Tami
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
Hi vcoolio

Ive been told that Excel 2013 is not macro friendly and not all macros work properly in 2013.

Im sorry to have wasted all your good work.
By the way I am still using your codes but it doesnt transfer from standby to scorecard

Cheers

T
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230
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 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.
Hi vcoolio,

thanks for your prompt response. I am puzzled about why it doesn't work in 2013 but will try what u have suggested.

Thanks a million for all your help....i am forever grateful.

I will let you know how i go with the settings check.

Cheers

Tami
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230
Hello Tami,

Good luck is all I can say now! Its still all working nicely on this end.

Cheerio,
vcoolio.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!