Sub assistance

  • Thread starter Thread starter Max
  • Start date Start date
M

Max

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
 
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).Name = "NewCodeName"
'or
ThisWorkbook.VBProject.VBComponents(sh.CodeName) _
.Properties("_CodeName").Value = "NewCodeName2"


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

Set NewSht = Sheets.Add(before:=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/showthread.php?t=166224

Microsoft Office Help
Click to expand...
 
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 _
before:=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.
 
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(before:=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
 
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?
Click to expand...
Use the Replace() function to create the string you want in the
filename.

Alan
 
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.
 
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(before:=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
 
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
 
Alan, thanks
Think it was just a typo (should be Date, not data) which I failed to see
earlier:
NewSht.Name = Format(Date, "ddmmm")
 
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!
 
Sht.copy
will copy the sheet to a new workbook.

Maybe...

Set NewSht = Sheets.Add(before:=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
Codename is a readonly proerty in VBA so yo can't change it from a
macro.

Set NewSht = Sheets.Add(before:=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/showthread.php?t=166224

Microsoft Office Help
Click to expand...
 
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.
 
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, Procedure:=cRunWhat, _
Schedule:=True
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=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 _
before:=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 procedure:

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
 
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
Click to expand...
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
 
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.
 
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.
Click to expand...

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.
 
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>.)
 
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
 
Back
Top