Copy lines of other sheets based on criteria entered into two cells

[kbovard]

Hello!

I am trying to see if there is a way to achieve my idea without nesting IF AND/IF OR statements in the formula bar. I am not too familiar with VBA, but I imagine there is some code that could help me with the following goal:

I am creating an excel workbook database that will allow users to enter a document number in cell B3 and a material number in B6 on the "Autofill" sheet (Sheet1). There is a separate corresponding sheet in the workbook for each possible document number that can be entered in B3. On each of those sheets there are lines of information that correspond with each material value that can be entered in B6.

I would like to code a simple command button that will copy that line or lines onto line 11 of "Autofill" sheet based on the values entered in B3 and B6. So each time new numbers are entered, new information replaces line 11.

Anyone able to help? It would be MUCH appreciated

 

[JLGWhiz]

The column for the material value on the document sheets is not specified, but the code below uses Column A. If that is incorrect you will need to adjust the code accordingly. The code is written for use with a Form Controls command button, so it should be copied to the standards code module1.

Sub t()
Dim sh As Worksheet, fn As Range, fAdr As String
Set sh = Sheets("Autofill")
 With sh
  Set fn = Sheets(.Range("B3").Value).Range("A:A").Find(.Range("B6").Value, , xlValues, xlWhole)
   If Not fn Is Nothing Then
    sh.Rows(11).ClearContents    
    fn.EntireRow.Copy sh.Range("A11")
   End If
 End With
End Sub

This code only copies one row per specified sheet. But the statement below implies that there could be more than one row of data for the material value. You need to clarify this if the code needs to be modified. A screen shot or example of your document number sheets would be helpful.

copy that line or lines onto line 11 of "Autofill" sheet

 

[kbovard]

Thank you so much for the quick reply! I think I understand what is being done here. I'm unfortunately unable to download any attachment software to this computer, so I will try to explain better via text.

Sheet1 ("Autofill") asks the user to enter a document number in B3 and two of the possible responses are: 01008765 or 01008771. (There are 13 possible entries in that cell, but those are two examples.)

Cell B6 is where the material should be entered and possible values are: 10000697, 10000705, and 10000701.

The data for the combo of B3 = 01008765 and B6 = 10000697 is found on Sheet2 ("01008765"), A9:G9.

The data needing copied for the combo of B3 = 01008771 and B6 = 10000701 is found on Sheet3 ("01008771"), A19:G21. However, the combo for B3 = 01008771 and B6 = 10000705 is ALSO found on Sheet3 ("01008771"), A19:G21

The copied cells will then display on "Autofill" starting in cell C11. In your code you have it searching for those values in a column, but multiple material numbers are associated with the same line or two of data, so right now all the material numbers are pasted into a header cell above the data lines. I would rather just code specific lines/cells that I would need copied for each combo rather than searching for the values. Is this making it more complicated?

If this is too confusing and you would prefer I attach examples of my sheets, I can try and find a way to do so. Thanks again!

 

[JLGWhiz]

I don't believe I can offer anything better without seeing the sheet layout for the source sheets.
Regards, JLG