LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6
Default Worksheet copy with rename

Private Sub CommandButton1_Click()
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Set sht1 = ActiveSheet
Set sht2 = Worksheets.Add(after:=sht1)
sht2.Name = sht1.Range("J1").Value
sht1.UsedRange.Copy
sht2.Range("A1").PasteSpecial (xlPasteAll)


End Sub

The above code is supposed to:
1. create a new worksheet after the current worksheet containing all the information in the current worksheet and
2. To renme the worksheet to the contents of J1 in the first worksheet.

The problem:
Depending on the order of the statements, it either creates the new worksheet and renames it correctly OR it creates the worksheet
and correctly copies the info. I can't get it to do both.

What am I doing wrong? Is there a better way? Thanks


 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro: Copy a worksheet and rename it to a specified value mitch Excel Discussion (Misc queries) 2 February 16th 09 05:43 PM
Copy worksheet and rename sheet Ernst - EXE Graphics Excel Discussion (Misc queries) 4 July 25th 08 01:18 PM
Copy/Paste Worksheet to last and rename with input box for value. bigwheel[_2_] Excel Programming 0 September 7th 04 10:17 PM
Copy/Paste Worksheet to last and rename with input box for value. Dave Peterson[_3_] Excel Programming 2 September 7th 04 08:59 PM
Copy worksheet from multiple files in one DIR to another DIR & rename Mike Taylor Excel Programming 1 July 13th 03 03:28 PM


All times are GMT +1. The time now is 10:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"