Using cell value for worksheet name

[Cellvalue]

Hello,

I am trying to copy and paste using the code below. The VBA macro works fine if you enter two static worksheet names (A and B in this case).

However I want worksheet A to have a dynamic name (whatever the user changes the sheet name to). I have a code that displays the sheet name in cell E5. I want to reference that cell in my VBA for sheet A instead of having to input a static worksheet name (A).

Anyone know how to solve this problem?

The copy/paste code I am using is

Worksheets("A").Range("A2").Copy
Worksheets("B").Range("A2").PasteSpecial Paste:=xlPasteFormulas

 

[offthelip]

try this:

Sub test()
sname = ActiveSheet.Range("E5")


Worksheets(sname).Range("A2").Copy
Worksheets("B").Range("A2").PasteSpecial Paste:=xlPasteFormulas
End Sub