Sub assistance

[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

 

[joel]

Codename is a readonly proerty in VBA so yo can't change it from
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 Su

 

[Dave Peterson]

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

 

[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 _
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.

 

[Max]

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

 

[Alan]

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

 

[JLGWhiz]

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.

 

[JLGWhiz]

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

 

[Max]

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

 

[Max]

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

 

[Max]

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

 

[Dave Peterson]

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

 

[Dave Peterson]

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

 

[Max]

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.

 

[Max]

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]

Maybe something like this:

Option Explicit
Public RunWhen As Double
Public Const cRunWhat = "YourSubRoutineNameHere"
Sub Autpen()
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 Autpen()
Call StartTimer
End Sub
could be:
Sub Autpen()
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 Autpen()
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]

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]

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]

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]

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