Activate sheet copy and paste using cell

Ztcollins

Board Regular
Joined
Jun 4, 2014
Messages
69
Office Version
  1. 365
Platform
  1. Windows
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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
You need an equals sign before the ""
 
Upvote 0
That fixed that part of it but now i am getting an Subscript out of Range
 
Upvote 0
VBA Code:
Worksheets("Sheet7").Activate

Its doing it after this line when i hit F8
 
Upvote 0
In that case you don't have a sheet called Sheet7 in the active workbook.
 
Upvote 0
Ok. Changed the name and now its saying 400 but it did copy the sheet and move it to the end but no rename.
 
Upvote 0
If you step through the code line by line using F8 what error do you get & on what line?
 
Upvote 0
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
 
Upvote 0
You are still missing the = sign.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,733
Members
448,294
Latest member
jmjmjmjmjmjm

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
Back
Top