Printable View
I wanted to see if i can have someone enter a number in a input box i put on a spreadsheet that then would look at one column for that number if found to grab all that match and move to the next available line and move everything else up. And If possible I want to be able to call a macro after that that runs on another worksheet.
Hi Em,Quote:
Originally Posted by Emoncada
If the number is found grab all of what that match.. the row?
Move to the next line? You mean at the bottom or to another sheet?
You answered your own question about calling a macro....use call
[VBA]call yourmacro[/VBA]
Basically if the order number is found in 3 rows i want it to grab those 3 rows and move them to the bottem of the same sheet.
Example
If order number is found in column A in row 5,6,7, & 8 then I want it to grab rows 5:8 cut and paste on the bottom on the sheet underneath the last line so if the last row is 28 i want it to go into row 29,30,31 & 32, but then i don't want rows 5:8 empty so i would like to move everything up.
And the call is a little more difficult than that. The macro runs when you double click in a cell on sheet2, but i want to be to have it double click it depending on the number entered in the first part. So if the number is "12345" then it will look for "12345" on sheet1 grab it move to bottom and then look for "12345" on sheet2 then when found have it double click it. Which then opens a form. I know that a lot any help would be great.
Let me explain my situation. I have a form that I need to be able to call back after it's sent to the spreadsheet. So i was trying to see if i can get some help on how to do that with no luck. I did have code on doing it but just with all the data in the same row. So what I did was created another worksheet that takes all the data and put's it all in one row, while still keeping the data on the original worksheet the way i want it.
So now I am able to call the userform back with all the information i need. So I hide the new worksheet with all the data in one row, and would like to be able to call it through the first original sheet. That is why I am trying this, this way. It's the only way I can figure it can work.
I can attach spreadsheet if needed.
I think that would be a help. It saves us having to replicate your workbook.Quote:
Originally Posted by Emoncada
Sorry i should of just added it. Hope this helps.
Is this possible?
There must be a better way of getting the value fron the dropdown!
The move can be accomplished with
[vba]Sub MoveRows()
Dim Rng As Range, c As Range, FirstAddress As String
Dim Num As Long
Num = Range("A1").Offset(ActiveSheet.DropDowns(1))
With Columns(1)
Set c = .Find(Num, LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
If Rng Is Nothing Then
Set Rng = c
Else
Set Rng = Union(Rng, c)
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
Rng.EntireRow.Copy Cells(Rows.Count, 1).End(xlUp).Offset(1)
Rng.EntireRow.Delete
End Sub
[/vba]
Basically I setup the combobox on the spreadsheet to pull all in column A. I would of liked just to have the user double click the order like I have in the second worksheet but it seems that isn't possible. So that's when I came up with the idea if they select the one to edit then somehow run the macro to have the second worksheet open up the form depending on what was selected in the combobox. The reason I wanted for this to delete the original is because if there is a change like they add a new item, I don't want it to affect another order. Possible overwrite an updated order. I know this is a little complex but I have seen some amazing stuff from this forum so I hope someone will give it a try.
MdMack do you recommend some other way of doing what I am trying to do a different way?