Help.. need to change sheet name to whats in a cell?

L

lild

This is my macro so far... need it to change the sheet name to what's in
cell J8 or alternitivly, that days date. This is so that at the end of
each day a copy of the sheet is made with that days date bookings on
and the orignal sheet is cleared for the next day.

Sub dailymacro()
'
' dailymacro Macro
' This is the main macro. Does several tasks, copys days booking,
renames sheet to that days date, delete days booking ready for next day
and recolours tab to make it easier for navigation.
'

'
Sheets("TODAYS BOOKING").Select
Sheets("TODAYS BOOKING").copy Before:=Sheets(2)
Sheets("TODAYS BOOKING (2)").Select
Sheets("TODAYS BOOKING (2)").Move After:=Sheets(4)
Sheets("TODAYS BOOKING (2)").Select
ActiveWorkbook.Sheets("TODAYS BOOKING (2)").Tab.ColorIndex = 3
Range("J8").Select
*NEED IT TO CHANGE SHEET NAME TO J8 OR DATE HERE*
End Sub


If anyone can help me? or if you need more details just ask.
 
E

evgny

ActiveWorkbook.SaveAs Filename:= _
"C:\yourshetname.xls" & Date, FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False

Regards Yngve
 
T

Tom Ogilvy

Sub dailymacro()
'
' dailymacro Macro
' This is the main macro. Does several tasks, copys days booking,
renames sheet to that days date, delete days booking ready for next day
and recolours tab to make it easier for navigation.
'

'
Sheets("TODAYS BOOKING").Select
Sheets("TODAYS BOOKING").copy Before:=Sheets(2)
Sheets("TODAYS BOOKING (2)").Select
Sheets("TODAYS BOOKING (2)").Move After:=Sheets(4)
Sheets("TODAYS BOOKING (2)").Select
ActiveWorkbook.Sheets("TODAYS BOOKING (2)").Tab.ColorIndex = 3
Range("J8").Select
*NEED IT TO CHANGE SHEET NAME TO J8 OR DATE HERE*
Activeworkbook.SaveAs ActiveWorkbook.Path & "\" & Range("J8").Text
End Sub
 
L

lild

Thanks for your help but didnt want it saving as a new file...

I mean the active sheet it copies.... need to be rename to the days
date or the cell reference J8...

Sheet todays booking > Copied > Rename copied sheet (active sheet) to
either J8 or todays day ...

You understnad what I mean
 
L

lild

DONE IT!!!

WAS EASIER THAN I EXPECTED!!

Sub dailymacro()
'
' dailymacro Macro
' This is the main macro. Does several tasks, copys days booking,
renames sheet to that days date, delete days booking ready for next day
and recolours tab to make it easier for navigation.
'

'
Sheets("TODAYS BOOKING").Select
Sheets("TODAYS BOOKING").copy Before:=Sheets(2)
Sheets("TODAYS BOOKING (2)").Select
Sheets("TODAYS BOOKING (2)").Move After:=Sheets(4)
Sheets("TODAYS BOOKING (2)").Select
ActiveWorkbook.Sheets("TODAYS BOOKING (2)").Tab.ColorIndex = 3
Range("J8").Select
Selection.copy
ActiveSheet.Name = Range("J8") 'activesheet.range("J8").value _
& " production worksheet"


thanks for all your help, sorry if i wasted ya time, I'm still trying
to learn how excel/VB works proper as only 16 lol
 
T

Tom Ogilvy

You can get a good start on your code by using the macro recorder while
doing the action manually. Then look at the code recorded. Obviously you
want to avoid the

Range("J8").Select
Selection.Value = "Dog"

type of construct, but it gives good insight into the objects and properties
in various commands.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top