I need a sub to copy a sheet: Live (this is its codename), then insert a new
sheet, do a paste special as values & formats, then rename the new sheet as
the date in ddmmm format, eg: 31Dec, and move this to be the leftmost sheet.
Then to save the file. Thanks

Codename is a readonly proerty in VBA so yo can't change it from a
macro.


Set NewSht = Sheets.Add(befo=Sheets(1))
NewSht.Name = Format(data, "ddmmm")

For Each Sht In Sheets
If UCase(Sht.Name) = "LIVE" Then
Sht.Copy
NewSht.PasteSpecial _
Paste:=xlPasteValues
NewSht.PasteSpecial _
Paste:=xlPasteFormats

Exit For
End If

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=166224

Microsoft Office Help

You can change the codename via a macro.

if the user allows programmatic access (tools|macro|security|trusted publishers
tab in xl2003 menus), you can use something like:

dim sh as object 'any old sheet type
set sh = activeworkbook.sheets("aaa")
ThisWorkbook.VBProject.VBComponents(sh.codename).N ame = "NewCodeName"
'or
ThisWorkbook.VBProject.VBComponents(sh.CodeName) _
.Properties("_CodeName").Value = "NewCodeName2"



joel wrote:

Codename is a readonly proerty in VBA so yo can't change it from a
macro.

Set NewSht = Sheets.Add(befo=Sheets(1))
NewSht.Name = Format(data, "ddmmm")

For Each Sht In Sheets
If UCase(Sht.Name) = "LIVE" Then
Sht.Copy
NewSht.PasteSpecial _
Paste:=xlPasteValues
NewSht.PasteSpecial _
Paste:=xlPasteFormats

Exit For
End If

End Sub

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=166224

Microsoft Office Help

--

Dave Peterson

Thanks Dave, Joel for your inputs
I had no intents to change the codename: Live

I tried Joel's code like this (added a Next):

Sub test()

Set NewSht = Sheets.Add(befo=Sheets(1))
NewSht.Name = Format(data, "ddmmm")

For Each Sht In Sheets
If UCase(Sht.Name) = "LIVE" Then
Sht.Copy
NewSht.PasteSpecial _
Paste:=xlPasteValues
NewSht.PasteSpecial _
Paste:=xlPasteFormats

Exit For
End If
Next

End Sub

but I got stuck at this line:
NewSht.Name = Format(data, "ddmmm")
What I wanted was to rename the new sheet as the current date
How can I replace "data" so that this happens?

When I remarked the above line, and stepped through the rest of the sub,
nothing else happened in the new sheet? It should get pasted with
data/formats from Live.

Thanks for further help

On Dec 31, 5:40*pm, "Max" wrote:

but I got stuck at this line:
NewSht.Name = Format(data, "ddmmm")
What I wanted was to rename the new sheet as the current date
How can I replace "data" so that this happens?

Use the Replace() function to create the string you want in the
filename.

Alan

Alan, thanks
Think it was just a typo (should be Date, not data) which I failed to see
earlier:
NewSht.Name = Format(Date, "ddmmm")

There is a typo in Joels code. That should be:

NewSht.Name = Format(Date, "ddmmm")

Date is a VBA constant that returns the current date.


"Max" wrote in message
...
Thanks Dave, Joel for your inputs
I had no intents to change the codename: Live

I tried Joel's code like this (added a Next):

Sub test()

Set NewSht = Sheets.Add(befo=Sheets(1))
NewSht.Name = Format(data, "ddmmm")

For Each Sht In Sheets
If UCase(Sht.Name) = "LIVE" Then
Sht.Copy
NewSht.PasteSpecial _
Paste:=xlPasteValues
NewSht.PasteSpecial _
Paste:=xlPasteFormats

Exit For
End If
Next

End Sub

but I got stuck at this line:
NewSht.Name = Format(data, "ddmmm")
What I wanted was to rename the new sheet as the current date
How can I replace "data" so that this happens?

When I remarked the above line, and stepped through the rest of the sub,
nothing else happened in the new sheet? It should get pasted with
data/formats from Live.

Thanks for further help

Yes, thanks. Just realized it (aw, me red-faced)

It also looks like he forgot to save the file. Here is Joel's code modified
to include the file save.

Sub test()

Set NewSht = Sheets.Add(befo=Sheets(1))
NewSht.Name = Format(Date, "ddmmm")

For Each Sht In Sheets
If UCase(Sht.Name) = "LIVE" Then
Sht.Copy
NewSht.PasteSpecial _
Paste:=xlPasteValues
NewSht.PasteSpecial _
Paste:=xlPasteFormats

Exit For
End If
Next
ActiveWorkbook.Save
End Sub





"Max" wrote in message
...
Thanks Dave, Joel for your inputs
I had no intents to change the codename: Live

I tried Joel's code like this (added a Next):

Sub test()

Set NewSht = Sheets.Add(befo=Sheets(1))
NewSht.Name = Format(data, "ddmmm")

For Each Sht In Sheets
If UCase(Sht.Name) = "LIVE" Then
Sht.Copy
NewSht.PasteSpecial _
Paste:=xlPasteValues
NewSht.PasteSpecial _
Paste:=xlPasteFormats

Exit For
End If
Next

End Sub

but I got stuck at this line:
NewSht.Name = Format(data, "ddmmm")
What I wanted was to rename the new sheet as the current date
How can I replace "data" so that this happens?

When I remarked the above line, and stepped through the rest of the sub,
nothing else happened in the new sheet? It should get pasted with
data/formats from Live.

Thanks for further help

Thanks for the modified sub, JLGWhiz. Just tried it, but I'm afraid nothing
gets pasted into the new sheet from Live. I've got Dave's sub working ok, so
I think I can use that to get by. As responsed to Dave in his branch, the
last bit I need is a way (another sub?) to auto-fire that sub at say 8 am
daily. The file will be left open throughout on a PC which is left on 24x7.
If you have any thoughts on this, please reply further in Dave's branch.
Thanks.

Sht.copy
will copy the sheet to a new workbook.

Maybe...

Set NewSht = Sheets.Add(befo=Sheets(1))
NewSht.Name = Format(data, "ddmmm")

For Each Sht In Sheets
If UCase(Sht.Name) = "LIVE" Then
Sht.Cells.copy
NewSht.range("a1").PasteSpecial _
Paste:=xlPasteValues
NewSht.range("A1").PasteSpecial _
Paste:=xlPasteFormats

Exit For
End If

End Sub

joel wrote:

Codename is a readonly proerty in VBA so yo can't change it from a
macro.

Set NewSht = Sheets.Add(befo=Sheets(1))
NewSht.Name = Format(data, "ddmmm")

For Each Sht In Sheets
If UCase(Sht.Name) = "LIVE" Then
Sht.Copy
NewSht.PasteSpecial _
Paste:=xlPasteValues
NewSht.PasteSpecial _
Paste:=xlPasteFormats

Exit For
End If

End Sub

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=166224

Microsoft Office Help

--

Dave Peterson

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim IsVisible As Boolean

IsVisible = Live.Visible
'make sure Live is visible if it's not
Live.Visible = xlSheetVisible

Live.Copy _
befo=ThisWorkbook.Sheets(1)

Set wks = ActiveSheet 'just copied version of live

With wks
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
On Error Resume Next
.Name = Format(Date, "ddmmm")
If Err.Number < 0 Then
Err.Clear
MsgBox "Rename failed!"
End If
On Error GoTo 0
End With

Live.Visible = IsVisible

ThisWorkbook.Save

End Sub

By copying the sheet (not the cells), the formats should be ok. Converting to
values would still need to be done, though.



Max wrote:

I need a sub to copy a sheet: Live (this is its codename), then insert a new
sheet, do a paste special as values & formats, then rename the new sheet as
the date in ddmmm format, eg: 31Dec, and move this to be the leftmost sheet.
Then to save the file. Thanks

--

Dave Peterson

Thanks Dave. That does it ok.
Now I just a need a way (another sub?) to auto-fire that sub at say 8 am
daily
Grateful for any thoughts on this

If excel is open, you can use an OnTime macro.

Chip Pearson shows how:
http://www.cpearson.com/excel/OnTime.aspx

If excel isn't open, then you'll need something to start excel and open your
workbook at 8:00 AM (with macros enabled, so change your security settings!).

Depending on your version of windows, you may have a scheduling program
built-in. If not, search google for some ideas.

And name the macro Auto_Open so that it runs when that workbook is opened.

After the save, you could use:

....

Live.Visible = IsVisible

ThisWorkbook.Save

Application.Quit 'quit excel

End Sub

But if you have excel open, you may not want to quit!


Max wrote:

Thanks Dave. That does it ok.
Now I just a need a way (another sub?) to auto-fire that sub at say 8 am
daily
Grateful for any thoughts on this

--

Dave Peterson

Dave,
The file with your earlier sub will be left open throughout on a PC which is
left on 24x7
How would the complete sub look like? Thanks

Maybe something like this:

Option Explicit
Public RunWhen As Double
Public Const cRunWhat = "YourSubRoutineNameHere"
Sub Auto_Open()
Call StartTimer
End Sub
Sub Auto_Close()
Call StopTimer
End Sub
Sub StartTimer()
If Time < TimeSerial(8, 0, 0) Then
RunWhen = Date + TimeSerial(8, 0, 0)
Else
RunWhen = Date + 1 + TimeSerial(8, 0, 0)
End If
Application.OnTime EarliestTime:=RunWhen, Procedu=cRunWhat, _
Schedule:=True
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedu=cRunWhat, _
Schedule:=False
End Sub
Sub YourSubRoutineNameHere()

Dim wks As Worksheet
Dim IsVisible As Boolean

IsVisible = Live.Visible
'make sure Live is visible if it's not
Live.Visible = xlSheetVisible

Live.Copy _
befo=ThisWorkbook.Sheets(1)

Set wks = ActiveSheet 'just copied version of live

With wks
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
On Error Resume Next
.Name = Format(Date, "ddmmm")
If Err.Number < 0 Then
Err.Clear
MsgBox "Rename failed!"
End If
On Error GoTo 0
End With

Live.Visible = IsVisible

ThisWorkbook.Save

Call StartTimer

End Sub

=========
Depending on what you want to do when the workbook opens, this procedu

Sub Auto_Open()
Call StartTimer
End Sub
could be:
Sub Auto_Open()
Call YourSubRoutineNameHere
End Sub

Do you want to set the timer and then decide to run it or always run it.

Or you could do what I'd do...ask.

Sub Auto_Open()
Dim resp As Long

resp = MsgBox(Prompt:="Yes to run" _
& vbLf & "No to Start Timer" _
& vbLf & "Cancel to do nothing", _
Buttons:=vbYesNoCancel)

Select Case resp
Case Is = vbYes: Call YourSubRoutineNameHere
Case Is = vbNo: Call StartTimer
Case Else
MsgBox "You're on your own!"
End Select
End Sub




Max wrote:

Dave,
The file with your earlier sub will be left open throughout on a PC which is
left on 24x7
How would the complete sub look like? Thanks

--

Dave Peterson