open worksheet from different worksheet cell

[booyw]

hi world

what am I doing wrong on the attached
when I press the blue circle a calculation gets done and the result must paste in sheet2(w2)
but when I change Sheet 1 cell A2 to w3 the result must paste in sheet3(w3)

[vcoolio]

Hello Booyw,

Change your tab names to w1, w2, w3 etc. then try the code amended as follows:-



Sub Macro1()

Dim w1 As Worksheet, w2 As Worksheet, w3 As Worksheet, cVal As String
Set w1 = Sheet1 '---->sheet code.
Set w2 = Sheet2 '---->sheet code.
Set w3 = Sheet3 '---->sheet code.
cVal = w1.[A2].Value
If cVal = vbNullString Then Exit Sub

Application.ScreenUpdating = False

w1.[F2].Formula = "=D2*E2"
w1.[F2].Copy
Sheets(cVal).[A1].PasteSpecial xlValues

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

You'll note that I've used the sheet codes in the macro above rather than sheet names.
I don't know where you want to paste to in the relevant destination sheet so I've just used A1 as the destination cell in each sheet.

I hope that this helps.

Cheerio,
vcoolio.

[booyw]

thanks vcoolio
works great

[vcoolio]

You're welcome Booyw. I'm glad to have been able to assist.

Cheerio,
vcoolio.