Retaining Worksheet name as string

[jimrosser]

Hello,
I've created code that allows the user to select a file to open and select a worksheet to copy and make a new worksheet from it. However I am unable to hold the previous worksheet name as a variable. This is needed a few steps down in the macro for inserting a vlookup formula that uses a reference to the original worksheet?

Is there a way to capture the name of the previous worksheet and retain throughout the macro?

[HTML][/Sub insertformulas()
With Range("b4")
Dim shname As String
shname = ActiveSheet.name

.Formula = "=VLOOKUP($a4,'" & shname & "'!A:A,1,FALSE)"
'.AutoFill Destination:=Range("b4:b" & LastRow)
End With
End SubHTML]

[Marc L]

Hi !

As each worksheet has a Name property …

[nigelog]

Collect the name at the correct time as a string

Please Login or Register  to view this content.

[jimrosser]

Thanks for the help, I rigged nigelog's code and made it work.