Activate sheet copy and paste using cell

Ztcollins

Board Regular
Joined
Jun 4, 2014
Messages
67
VBA Code:
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.

VBA Code:
If wh.Range("U2").Value "" Then

This is the syntax error i am getting.

Please and thanks for the help in advance.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,638
Office Version
  1. 365
Platform
  1. Windows
You need an equals sign before the ""
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,638
Office Version
  1. 365
Platform
  1. Windows
On which line?
 

Ztcollins

Board Regular
Joined
Jun 4, 2014
Messages
67

ADVERTISEMENT

VBA Code:
Worksheets("Sheet7").Activate

Its doing it after this line when i hit F8
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,638
Office Version
  1. 365
Platform
  1. Windows
In that case you don't have a sheet called Sheet7 in the active workbook.
 

Ztcollins

Board Regular
Joined
Jun 4, 2014
Messages
67

ADVERTISEMENT

Ok. Changed the name and now its saying 400 but it did copy the sheet and move it to the end but no rename.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,638
Office Version
  1. 365
Platform
  1. Windows
If you step through the code line by line using F8 what error do you get & on what line?
 

Ztcollins

Board Regular
Joined
Jun 4, 2014
Messages
67
VBA Code:
If wh.Range("U2").Value "" Then
ActiveSheet.Name = wh.Range("U2").Value
End If

when it steps through the first if line it skips the second and doesnt rename the worksheet it says 400 in the error
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,638
Office Version
  1. 365
Platform
  1. Windows
You are still missing the = sign.
 

Forum statistics

Threads
1,141,814
Messages
5,708,732
Members
421,588
Latest member
Wawie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top