Hold Tag Auto Populate

Kuro Kuma22

New Member
Joined
Mar 23, 2017
Messages
43
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:

b3JQWA.jpg


The second tab is the Hold/Rework Tag itself:

7mVO63.jpg


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?
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Sektor

Well-known Member
Joined
May 6, 2011
Messages
2,834
Well, you need VBA code to move data from one sheet to another
umnik.gif
 

Kuro Kuma22

New Member
Joined
Mar 23, 2017
Messages
43
Well, you need VBA code to move data from one sheet to another
umnik.gif
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,690
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Mar 23, 2017
Messages
43

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,690
Office Version
  1. 2010
Platform
  1. Windows
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.
Code:
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

New Member
Joined
Mar 23, 2017
Messages
43

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,690
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Mar 23, 2017
Messages
43
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,690
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,737
Messages
5,597,826
Members
414,180
Latest member
Sir Khaya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top