Excel/VBA Copy & Paste Lookup
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?
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
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.
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