Hold Tag Auto Populate

[Kuro Kuma22]

Hello Everyone,

I'm creating a Hold Log for the company I work for and I want to make it fairly simply for our quality techs to fill out the information needed to put things on Hold. There are two tabs on the spreadsheet. One tab is fairly basic, they input the data of the Hold/Rework. This tab is used to input any and all relevant information:

The second tab is the Hold/Rework Tag itself:

My goal here is to have them enter all of the information for the Hold on the first tab and then on the second tab all they have to do is enter the Hold Tag Number and it auto populates all of the information in all of the other sections. How would I code this?

 

[Sektor]

Well, you need VBA code to move data from one sheet to another

 

[Kuro Kuma22]

Well, you need VBA code to move data from one sheet to another

This is correct. Sadly I'm oblivious to coding anything in VBA. I've been very dependent in the past on the good people of MrExcel to help me code and understand what's needed.

 

[Rick Rothstein]

This is correct. Sadly I'm oblivious to coding anything in VBA. I've been very dependent in the past on the good people of MrExcel to help me code and understand what's needed.

I would be happy to try and help you, but unfortunately I cannot read anything from the picture you posted for the first tab. Can you post a copy of your actual workbook to a file sharing service like DropBox and post the link they give you for your file here? That way we can see all the headers and their location for the first tab and also have the actual physical layout for the second tab as well... with those actual worksheets in hand, developing the VBA code for you will be much easier.

 

[Kuro Kuma22]

I would be happy to try and help you, but unfortunately I cannot read anything from the picture you posted for the first tab. Can you post a copy of your actual workbook to a file sharing service like DropBox and post the link they give you for your file here? That way we can see all the headers and their location for the first tab and also have the actual physical layout for the second tab as well... with those actual worksheets in hand, developing the VBA code for you will be much easier.

Yes absolutely, my apologies for the low quality images. Below is the link to the dropbox file for the document:

https://www.dropbox.com/s/x7nwesj1o6e8w4v/Hold Book.xlsx?dl=0

Thank you for the feedback!

 

[Rick Rothstein]

Yes absolutely, my apologies for the low quality images. Below is the link to the dropbox file for the document:

https://www.dropbox.com/s/x7nwesj1o6e8w4v/Hold Book.xlsx?dl=0

Assuming you don't move any of the fields on either the "Hold Info" or "Hold Tag" sheets (if you do, you will mess everything up because you used merged cells), this event code should do what you want. Simply enter the tag number into the "Hold Number" field and the rest of the fields should populate.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim RowNum As Long
  With Sheets("Hold Info")
    If Target.Address = "$C$1" Then
      RowNum = Application.Match(Target.Value, .Columns("A"))
      Range("I1:K1").Value = .Cells(RowNum, "B").Value
      Range("C7:L8").Value = .Cells(RowNum, "C").Value
      Range("C9:H9").Value = .Cells(RowNum, "D").Value
      Range("K9").Value = .Cells(RowNum, "E").Value
      Range("L9").Value = .Cells(RowNum, "F").Value
      Range("C10:H10").Value = .Cells(RowNum, "G").Value
      Range("K10:L10").Value = .Cells(RowNum, "H").Value
      Range("C11:C12").Value = .Cells(RowNum, "I").Value
      Range("C13").Value = .Cells(RowNum, "J").Value
      Range("C14:L14").Value = .Cells(RowNum, "K").Value
      Range("C15:L15").Value = .Cells(RowNum, "L").Value
      Range("C16:L16").Value = .Cells(RowNum, "M").Value
      Range("C14:L14").Value = .Cells(RowNum, "N").Value
      Range("C17:H18").Value = .Cells(RowNum, "O").Value
      Range("K17:L18").Value = .Cells(RowNum, "B").Value
    End If
  End With
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Kuro Kuma22]

So I entered test data into the first row on the Hold Info sheet and then entered the number into the Hold Tag sheet after applying the code and saving the document as a .xlsm and nothing appears to be happening. Here is an updated version with the code active:

https://www.dropbox.com/s/d83llp3tlhkxq1p/Hold Book.xlsm?dl=0

I'm a little concerned that I may be going about this the wrong way as I'm also still trying to create drop down lists in some of the fields that are capable of auto complete, and from what I can find online this will require fields that are not directly able to be referenced.

I'm horrible at explaining things, but basically in the fields where people have to enter information such as the: Product; Hold Issued By; Disposition By columns I want people to be able to start typing in a name or product and it starts to autocomplete whatever it is that the person is typing. I'm not sure if working on this after entering the code you submitted will have an impact as your post states that it's meant to work with little to no changes at all.

I'm very new to Excel coding and advanced options, I've been using Mr. Excel to try and prose my current ideas and see if they're possible, but I am a complete amateur when it comes to excel spreadsheets.

 

[Rick Rothstein]

The code worked fine for me. What you are supposed to do is first fill out the data on the "Hold Info" sheet (that is what you said you wanted to do), then go to the "Hold Tag" sheet and enter a "Hold Number" into cell C1 (which is actually C1:E1 merged) and all the other fields will fill in (also what you said you wanted). Note... you must physically type something into cell C1 on the "Hold Tag" sheet and then hit the Enter Key (or Tab to a different cell) in order to make the code work.

 

[Kuro Kuma22]

I tried again and it worked! I apologize I'm not sure what I did wrong the first time. Do you know how things will go if I try to enter autocomplete able boxes?

 

[Rick Rothstein]

I tried again and it worked! I apologize I'm not sure what I did wrong the first time. Do you know how things will go if I try to enter autocomplete able boxes?

Where... on the "Hold Tag" sheet? If so, why? Your concept of filling everything in on the "Hold Info" sheet and using the "Hold Tag" sheet as a viewer seems straightforward enough to me.