Macro: cell value from Sheet1 as cell reference on Sheet2 in which to paste

[AndyHughson]

Hi, I have a cell on Sheet1 that has the cell reference from a MATCH function that tells me the cell reference on Sheet2 in which I wish to paste a value.

The ACTUAL value I wish to paste into my destination cell in Sheet2 is the contents of cell C8 from Sheet1, which is a date.
The Cell Reference in Sheet2 in which I wish to paste that value is calculated with a MATCH function and shown in cell M2 of Sheet1 (which for instance might be "E21").
So I want to pick up the copied value from C8 and paste that value in the cell reference in Sheet2 as dictated by the value of M2 on Sheet1.

My code so far below picks up C8 and copies it into fixed cell reference E2 of Sheet2, but I want that "E2" Range to be a variable row number within column E as defined by the cell reference held in cell M2 of Sheet1 (e.g. "E21").
How do I do that? (the code below is the result of recording the macro, hence the fixed "E2" destination cell range)

Sheets("Sheet1").Select
Range("C8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Font.Bold = False
Selection.NumberFormat = "dd/mm/yyyy"

[ORoos]

Hi Andy,

It would be helpful if you attach a sample workbook (see yellow banner at top). This makes it much easier to see what data you are working with and testing solutions offered.

Anyway, I made some assumptions. Refer to the attached file and let us know if that works for you.

Regards

Please Login or Register  to view this content.

[AndyHughson]

Great - that worked a treat, thanks!