Excel - Search for specific sheet then paste data?

[rayjay01]

Sheet1 contains everything I need to update all other sheets, for example I have a form that allows me to search for a specific persons information which is located in sheet2 (Tenant List), using the VLookup function.

At present, I have sheet1 (Main Controls), contact details, addresses and names in sheet2 (Tenant List) and all other sheets that may go up to 100 contain individual information for each person within the Tenant List. So person one will have the ID "1-7", so there's a sheet called "1-7".

I'm looking to create another form with the headings, ID, Date, Time, Issue, Resolution and Status.

When the data under these headings have been entered and a button is pressed I would like the Macro to search for the corresponding sheet name (which is the ID of the person) using the "ID" cell within the main form, then paste the information into that persons sheet which also contains the same headings.

Example: Sheet1, C21 has the heading (ID - Which is the individual persons ID, also sheet name), C22 (ID Entry Box), D21 (Date), D22 (Date Entry Box), E21 (Time), E22 (Time Entry Box), F21 (Issue), F22 (Issue Entry Box), C23 (Resolution), C24 (Resolution Entry Box).

C25:F25 will have a shape box awaiting a Macro to be assigned.

Using this example, I would like the Macro to search the ID Entry Box which say contains "1-7", find the sheet with that name, then paste all Information from the "Entry Boxes" into the corresponding cells in the sheet "1-7" .

If I haven't lost you, I would really appreciate some advice. Thanks!

 

[AlphaFrog]

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Find_ID_and_Copy()<br><br>    <SPAN style="color:#00007F">Dim</SPAN> wsID <SPAN style="color:#00007F">As</SPAN> Worksheet, Nextrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Worksheets("Main Controls")<br>    <br>        <SPAN style="color:#00007F">If</SPAN> .Range("C22") = "" <SPAN style="color:#00007F">Then</SPAN> MsgBox "No ID entered in cell C22.", , "Missing ID": <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> wsID = Sheets(.Range("C22").Text)<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>        <br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> wsID <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <br>            <SPAN style="color:#007F00">' Next empty row on worksheet ID</SPAN><br>            Nextrow = wsID.Range("A" & Rows.Count).End(xlUp).Row + 1<br>            <br>            <SPAN style="color:#007F00">'Copy values</SPAN><br>            wsID.Range("A" & Nextrow).Value = .Range("D22").Value    <SPAN style="color:#007F00">'Date</SPAN><br>            wsID.Range("B" & Nextrow).Value = .Range("E22").Value    <SPAN style="color:#007F00">'Time</SPAN><br>            wsID.Range("C" & Nextrow).Value = .Range("F22").Value    <SPAN style="color:#007F00">'Issue</SPAN><br>            wsID.Range("D" & Nextrow).Value = .Range("C24").Value    <SPAN style="color:#007F00">'Resolution</SPAN><br>            <br>            <SPAN style="color:#007F00">'Clear input cells</SPAN><br>            <SPAN style="color:#007F00">' .Range("D22:F22,C24").ClearContents</SPAN><br>            <br>            MsgBox "Issue and resolution copied.", , "Data Copy Complete."<br>            <br>        <SPAN style="color:#00007F">Else</SPAN><br>            MsgBox "Cannot locate worksheet: " & .Range("C22").Text, , "Cannot Locate Worksheet"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>