Another Copy/Paste Question
I'm really close to getting this right. I'm sure this code looks like
a mess but it basically does this: it looks in Sheet1 Column 1 for "MCC", if it finds it, the data in Columns 2 and 3 are copied and pasted into a spreadsheet called ("Lookup.xls") where values are populated into Column 6, the values in Column 6 are then pasted back into Column 4 of Sheet1. The problem is that the data is pasted at the top of column 4 in Sheet1, I need it to paste into the same row from which it was copied. Can someone please help me with this? Sub Get_Data() Dim CP As Range Dim iR As Range Dim i As Long Dim Wb As Workbook Dim dRange1 As Range Dim dRange2 As Range Dim dRange3 As Range Application.ScreenUpdating = False With Worksheets("Sheet1") Set CP = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp)) Set dRange3 = Cells(2, 4) End With i = 1 Set Wb = Workbooks.Open("A:\Lookup.xls") Set dRange1 = Wb.Sheets(13).Range("B3") For Each iR In CP If UCase(Trim(iR.Value)) = "MCC" Then iR.Cells(1, 2).Copy dRange1.Cells(i, 1).PasteSpecial xlPasteValues, , False, False iR.Cells(1, 3).Copy dRange1.Cells(i, 2).PasteSpecial xlPasteValues, , False, False i = i + 1 With Wb.Sheets(13) Set dRange2 = .Range("F3", .Cells(Rows.Count, 6).End(xlUp)) End With dRange2.Copy dRange3.PasteSpecial xlPasteValues, , False, False Application.ScreenUpdating = True End If Next End Sub Thanks again, -- Dan |
All times are GMT +1. The time now is 09:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com