VBA Copy from another sheet & paste in cell where the formula is

Hi

I think your main problem is that CellCopy is an integer number whereas you want CellCopy to be a cell reference on worksheet 2. Are you trying to pick up the value for a numbered row in a given column?

Regards

Dominic

A function does not copy (Action), but return VALUES only, unless calling it. You need to change it to sub

Hi,

This is going to be part of something bigger but for now I want to copy a given value in Sheet2 ("Mappings") and paste it into the Active Cell of Sheet1 ("Main").

Thanks for the help so far...

Sub Copy2()

 Dim Sel_Val As Range

Set Sel_Val = ActiveWorkbook.Worksheets("Mappings").Range("E4")
Sel_Val.Select
Sel_Val.Copy
Worksheets("Main").ActiveCell.PasteSpecial xlValues

End Sub

Hi,

This is going to be part of something bigger but for now I want to copy a given value in Sheet2 ("Mappings") and paste it into the Active Cell of Sheet1 ("Main").

Thanks for the help so far...

Sub Copy2()

 Dim Sel_Val As Range

Set Sel_Val = ActiveWorkbook.Worksheets("Mappings").Range("E4")
Sel_Val.Select
Sel_Val.Copy
Worksheets("Main").ActiveCell.PasteSpecial xlValues

End Sub

Since you are copying in to active cell, you need to put the cursor on the active cell when you run the code.

Sub Copy2()



ActiveWorkbook.Worksheets("Mappings").Range("E4").Copy


Worksheets("Main").ActiveCell.PasteSpecial xlValues

End Sub

Originally Posted by AB33

Since you are copying in to active cell, you need to put the cursor on the active cell when you run the code.

Sub Copy2()



ActiveWorkbook.Worksheets("Mappings").Range("E4").Copy


Worksheets("Main").ActiveCell.PasteSpecial xlValues

End Sub

I have my cursor on the active cell and that is the one I have selected. When using your code I get the error "Object doesn't support this property or method".

Thanks for your patience...new to this, this week!

Has the active cell got a name? Where is the active cell in sheet main? is in in A1, B1, etc?

Assuming you want to copy in to A1,

Sub Copy2()

ActiveWorkbook.Worksheets("Mappings").Range("E4").Copy


Worksheets("Main").Range("A1").PasteSpecial xlValues

Application.CutCopyMode = 0

End Sub

Originally Posted by AB33

Assuming you want to copy in to A1,

Sub Copy2()

ActiveWorkbook.Worksheets("Mappings").Range("E4").Copy


Worksheets("Main").Range("A1").PasteSpecial xlValues

Application.CutCopyMode = 0

End Sub

That worked great thanks when I put in an actual cell range!

It's "P2" in Sheet Main.

I really want to avoid setting the paste destination to a hard coded place because later on I want to use the copy&paste in a Function...this is assuming if I put "P2" in as the paste destination it would be an absolute cell reference?