VBA Paste Entire Row over existing row by matching row A

[dturgel]

Hi,

I have the following code for copying and pasting an entire row from one spreadsheet into another:

Sub ExportRow72()


'Declare Variables
Dim MyRange As Range, rng As Worksheet
Set rng = Workbooks("2012 to 2016 bound new test VBA").Sheets("2015 CLASH")
Set MyRange = Workbooks("Copy of Mkt Curve Tool Working Copy SW v3").Sheets("Summary In 2").Rows(72)
MyRange.Copy
rng.Range("a" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues


End Sub

But instead of pasting into the last row, I want to lookup/match the value in column A and paste over that row (the rows are the exact same values so each cell will match one for one) - any idea how to do that? Also, my row has cells that are both numbers and formulae, any idea which member of xlPasteType class I should use?

Thanks!

Daniel

 

[DebugGalpin]

No worries you just need to add a match in there.

RowId = APplication.WorksheetFunction.Match( Cell Ref , rng.columns(1),0)

then change the paste

rng.Cells(RowID,1).PasteSpecial xlPasteValues

 

[dturgel]

Hi,

I was getting an error on the RowID line but then I saw another thread which said to change application.worksheetfunction.match to application.match and that got me past that line but now the final line is yielding a type mismatch (Error 13) error - any advice?

Also, is there any way to do this cell by cell so that I can paste values when I need to paste values and paste formulas when I need to paste formulas?

Daniel

 

[dturgel]

This is what I ended up doing and it seems to work:

'Declare Variables
Dim MyRange As Range
Set rng = Workbooks("2012 to 2016 bound new test VBA").Sheets("2016 CLASH")
Set MyRange = Workbooks("Copy of Mkt Curve Tool Working Copy SW v3").Sheets("Summary In 2").Rows(103)
RowID = Application.Match(MyRange.Columns(1), rng.Columns(1), 0)
MyRange.Rows.Copy
rng.Rows(RowID).PasteSpecial xlPasteValues
End Sub