Sub assistance
in [Programming]
|
Prev: FIFO HELP!
Next: Application.GetOpenFilename
From: Alan on 31 Dec 2009 17:48 On Dec 31, 5:40 pm, "Max" <demecha...(a)yahoo.com> 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
From: JLGWhiz on 31 Dec 2009 17:56 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" <demechanik(a)yahoo.com> wrote in message news:%23k$E5omiKHA.4912(a)TK2MSFTNGP02.phx.gbl... > 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 >
From: JLGWhiz on 31 Dec 2009 18:00 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" <demechanik(a)yahoo.com> wrote in message news:%23k$E5omiKHA.4912(a)TK2MSFTNGP02.phx.gbl... > 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 >
From: Max on 31 Dec 2009 18:00 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
From: Max on 31 Dec 2009 18:04
Alan, thanks Think it was just a typo (should be Date, not data) which I failed to see earlier: NewSht.Name = Format(Date, "ddmmm") |