CODEWITHSUNDEEP
vbaexcel
gabx
gabx

Reputation: 482

copy/paste range with formula to another worksheet

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.

Upvotes: 4

Views: 3865

Answers (1)

user2140173
user2140173

Reputation:

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

Upvotes: 2

Related Questions