Sub assistance
 

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

Sub assistance
 

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

Sub assistance
 

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

Sub assistance
 

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

Sub assistance
 

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

Sub assistance
 

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

Sub assistance
 

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

Sub assistance
 

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

Sub assistance
 

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

Sub assistance
 

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

Sub assistance
 

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

Sub assistance
 

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

Sub assistance
 

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

Sub assistance
 

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.

Sub assistance
 

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

Sub assistance
 

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

Sub assistance
 

Many thanks, Dave. I'll need to test it out & monitor over several days

Do you want to set the timer and then decide to run it or always run it
Yes, thought I'd set it up once, ie install the sub, then click to run it
once, then leave it alone (like a sentinel). I'd need to work on the daily
output sheets every now and then

Sub assistance
 

I would always turn my pc off when I went home.

And there are lots of times I'd have to reboot--not just because of updates
coming from MS.

Max wrote:

Many thanks, Dave. I'll need to test it out & monitor over several days

Do you want to set the timer and then decide to run it or always run it
Yes, thought I'd set it up once, ie install the sub, then click to run it
once, then leave it alone (like a sentinel). I'd need to work on the daily
output sheets every now and then


--

Dave Peterson

Sub assistance
 

I would always turn my pc off when I went home
And there are lots of times I'd have to reboot--not just because of
updates
coming from MS.

Given the above circumstances, how could it be practically done then? The
process to auto-fire the 1st sub needs to be unmanned. The PC used is a
shared, common PC. The only thing that I know is that it's left on round the
clock (hence that excel file can also be left open in it). At the time when
1st sub needs to auto-fire, there's nobody around. Grateful for views.

Sub assistance
 

I've never been in a situation where the pc is left on continuously. Even under
the best of circumstances, I've had to reboot the pc.

The question that you'll have to answer is what happens after the reboot?

Will a person be there to launch excel and your workbook?

If yes, can he/she be trusted to start your application correctly?

If no, then you'll need some sort of scheduling program (windows scheduler(???)
or a visit to google) and you'll have to know how to start your program (run and
then start timer or start timer in that previous message).

I don't have an answer for you--well, I do, but most people wouldn't like it.

Find a trusted employee who can be trained to start the pc, start excel and
start your program following the rules you want. (I like the human touch
<vbg.)

Max wrote:

I would always turn my pc off when I went home
And there are lots of times I'd have to reboot--not just because of
updates
coming from MS.

Given the above circumstances, how could it be practically done then? The
process to auto-fire the 1st sub needs to be unmanned. The PC used is a
shared, common PC. The only thing that I know is that it's left on round the
clock (hence that excel file can also be left open in it). At the time when
1st sub needs to auto-fire, there's nobody around. Grateful for views.

--

Dave Peterson

Sub assistance
 

Dave, I hit some problems trying it out live at the office ..

a. The core Sub YourSubRoutineNameHere()
The pasted new sheet (copied from Live) seems to be prematurely? copied as
all of the formulated cells pasted are showing errors such as "#Name", or
"#N/A requesting data". The live formulas involved are Bloomberg formulas,
and the PC is a bloomberg terminal. When I tested it at home the other day,
I used some volatile functions (Rand(),Now()) and it worked fine. What can
be done to force the sub to wait awhile (say, 30 sec) before copying the
sheet, codename: Live, and pasting it? That should suffice to allow all
calcs to complete before the copy/paste proceeds.

b. The timer subs
On 2 testing occasions I was somehow caught in an interminable: "Rename
failed!" loop where answering the msgbox failed to end the sub (it looped to
return yet another "Rename failed!"), and I had to Ctrl-break to stop the
sub.

Grateful for further views, thanks

Sub assistance
 

I've never used the bloomberg stuff, so this is just a guess.

Try adding these two lines after the .copy line:

doevents
application.calculate

(maybe the doevents will mean you won't need the .calculate--you'll find out
soon!)

If that doesn't work, then try:

Set wks = ActiveSheet 'just copied version of live

'doevents
'application.calculate

With wks
.cells.Replace what:="=", replacement:="=", _
lookat:=xlPart, searchorder:=xlByRows, _
MatchCase:=False

....

This replaces the equal sign with equal sign. Hoping that it forces excel to
recalculate all the formulas in the new worksheet.

Since the name is changed to the month and day, there's a good chance that it'll
fail when you're testing.

I'd use something that would make it much harder to fail:

..Name = Format(Now, "yyyymmdd hhmmss")

It would be pretty weird to have the program run at the same second!

Max wrote:

Dave, I hit some problems trying it out live at the office ..

a. The core Sub YourSubRoutineNameHere()
The pasted new sheet (copied from Live) seems to be prematurely? copied as
all of the formulated cells pasted are showing errors such as "#Name", or
"#N/A requesting data". The live formulas involved are Bloomberg formulas,
and the PC is a bloomberg terminal. When I tested it at home the other day,
I used some volatile functions (Rand(),Now()) and it worked fine. What can
be done to force the sub to wait awhile (say, 30 sec) before copying the
sheet, codename: Live, and pasting it? That should suffice to allow all
calcs to complete before the copy/paste proceeds.

b. The timer subs
On 2 testing occasions I was somehow caught in an interminable: "Rename
failed!" loop where answering the msgbox failed to end the sub (it looped to
return yet another "Rename failed!"), and I had to Ctrl-break to stop the
sub.

Grateful for further views, thanks

--

Dave Peterson

Sub assistance
 

Thanks Dave. Will tinker as guided tomorrow, and feedback further in this
thread (in ~ 24 hours time). cheers

Sub assistance
 

Dave, think it seems to work, but I'll like to monitor it over the next few
days

What does doevents accomplish?

To enable accelerated testing, how could your Sub StartTimer() below be
changed
to say, fire it at 3 min intervals between 8 am - 9 am everyday?

------
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 assistance
 

DoEvents lets the pc handle some other stuff--sometimes, the VBA loops can
essentially take over the pc. The DoEvents says to let other processes run.

Go back to Chip's page. His start timer routine would be perfect for testing.


Max wrote:

Dave, think it seems to work, but I'll like to monitor it over the next few
days

What does doevents accomplish?

To enable accelerated testing, how could your Sub StartTimer() below be
changed
to say, fire it at 3 min intervals between 8 am - 9 am everyday?

------
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

--

Dave Peterson

Sub assistance
 

Dave, many thanks. Will do.