Macro: Copy/rename sheet and create hyperlink. Error. Are shared documents not macro friendly?

[MickeyW]

Hi All,

I'm very very very new to excel macros so please be kind.

I found a macro online and was able to add a couple of extra steps to do what I want it to do. Everything was working fine. I tested the macro heaps of times, and it worked fine. Tried it again later in the day and now I'm coming up with a 1004 run time error.

Little background the document is uploaded to sharepoint. I open "in app" and work with it that way. It is also a shared document which I don't have the authority to change anything as it is through my company. I noticed that as I was in the sheet, I saw a coworker was making changes (just writing comments in cells not to the macro). And after that the macro would not work. I thought it was because 2 people were in at once and maybe the active cell part of my macro might be confused so I tried again this morning when there was no one else in the sheet. Still received an error.

My question is there an issue with macros in share documents? And if the shared document is not the issue what else might be going wrong?

Quickl macro breakdown.... There is a table on sheet "GNA Rehab Status Tracker". The ProjectID is in column B and the Description in column C. The idea was to select the ProjectID in column B and run the macro. The name in the active cell would become the name of the sheet and be used to create the hyperlink. Once the template sheet is copied and renamed, I added code to paste the projectID and Description into E2 and F2 respectfully. Then I will come back to the original Tracker sheet and create a hyperlink that would be offset 19 to be placed in the same row but in column U.

I've stepped through the macro and the error happens at line " ActiveWindow.ActiveSheet.Range("E2").Value = tempProjectID "

Thank you in advance!



Sub CopyRenameHyperlink()


Dim tempProjectID As String
Dim sh As Worksheet, nsh As Worksheet
Dim nrng As Range
Dim cont As Worksheet
Dim oRng As Range



tempProjectID = ActiveCell.Value
tempProjectDesc = Range("C" & ActiveCell.Row).Value
Set oRng = ActiveCell.Offset(0, 19)
Set sh = Sheets("GNA Rehab Status Tracker")


For rep = 1 To (Worksheets.Count)
If LCase(Sheets(rep).Name) = LCase(sheet_name_to_create) Then

MsgBox "this sheet already exists"
Exit Sub
End If

Next

Sheets("Template").Visible = True
Sheets("Template").Copy after:=Sheets(Sheets.Count)


ActiveWindow.ActiveSheet.Name = tempProjectID
ActiveWindow.ActiveSheet.Range("E2").Value = tempProjectID
ActiveWindow.ActiveSheet.Range("F2").Value = tempProjectDesc

Sheets("Template").Visible = False

sh.Activate
sh.Hyperlinks.Add oRng, "", "'" & tempProjectID & "'!A1", _
"Go to " & tempProjectID, tempProjectID

Set oRng = Nothing

End Sub

 

[Micron]

1) please post code within code tags to maintain indentation and readability
2) advise to not only step through code but check your references and variable values as you go. You can mouse over them and often detect what they are, or you can use the immediate window to inquire (e.g. ?ActiveWindow.ActiveSheet.Range("E2").Value) and hit Enter and see the value. Because almost every object has the Name property, Name is also a useful property to vet references (e.g. ?ActiveWindow.ActiveSheet.Name) and hit enter (again, in the immediate window). You might find that the active sheet is not what you expect. Or you can use the watch or locals window. NOTE - a line has to execute (be processed) before you can rely on validating whatever it applies to.

IMO, 1004 is the least helpful error number, given that it applies to many problems. You should always post the error message even if it isn't 1004. Otherwise you're expecting anyone to remember what your posted number means out of thousands of error numbers, or worse, look it up for you.