VBA Find/Select Worksheet with Range as Name

[jrepko11]

Hello,

I am creating a workbook that will allow multiple users to work at once. When the workbook opens, a template is copied and pasted as a new sheet with their name as the sheet name. The workbook_open macro looks at their windows username in a list and then creates the sheet for their name (a column over). This is working fine.

I am also creating a macro for when they save the workbook. I first need their worksheet to be activated so that the right data is copied and pasted with the save button. Here is the code I have. I'm wondering if I can call up a worksheet that has a range as a name?

Sub TestSave()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim rng As Range

With Worksheets("Associates").Range("A:A")
Set rng = .Find(What:=UserNameWindows, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Application.Goto rng, True
Selection.Activate

Dim Name As Range
Set Name = ActiveCell.Offset(0, 1)
End If
End With
Worksheets(Name).Activate

End Sub

I thought about using location, as the sheet pastes before the "Associates" sheet when they open the book, but if the book is shared, I'm not sure if their specific sheet will always be the one in front of "Associates"

Any ideas would help, please let me know if you have questions. Thanks!!

 

[swaroop]

Use

Dim Name As String
Name = ActiveCell.Offset(0, 1)

instead of

Hello,
Dim Name As Range
Set Name = ActiveCell.Offset(0, 1)

 

[jrepko11]

Wow, well I knew I was a noob!! Haha thanks!!!!

 

[swaroop]

Lol...