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]

Yeah its not blank because it is getting data from the points list macro copied and pasted in before i try to run the macro to copy and paste it. after that i have a clear macro that clears my default page.

 

[Ztcollins]

i have even went back and made all those cells where it is pasting in data from the csv file to be text.

 

[Fluff]

But is the same sheet active when you run both codes?

 

[Ztcollins]

No. First i use the Insert File Button on the Defaults Points Page. It allows me to select the csv file i want to upload. It uploads that data from T1:AO54 As shown in the pic below.

Then i go to my Standings Worksheet and Use the Default Copy Button which is where the issue is coming into play. See images below.

 

[Fluff]

U2 is blank on that sheet which is why it's not renaming the sheet.

 

[Ztcollins]

Thought about that. so made this change and the same effect.

Sub Copyrenameworksheet()

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

 

[Ztcollins]

So went and add that data to the U2 cell on the standings worksheet now getting a 1004 object error in this line

ActiveSheet.Name = wh.Range("U2").Value

 

[Fluff]

How about

Sub Copyrenameworksheet()
   Dim Ws As Worksheet
  
   Set Ws = ActiveSheet
   With Worksheets("Default Points Page")
      If .Range("U2").Value = "" Then Exit Sub
      .Copy After:=Worksheets(Sheets.Count)
      ActiveSheet.Name = .Range("U2").Value
   End With
   Ws.Activate
End Sub

 

[Ztcollins]

Same Error I am getting

 

[Fluff]

What is the value in U2