Sub assistance
in [Programming]
|
Prev: FIFO HELP!
Next: Application.GetOpenFilename
From: Max on 31 Dec 2009 18:06 Yes, thanks. Just realized it (aw, me red-faced)
From: Dave Peterson on 31 Dec 2009 18:11 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
From: Dave Peterson on 31 Dec 2009 18:13 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 joel wrote: > > 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 > > [url="http://www.thecodecage.com"]Microsoft Office Help[/url] -- Dave Peterson
From: Max on 31 Dec 2009 18:15 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.
From: Max on 31 Dec 2009 18:21
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 |