Rename sheet name as a value in a cell

Hello, this is a VBA query relating to Microsoft Excel 2007.

I am trying to run a macro that copies the current sheet and deletes some of the content to provide a new blank sheet.

Ideally I want this to use a value that is shown in a cell within the new sheet. I had tried to acheive this by copying the cells contents and then pasting as a value within a macro, however this worked on the first sheet but the second time I ran the macro, the second new sheet was named the same as the first new sheet.

I had tried stepping into the macro's code, but cannot work it out. Can anyone help me?? Any help would be much appreciated. Many thanks.

re: Rename sheet name as a value in a cell

Hi

This is not too difficult, it is just a matter of being clear as to which sheet you are refering to. If the new sheet name is held in the "New" sheet you are creating/copied, then in the line immediatly after the copy is made (this will then be the active sheet) add this

ActiveSheet.Name = ActiveSheet.Cells(x,y) (x & y being you row/column numbers)
Or
ActiveSheet.Name = ActiveSheet.Range("??")

Strickly speaking ActiveSheet could be omitted at this pont in the code.


If the Sheet Name is not within the "New" sheet then you could explicitly refer to the sheet with the name in it, perhaps posting the code (or relevant fragment) would help??


MTB

re: Rename sheet name as a value in a cell

Hi MTB, thanks for the reply. The cell is within the new sheet, however, the cell value needs to be ammended as part of the macro, then I want the new value to be the sheet name. The code from the Macro is below, could you tell me where I need to insert the code? The cell I am trying to use is C7. Many thanks for your help.

Sub NewSheet()
'
' NewSheet Macro
'

'
Sheets("Timesheet").Select
Sheets("Timesheet").Copy After:=Sheets(1)
Range("C5").Select
ActiveCell.FormulaR1C1 = "=Timesheet!R[57]C+3"
Range("C5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "7/6/2009"
Range("O6:O9").Select
Selection.ClearContents
Range("O6").Select
ActiveCell.FormulaR1C1 = "=IF(Timesheet!R[60]C>=0,Timesheet!R[60]C,"""")"
Range("O7").Select
ActiveCell.FormulaR1C1 = "=IF(Timesheet!R[59]C<0,Timesheet!R[59]C,"""")"
Range("O9").Select
ActiveCell.FormulaR1C1 = "=Timesheet!R[60]C"
Range("O6:O9").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("O6").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "1:45:00 AM"
Range("O7").Select
ActiveCell.FormulaR1C1 = ""
Range("O9").Select
ActiveCell.FormulaR1C1 = "12:00:00 AM"
Range("D13:E17,H13:I17,K13:M17,R13:R17").Select
Range("R13").Activate
ActiveWindow.SmallScroll Down:=12
Range( _
"D13:E17,H13:I17,K13:M17,R13:R17,D28:E32,H28:I32,K 28:M32,R28:R32,D43:E47,H43:I47,K43:M47,R43:R47" _
).Select
Range("R43").Activate
ActiveWindow.SmallScroll Down:=18
Range( _
"D13:E17,H13:I17,K13:M17,R13:R17,D28:E32,H28:I32,K 28:M32,R28:R32,D43:E47,H43:I47,K43:M47,R43:R47,D58 :E62,H58:I62,K58:M62,R58:R62" _
).Select
Range("R58").Activate
ActiveWindow.SmallScroll Down:=-48
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-63
Range("A1").Select
End Sub[/i]

re: Rename sheet name as a value in a cell

I think instead of posting such a huge code (in which C7 is no where selected), please post your specific problem or the sheet with code and problem you are facing. So that we may help.

re: Rename sheet name as a value in a cell

Hi

Again I don't know where you are going to get youe new sheet name from, but try this (modified to suit)
and see how you get on.


MTB