Sub assistance
in [Programming]
|
Prev: FIFO HELP!
Next: Application.GetOpenFilename
From: Max on 31 Dec 2009 15:45 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
From: joel on 31 Dec 2009 16:00 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]
From: Dave Peterson on 31 Dec 2009 17:21 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" 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: Dave Peterson on 31 Dec 2009 17:28 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 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
From: Max on 31 Dec 2009 17:40
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 |