You are here:

Excel/VBA Lookup Copy and paste


Screen Shot
Screen Shot  

Im currently trying to put together a spreadsheet, where a name is selected from a drop down and the post codes that correspond to that name are brought up at the press of a macro button. I had used a VLOOKUP formula previously but it created problems with the conditional formatting which ultimately limited the use of the sheet.

I've used this codes previously to copy values from one sheet to another but is limited as the range is set, where as I need it to be dynamic to identify what range to copy. Also to make matters better it has to be transposed as the master sheet the data runs horizontal but the editing sheet it runs

   Sheets("Post Adjustment").Range("A10:A224").ClearContents
   Worksheets("Pre Adjustment").Range("A10:A224").Copy
   Worksheets("Post Adjustment").Range("A10:A224").PasteSpecial Paste:=xlValues

When the changes have been made on the editing sheet ideally there would be a 2nd macro which would copy and paste it back to the master sheet, so pretty much the reverse of the original macro. Any help on this would be great as at moment all Im getting is error messages


I'm not sure what you mean by making the macro dynamic, nor on what it has to copy and where it should paste?

In what way does the code need to be dynamic?

---------- FOLLOW-UP ----------

QUESTION: Sorry I meant it would look up the name selected from the drop down in the editing sheet and copy and paste the codes listed against that name from the master sheet onto the editing sheet. The master sheet is essentially the database that will store all the codes.

So the codes are entered in which cell(s), those on row 8?
And to what target cells do they need to go, those belonging to the proper row in the column headed with "Repairer"?

---------- FOLLOW-UP ----------

QUESTION: The codes are copied from the mastersheet to the column with the same name on the editing sheet from row 10 downwards. For an Example the codes that would be pasted into the editing sheet under Johns name would be NW1, NW2, NW3. Under Angus W1, W2, W5, W6 etc. :-)

This macro should do the trick (you do need to change the worksheet names!)

Sub CopyInfo()
   Dim oCell As Range
   Dim oTarget As Range

   For Each oCell In Worksheets("Editing sheet").Range("A8:G8")
       If Len(oCell.Value) > 0 Then
         Set oTarget = Nothing
         Set oTarget = Worksheets("Master Sheet").UsedRange.Columns(1).Find(oCell.Value)
         If Not oTarget Is Nothing Then
         If Application.CountA(oCell.EntireColumn) > 1 Then
         oTarget.Offset(, 5).PasteSpecial xlValues, , , True
         End If
         End If
       End If
End Sub
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Jan Karel Pieterse


Excel and Excel/VBA questions


Excel MVP

Self employed Excel developer

Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...

©2017 All rights reserved.