Posts by NUBELUBE

NUBELUBE · Jul 10th 2020

Thank you works great. Think I confused the issue by rambling on. Much appreciated and thank you for you patience.

NUBELUBE · Jul 10th 2020

oK Will do when i get home all I want is to get the value from a cell into a userform cant see why you would one the work books but will do it comes as 2 workbooks

Thank you

NUBELUBE · Jul 9th 2020

Hi sorry.

Not sure if this is allowed here is the form in the first work book and the sheet behind it fills in, The check for tool default is the trigger for the code

The userform is called UserToolDetails you can see form and sheet marked 1 and 2. If this breached the rules or is just rubbish please remove the thread thank you

Code

Private Sub BodSearch_Click()
'1. declare and set variables
'2. clear old search results
'3. find records that match criteria and paste them to the report sheet
Dim wb As String
Dim sh As String
Dim path As String
Dim Link_Table As Worksheet 'where the data is copied from
Dim Man_Mod As Worksheet 'where the data is put
Dim Initial_Input As Worksheet
Dim Manufacturer As String
Dim Model As String
Dim i As Integer 'row counter
Manufacturer = cmbManufacturer.Text
Model = CmbModel.Text
path = "C:\Users\Dad\Desktop\Excel\Torque Total Package - V3\"
wb = "Wrench Data.xlsm"
sh = "Link_Table"
Workbooks.Open path & wb
Sheets(sh).Activate
Worksheets("Link_Table").Range("X2").Value = Manufacturer
Worksheets("Link_Table").Range("Y2").Value = Model
Worksheets("Man_Mod").Range("F2") = Manufacturer
Worksheets("Man_Mod").Range("G2") = Model
'Clear old data from Toolspecific sheet
Worksheets("Man_Mod").Select
Worksheets("Man_Mod").Range("Data_Search").ClearContents
'goto WrenchData sheet and start seacrhing and copying
Worksheets("Link_Table").Select
Worksheets("Link_Table").Range("a1").Select
finalrow = Cells(Rows.Count, 1).End(xlUp).Row
'loop through the rows to find the matching records
For i = 2 To finalrow
If Cells(i, 1) = Manufacturer And Cells(i, 2) = Model Then 'if the name matches then search and copy
Range(Cells(i, 1), Cells(i, 22)).Copy ' copy 19 columns -details
Worksheets("Man_Mod").Select ' go to to specific sheet
Range("F20").End(xlUp).Offset(1).PasteSpecial xlPasteFormulasAndNumberFormats ' find the first blank row and paste
Worksheets("Link_Table").Select 'GO BACK TO WRENCH DATA AND CONTINUE SEARCHING
End If
Next i
Worksheets("Man_Mod").Range("F3:AA12").Copy 'copy the top ten records to calculation ranges
Worksheets("Selected").Range("b3").PasteSpecial ' paste the top ten records to calculation ranges
If Worksheets("Selected").Range("B10") = "" Then
MsgBox ("Not enough Data for BS6789-2-2017")
End If
Workbooks("Wrench Data.xlsm").Worksheets("Selected").Range("Select").Copy ' copy calculated values
Workbooks("Torque Software V3 2020_6_7.xlsm").Worksheets("Variables").Range("B34").PasteSpecial 12 ' paste values to original work book
'******here is where the code will go to addthen to the use form. Its quite a big work book******
End Sub

Display More

NUBELUBE · Jul 9th 2020

Hi,

I have some code that when triggered from a userform searches using 2 combo boxes as the criteria on a different work book, choose the top ten matches perform some simple calculations the paste them into the original workbook as paste special values. works great.(some code from here).

How do I get "them" values into the userform that is still open and eventually into the worksheet that the use for filling in in? No point me posting any code:

A) as I have none for this bit, though have tried.

b) I never get the layout right.

Hope that makes sense. Any help very much appreciated.

NUBELUBE · May 1st 2013