Adding new spreadsheet where spreadsheet name is....

[AndyD]

Hi
I want users to be able to add a date to a field (as eg 01.01.2004) press a button then for macro to make a copy of the spreadsheet (in the same workbook) and call it 01.01.2004

any ideas mucho appreciated
cheers all
Andy

 

[SIXTH SENSE]

hi try this one!
this assumes that the name of the new sheet created is stored in A1.


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CopyAndRename()
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> owari
    <SPAN style="color:#00007F">Set</SPAN> oldsheet = ActiveSheet
    <SPAN style="color:#00007F">Set</SPAN> newsheet1 = Sheets.Add
    oldsheet.Select
    Cells.Select
    Selection.Copy
    newsheet1.Paste
    Sheets(newsheet1.Name).Name = oldsheet.Range("a1").Value
owari:
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[AndyD]

thanks, that's brilliant!

if want to go to the new sheet, whats the best way of doing...?

sheets(range("A1").value).select does not seem to work

cheers
Andy

 

[SIXTH SENSE]

hi!
try this.
range("A1").value
should be complete.
worksheets("sheet1").range("A1").value
you might end up the active sheet containing no or wrong value in A1.
but if you want it to make the new sheet the active sheet after copying, call
newsheet1.select.

 

[AndyD]

hi

what do i need to select a worksheet based on a value in a sheet?


Worksheets("results").Range("AK1").value.Select

does not work

cheers all
Andy