Rename sheet name as a value in a cell

[DWHTHS]

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.

[MikeTheBike]

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.

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.Nam e = ActiveSheet.Cel ls(x,y) (x & y being you row/column numbers)
Or
ActiveSheet.Nam e = ActiveSheet.Ran ge("??")

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

[DWHTHS]

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("Timeshe et").Select
Sheets("Timeshe et").Copy After:=Sheets(1 )
Range("C5").Sel ect
ActiveCell.Form ulaR1C1 = "=Timesheet !R[57]C+3"
Range("C5").Sel ect
Selection.Copy
Selection.Paste Special Paste:=xlPasteV alues, Operation:=xlNo ne, SkipBlanks _
:=False, Transpose:=Fals e
Application.Cut CopyMode = False
ActiveCell.Form ulaR1C1 = "7/6/2009"
Range("O6:O9"). Select
Selection.Clear Contents
Range("O6").Sel ect
ActiveCell.Form ulaR1C1 = "=IF(Timesheet! R[60]C>=0,Timesheet! R[60]C,"""")"
Range("O7").Sel ect
ActiveCell.Form ulaR1C1 = "=IF(Timesheet! R[59]C<0,Timesheet! R[59]C,"""")"
Range("O9").Sel ect
ActiveCell.Form ulaR1C1 = "=Timesheet !R[60]C"
Range("O6:O9"). Select
Selection.Copy
Selection.Paste Special Paste:=xlPasteV alues, Operation:=xlNo ne, SkipBlanks _
:=False, Transpose:=Fals e
Range("O6").Sel ect
Application.Cut CopyMode = False
ActiveCell.Form ulaR1C1 = "1:45:00 AM"
Range("O7").Sel ect
ActiveCell.Form ulaR1C1 = ""
Range("O9").Sel ect
ActiveCell.Form ulaR1C1 = "12:00:00 AM"
Range("D13:E17, H13:I17,K13:M17 ,R13:R17").Sele ct
Range("R13").Ac tivate
ActiveWindow.Sm allScroll Down:=12
Range( _
"D13:E17,H13:I1 7,K13:M17,R13:R 17,D28:E32,H28: I32,K28:M32,R28 :R32,D43:E47,H4 3:I47,K43:M47,R 43:R47" _
).Select
Range("R43").Ac tivate
ActiveWindow.Sm allScroll Down:=18
Range( _
"D13:E17,H13:I1 7,K13:M17,R13:R 17,D28:E32,H28: I32,K28:M32,R28 :R32,D43:E47,H4 3:I47,K43:M47,R 43:R47,D58:E62, H58:I62,K58:M62 ,R58:R62" _
).Select
Range("R58").Ac tivate
ActiveWindow.Sm allScroll Down:=-48
Selection.Clear Contents
ActiveWindow.Sm allScroll Down:=-63
Range("A1").Sel ect
End Sub[/I]

[smartchap]

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.

[MikeTheBike]

Hi

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

    Sheets("Timesheet").Select
    Sheets("Timesheet").Copy After:=Sheets(1)
    Range("C7") = "Your New Sheet Name"
    ActiveSheet.Name = Range("C7")

and see how you get on.


MTB