copy/paste range with formula to another worksheet

excel, vba

Solution

You need to qualify the Cells objects (right hand side of the equation)

and for the below you need to have `ws1` (`Sheets("A")`) activated

ws1.Activate
ws1.Range(Cells(19, 1), Cells(41, 7)).Formula = ws.Range(ws.Cells(19, 1), ws.Cells(41, 7)).Formula

Problem

I may be stupid, but I can't find a simple way to copy/paste a range of formula from ws1 to ws in the same workbook. I want to avoid the heavy and unsuitable `Range.select select.copy Sheet("X").select ...` story. ``` Set wb = Application.Workbooks("TT.xlsm") Set ws = wb.Sheets("B") Set ws1 = wb.Sheets("A") <-----more code-------> Sheets("A").Range(Cells(19, 1), Cells(41, 7)).Copy _ Destination:=Sheets("B").Range(Cells(19, 1), Cells(41, 7)) ``` Returns an error. Same if I catch the expression between a `With wb [code] End With`. ``` ws1.Range(Cells(19, 1), Cells(41, 7)).Formula = ws.Range(Cells(19, 1), Cells(41, 7)).Formula ``` Returns Range method has failed, `With wb` or not. I must be missing something, but can't see what! Thank you for your light.

Original source