Activate sheet copy and paste using cell

[Ztcollins]

Sub Copyrenameworksheet()

Dim ws As Worksheet
Set wh = Worksheets(ActiveSheet.Name)
Worksheets("Sheet7").Activate
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
If wh.Range("U2").Value "" Then
ActiveSheet.Name = wh.Range("U2").Value
End If
wh.Activate
End Sub

This Macro is set on a Form Controls Button on the Standings Page of the Workboook. The"Sheet7" which is the rootname for the "Default Points Page" i am trying to copy, move to end, and rename it from the cell U2 on that copied sheet.


I have researched this and i am just having a huge brain block on figuring this one out.

If wh.Range("U2").Value "" Then

This is the syntax error i am getting.

Please and thanks for the help in advance.

 

[Ztcollins]

its right in the actual VB script. I copied those lines from the top sorry for the confusion.

 

[Fluff]

Just realise it should not be an = sign it should be <> instead.

 

[Ztcollins]

Testing that change now

 

[Fluff]

Have you seen post#11?

 

[Ztcollins]

Doing the same things still but no errors

 

[Fluff]

What do you mean?

 

[Ztcollins]

It switches to the default points page worksheet, then creates the copy at the end of the other worksheets, but does not rename it nor does it make the copied worksheet the active worksheet. and there are no errors coming up during debug or actual use of the button.

 

[Fluff]

In that case can you post your entire code as it stands.

 

[Ztcollins]

Sub Points_Import()
    Dim FileToOpen As Variant
    Dim OpenBook As Workbook
    Dim ws As Worksheet

    Set ws = ActiveSheet
   
    Application.ScreenUpdating = False

    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Text Files (*.csv*),*csv*")

    If FileToOpen <> False Then
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        OpenBook.Sheets(1).Range("A1:V45").Copy
        ws.Range("T1").PasteSpecial xlPasteValues
        OpenBook.Close False
    End If

    Application.ScreenUpdating = True
End Sub

Sub Copyrenameworksheet()

Dim ws As Worksheet
Set wh = Worksheets(ActiveSheet.Name)
Worksheets("Default Points Page").Activate
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
If wh.Range("U2").Value <> "" Then
ActiveSheet.Name = wh.Range("U2").Value
End If
wh.Activate
End Sub

There seperate macros that i use on different buttons

 

[Fluff]

Other than the fact you have a typo with ws & wh there is nothing wrong with the code. Check that U2 on the sheet that is active when you run the code is not blank.