Userforms and textbox

[watkins6878]

Hi All

The problem i am having is this: I have a Workbook with two worksheets
Sheet1 has 6 columns (Delivery NO,Customer,Store,order No,size,Drops
on this sheet i run a macro to find a delivery No and display its location, I would like to be able if possible to copy the contents of the row then have a userform which i enter a load No and Drop No in and then paste this to the next empty line in sheet two which has 8 columns in

Load No,Drop No,Delivery NO,Customer,Store,order No,size,Drops
Any tip would be helpful

thanks in advance

 

[HalfAce]

Hello watkins6878,
I'm not quite clear on exactly what you want to do.
It seems you already have the code to find the location (row to copy?) of a Delivery # but I'm unclear on what you want to do at this point.
Do you want to copy this row and then load up a userform with (I assume) a couple textboxes that you can enter the Load # & Drop # in, and then paste what into sheet2?

 

[watkins6878]

I want to copy the row then in my user form i have two textboxs which i will enter a load No and Drop No then i want to paste the rowwhich was copied earlier and the infromation from the userform into sheet 2
The userform will relate to columns a & b then the copied row will take the next 6 columns
but this may need to be done 100 times so it would need to look for the next empty line in sheet 2

 

[HalfAce]

OK, this can be done in two steps.
When your existing code copies the row of interest, make your destination be: Sheets("Sheet2").Cells(Rows.Count, "C").End(xlUp)(2, 1)
That will take care of the columns C through H.
You can then show your userform. Once you get your values entered into the textboxes, the code to paste them to columns A & B would look like this:

With Sheets("Sheet2")
  'Put TextBox1 value into columnA
  .Cells(Rows.Count, "C").End(xlUp).Offset(, -2).Value = TextBox1.Text
  'Put TextBox2 value into columnB
  .Cells(Rows.Count, "C").End(xlUp).Offset(, -1).Value = TextBox2.Text
End With

Does that help?

 

[watkins6878]

the first macro uses an inputbox to enter delivery no could i user another text box on my user form instead and if so how would i need to change this code

Dim Target As Range, FirstAddress As String, It As String
It = InputBox("Find what?", "Looking For?")
If It = Empty Then Exit Sub

 

[HalfAce]

OK. I'm only thinking that this is what you're attempting to do, so let me explain what I've done here.
Say you have your userform with 3 textboxes and a command button.
TextBox1 is for the Load #.
TextBox2 is for the Drop #.
TextBox3 is for the value to search for. (The Delivery #.)

Assumming your data is layed out as I understand it...

(Sheet1 contains):
Delivery #, Customer, Store, Order #, Size & Drops in columns A through F respectively.

(Sheet2 contains):
Load #, Drop #, Delivery #, Customer, Store, Order #, Size & Drops in columns A through H respectively.

Then this code for your userform commandbutton should do what you're after.

Private Sub CommandButton1_Click()
Dim fCell As Range, fCellRow As Long
If TextBox3 <> "" Then
    Set fCell = Cells.Find(TextBox3, lookat:=xlWhole)
    If Not fCell Is Nothing Then
        fCellRow = fCell.Row
        With Sheets("Sheet2")
            .Cells(Rows.Count, "A").End(xlUp)(2, 1).Value = TextBox1.Text
            .Cells(Rows.Count, "B").End(xlUp)(2, 1).Value = TextBox2.Text
        End With
        Sheets("Sheet1").Range(Cells(fCellRow, 1), Cells(fCellRow, 6)).Copy _
            Sheets("Sheet2").Cells(Rows.Count, 3).End(xlUp)(2, 1)
    End If
End If
End Sub

Am I even close to what you're looking for?

[EDIT:]
Oh yeah, this also assumes you'll be showing the userform (& therefore running the code) while sheet1 is the activesheet...

 

[watkins6878]

Yes works great thankyou very much easy when you know how you sorted that in half the time and with nowhere near as much code as i was trying to use

also would it be much work if i want to stop duplicate delivery No from being pasted into sheet2

 

[HalfAce]

also would it be much work if i want to stop duplicate delivery No from being pasted into sheet2

Nope, not much work at all.
Try replacing the commandbutton code with this...

Private Sub CommandButton1_Click()
Dim fCell As Range, fCellExists As Range, fCellRow As Long
If TextBox3 <> "" Then
  Set fCell = Cells.Find(TextBox3.Text, lookat:=xlWhole)
  If Not fCell Is Nothing Then
    fCellRow = fCell.Row
    With Sheets("Sheet2")
      Set fCellExists = .Columns(3).Find(fCell, lookat:=xlWhole)
      If Not fCellExists Is Nothing Then _
        MsgBox ("Delivery number '" & fCell.Value & "' already exists in sheet 2."), , _
        "Duplicate Delivery Number...": Exit Sub
      .Cells(Rows.Count, "A").End(xlUp)(2, 1).Value = TextBox1.Text
      .Cells(Rows.Count, "B").End(xlUp)(2, 1).Value = TextBox2.Text
    End With
    Sheets("Sheet1").Range(Cells(fCellRow, 1), Cells(fCellRow, 6)).Copy _
        Sheets("Sheet2").Cells(Rows.Count, 3).End(xlUp)(2, 1)
  End If
End If
End Sub

That any better?

 

[watkins6878]

Yeah work great thanks

 

[watkins6878]

sorry about this i have one other thing to ask if possible with the data in sheet2 I would like to sort the data by load number and Drop order which i have done with a sort macro but is there a way to have a macro look for the last instance of say load 1 then insert a blank row and then load 2 then insert a blank row and on Etc Etc
load are displayed with numbers only