Copy Inactive Worksheet using pre-defined data

mike_calc · Jul 23rd 2020

Thanks, I added the code and now I get a “compile error: Ambiguous name detected: Worksheet_Change” error that pops up in the VBA editor. This error pops up every time I add any text to a cell regardless of column or row I'm in.

Mumps · Jul 23rd 2020

That error usually means that you have two macros with the same name.

mike_calc · Jul 23rd 2020

Once again, thank you. Still learning this side of excel so apologies if I ask obvious questions J

It’s working now for the hyperlink but the value from the column D on the Site list tab is not populating on the newly created tab for that site (row). On the template I have an example of where it needs to go and in cell 1B on each of the templates I inserted the word “here” and when the template is copied and named the contents of cell 1B is blank.

Mumps · Jul 23rd 2020

try:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Application.ScreenUpdating = False
    Dim shName As String
    shName = Target.Offset(, -1).Value
    If Target <> "" Then
        If Not Evaluate("isref('" & shName & "'!A16)") Then
            With Sheets(Target.Value)
                .Visible = True
                .Copy after:=Sheets(Sheets.Count)
                ActiveSheet.Name = shName
                ActiveSheet.Range("B1") = Target.Offset(, 1)
                .Visible = False
            End With
        End If
    End If
    Sheets("Site List").Activate
    ActiveSheet.Hyperlinks.Add Anchor:=Target.Offset(, -1), Address:="", SubAddress:="'" & shName & "'!A1", TextToDisplay:=shName
    Application.ScreenUpdating = True
End Sub

Display More

mike_calc · Jul 23rd 2020

Same result as before. The existing word of "here" in cell B1 on the newly created tab is being deleted and the cell is left blank. The contents from the "site list" tab column D for that row isn't being copied over.

Mumps · Jul 24th 2020

Try the attached file. It seems to be working for me.

mike_calc · Jul 24th 2020

See attached file. On row 25 of the site list tab I created a site named test and the corresponding worksheet named test was created. On the newly created test worksheet the value in cell B1 is blank even though the value was set on the “site list” tab cell D25.

Mumps · Jul 24th 2020

It worked for me. Look at the attached file.

mike_calc · Jul 24th 2020

Thanks, it works when I select the value in column D on the site list tab before I select the site type value in column C. When I complete column C before column D then I get a blank value on the new tab created.

Mumps · Jul 24th 2020

Because a change in column C triggers the macro, it must be the last column populated. You could reverse columns C and D so that the Site Type is to the right of Host Type to make the flow easier and then change

Code

If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub

to

Code

If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub

mike_calc · Jul 24th 2020

I swapped column C and D, so now the Host Type is asked before the site type - since making that change and updating the code I don't get the Host Type updating on the new tab.

Also, not a huge deal but if I go in to the site list tab and select a row and hit delete (clear the contents) and then enter in new text (effectively adding a new site) everything works fine but the formatting in column B changes to underline and blue (as if it were a hyperlink before it is actually a hyperlink). Is there a way to restore the formatting to it's original if the cell is empty? On the attached I used row 21 to create a site called hyperlink and then I cleared the row - typed in not a hyperlink in B21 and as you can see the formatting makes it look like a hyperlink when it is really only text. Row 22 never had any preexisting data.

Mumps · Jul 24th 2020

Try:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Application.ScreenUpdating = False
    Dim shName As String
    shName = Target.Offset(, -2).Value
    If Target <> "" Then
        If Not Evaluate("isref('" & shName & "'!A16)") Then
            With Sheets(Target.Value)
                .Visible = True
                .Copy after:=Sheets(Sheets.Count)
                ActiveSheet.Name = shName
                ActiveSheet.Range("B1") = Target.Offset(, -1)
                .Visible = False
            End With
        End If
    End If
    Sheets("Site List").Activate
    ActiveSheet.Hyperlinks.Add Anchor:=Target.Offset(, -2), Address:="", SubAddress:="'" & shName & "'!A1", TextToDisplay:=shName
    Application.ScreenUpdating = True
End Sub

Display More

mike_calc · Jul 24th 2020

Thanks, Mumps,

It appears to be working – thank you. I moved some of the columns around to make it easier for the user to input their data. I updated the code and as far as I can tell everything is working. After reviewing the layout I also want to add the cell value in column C (sold to) on the site list tab to cell B4 on the newly created tab. I looked through the code and can’t figure out for the life of me where you specify this.

Mumps · Jul 25th 2020

The "Site List" sheet in the file you attached has no data.

mike_calc · Jul 27th 2020

Sorry about that, corrected version attached.

Mumps · Jul 27th 2020

Try:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("L:L")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Application.ScreenUpdating = False
    Dim shName As String
    shName = Target.Offset(, -10).Value
    If Target <> "" Then
        If Not Evaluate("isref('" & shName & "'!A16)") Then
            With Sheets(Target.Value)
            .Visible = True
            .Copy after:=Sheets(Sheets.Count)
                With ActiveSheet
                    .Name = shName
                    .Range("C2") = Target.Offset(, -1)
                    .Range("B4") = Target.Offset(, -9)
                    .Visible = False
                End With
            End With
        End If
    End If
    Sheets("Site List").Activate
    ActiveSheet.Hyperlinks.Add Anchor:=Target.Offset(, -10), Address:="", SubAddress:="'" & shName & "'!A1", TextToDisplay:=shName
    Application.ScreenUpdating = True
End Sub

Display More

mike_calc · Jul 27th 2020

Thanks, I inserted the code and now when I add a new site the new tab is being hidden and the template is unhidden. Is it as simple as changing line 10 and 16 in the new code?

Mumps · Jul 27th 2020

Try:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("L:L")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Application.ScreenUpdating = False
    Dim shName As String
    shName = Target.Offset(, -10).Value
    If Target <> "" Then
        If Not Evaluate("isref('" & shName & "'!A16)") Then
            With Sheets(Target.Value)
                .Visible = True
                .Copy after:=Sheets(Sheets.Count)
                With ActiveSheet
                    .Name = shName
                    .Range("C2") = Target.Offset(, -1)
                    .Range("B4") = Target.Offset(, -9)
                    .Visible = False
                End With
                .Visible = False
            End With
        End If
    End If
    Sheets("Site List").Activate
    ActiveSheet.Hyperlinks.Add Anchor:=Target.Offset(, -10), Address:="", SubAddress:="'" & shName & "'!A1", TextToDisplay:=shName
    Application.ScreenUpdating = True
End Sub

Display More

If you don't want the new sheet hidden, delete the first ".Visible = False" line of code.

mike_calc · Jul 27th 2020

Thank you Mumps. Everything appears to be working as designed. Much appreciated

Mumps · Jul 27th 2020

My pleasure.

Copy Inactive Worksheet using pre-defined data

Participate now!

Share

Tags