Excel - Search for specific sheet then paste data?


New Member
Oct 16, 2011
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!

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
<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>
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back