You are here:

Excel/VBA Copy & Paste Lookup


Post Adjustment Sheet
Post Adjustment Sheet  

Repairer Database
Repairer Database  
Hi Gulshan

Wondering if you could help me fix the below formula, at the moment it copies and pastes a data range using the header as a lookup, it works ok but the only issue im having is that its only copying the cells with data in and when it overwrites the previous data if the previous data is bigger (spread over a larger range) it won't clear the whole row. For example if Im copying 5 codes across and the pravious data was spread over 15, it will overwrite the 1st 5 entries and then leave the rest.

A potential solution would be to if the copy range was set, ie it copied 151 cells of data and tranposed it across along with the emty cells but Im not sure how to edit the VBA to reflect this. Can you help?

Sub Reset()

If MsgBox("Have you made the necessary updates?", vbYesNo) = vbNo Then Exit Sub

  Dim oCell As Range
  Dim oTarget As Range

  For Each oCell In Worksheets("Post Adjustment").Range("A8:G8")
      If Len(oCell.Value) > 0 Then
        Set oTarget = Nothing
        Set oTarget = Worksheets("Repairer Database").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

Hi Ben,

If I understand correctly, you need to make sure the destination range is cleared up before pasting new data.

Have you tried clearcontents command.

You could select the destination range and clear the contents before pasting the new data.

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




I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.


I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 All rights reserved.