About Us

Miree

Thanks this worked really well.

"Mike H" wrote:

Miree,

I assume your record number is in Column A sheet2 and you want to pull
records to Sheet1 to edit and then put then back. These 2 routines should do
that. Right click sheet1 tab, view code and paste this 2 subs in. I would run
them from a button on sheet 1

Sub PullItOut()
Dim MyRange As Range
Dim FoundIt As Boolean
FoundIt = False
Do
Response = InputBox("Enter record number to edit", vbOKCancel)
If Response = "" Then Exit Sub
If Not IsNumeric(Response) Or Response = 0 Then
MsgBox ("You must enter a number")
Else
Exit Do
End If
Loop
Response = Val(Response)
lastrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Sheets("Sheet2").Range("A1:A" & lastrow)
For Each c In MyRange
If c.Value = Response Then
c.EntireRow.Copy
FoundIt = True
Exit For
End If
Next
If FoundIt Then
Range("A2").PasteSpecial
Else
MsgBox ("Record " & Response & " Not found")
End If
End Sub

Sub PutItBack()
Dim MyRange As Range
Dim FoundIt As Boolean
FoundIt = False
Record = Range("A2").Value
Rows(2).EntireRow.Copy
lastrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Sheets("Sheet2").Range("A1:A" & lastrow)
For Each c In MyRange
If c.Value = Record Then
c.PasteSpecial
FoundIt = True
Exit For
End If
Next
If FoundIt Then
MsgBox ("Record replaced")
Rows(2).EntireRow.ClearContents

Else
MsgBox ("No corresponding record forund")
End If
End Sub

Mike

"Miree" wrote:

I have a large excel data base, with an input sheet and storage sheet
each row in storgae has a unique number(at the moment i can only add then
next number)

I want to be able to user enter a number and pull the row out and put into
the entry page for editing.

Then to save over the previous data for that number with new edited data.

Thanks

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Find rows with a common item and find or highlight difference	jonnybrovo815	Excel Programming	2	February 27th 08 12:56 AM
Find and Replace - delete the remainder of the text in the cell after my Find	[email protected]	Excel Programming	4	August 4th 07 03:39 AM
Find First Non blank cell than find column header and return that value	Silver Rose	Excel Worksheet Functions	10	April 30th 07 05:56 PM
find and delete text, find a 10-digit number and put it in a textbox	Paul	Excel Programming	3	November 16th 04 04:21 PM
backwards find function to find character in a string of text	Ashleigh K.	Excel Programming	1	January 14th 04 04:36 PM

Menu

About Us