ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Rn, _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Data").Sortthis is a snippet of the code I worked on (via macro recorder) to sort data. If I convert this macro to an add-in, the worksheet won't always be named "Data". So, I want to modify this code so that the macro acts on the Active worksheet. any suggestions?
VB:Sub SaveAsExcel() Dim WS As Worksheet Dim MyDay As String Dim MyMonth As String Dim MyYear As String Dim MyPath As String Dim MyFileName As String Dim MyCellContent As Range Application.ScreenUpdating = False MyDay = Day(Date) MyMonth = Month(Date) MyYear = Year(Date) MyPath = "[URL="file://Ho-0001-ncham/"]Ho-0001-ncham[/URL]" ' use declared Windows API function to set the path SetCurrentDirectoryA (MyPath) Set WS = ActiveSheet Set MyCellContent = WS.Range("B3") MyFileName = "Agent " & Range("A2").Text & " " & Format$(Date, "mm-dd-yyyy") WS.Copy With WS.UsedRange .Copy .PasteSpecial xlPasteValues End With Application.CutCopyMode = False Application.WindowState = xlMinimized ' ChDir MyPath If CInt(Application.Version)
VB:which returns "object does not support this property or method"Worksheets("Calc").Range ("BH38:CC47") Application.Run "OnKeyCalculateSelection"If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:which says "Select method of clas range failed"Worksheets("Calc").Range ("BH38:CC47").Select Application.Run "OnKeyCalculateSelection"If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:PrintPortfolio() If ActiveSheet.Name = "G&I" Or ActiveSheet.Name = "Growth" Then Application.ScreenUpdating = False ActiveSheet.PageSetup.PrintArea = "$a$1:$Q$170" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "Information as of &D" .CenterFooter = "Confidential" .RightFooter = "Daniel K. Speirs, Financial Consultant" .LeftMargin = Application.InchesToPoints(0.05) .RightMargin = Application.InchesToPoints(0.05) .TopMargin = Application.InchesToPoints(0.05) .BottomMargin = Application.InchesToPoints(0.05) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False .PrintErrors = xlPrintErrorsDisplayed Application.ScreenUpdating = True End With Application.ActivePrinter = "RSTB1TBCOLOR1 on Ne02:" ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "RSTB1TBCOLOR1 on Ne02:", Collate:=True Application.ScreenUpdating = True Else End If End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:Message() ' This example use late binding, you don't have to set a reference ' You must be online when you run the sub Dim iMsg As Object Dim iConf As Object Dim cell As Range ' Dim Flds As Variant Dim lname As String Dim Msg As String Dim Response Dim Answer Dim Question As String Application.ScreenUpdating = False Set iConf = CreateObject("CDO.Configuration") iConf.Load -1 ' CDO Source Defaults Set Flds = iConf.Fields With Flds .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "LEED" .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 .Update End With lname = ActiveWorkbook.ActiveSheet.name Msg = "Are you sure you want to send an email?" Response = MsgBox(Msg, vbYesNo) If Response = vbYes Then Question = "***** Please specify what you have changed *****" Answer = InputBox(Question) For Each cell In Sheets("EmailList").Columns("B").Cells.SpecialCells(xlCellTypeConstants) If cell.Value Like "*@*" And LCase(cell.Offset(0, 1).Value) = "yes" Then Set iMsg = CreateObject("CDO.Message") With iMsg Set .Configuration = iConf .To = cell.Value .From = Environ("USERNAME") & "@abc.ca" .Subject = "File Updated" .HTMLBody = "The """ & lname & """ Worksheet has been updated." _ & "If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
" _ & "
" _ & "Changes: " & Answer _ & "
" _ & "
" _ & "Library Link" _ & "
" _ & "
" _ & SheetToHTML(ActiveSheet) .Send End With Set iMsg = Nothing End If Next cell Set iConf = Nothing Else Exit Sub End If Application.ScreenUpdating = True End Sub Public Function SheetToHTML(sh As Worksheet) 'Function from Dick Kusleika his site 'http://www.dicks-clicks.com/excel/sheettohtml.htm 'Changed by Ron de Bruin 04-Nov-2003 Dim TempFile As String Dim Nwb As Workbook Dim myshape As Shape Dim fso As Object Dim ts As Object sh.Copy Set Nwb = ActiveWorkbook For Each myshape In Nwb.Sheets(1).Shapes myshape.Delete Next TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" Nwb.SaveAs TempFile, xlHtml Nwb.Close False Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) SheetToHTML = ts.ReadAll ts.Close Set ts = Nothing Set fso = Nothing Set Nwb = Nothing Kill TempFile End Function
Sheets("Sheet2").Activate [A1].SelectWithout asking why I am "Selecting" vs. Activating the cells (there are several reasons for this particular thing I am doing), I'm just wondering is there a single line of code (versus my 2 line method of code written above) which will SELECT (not Activate) the given cell on a NON ACTIVE worksheet? In other words, this will NOT work if Sheet2 is NOT the active sheet:
'This will NOT work if Sheet2 is NOT the active sheet Sheets("Sheet2").[A1].SelectI'm hoping there is something like this syntax (above) that will work to activate and select a cell on a non-active sheet???