Insert Names Across Rows Using Macro,Open Sheet is in the Home Position,Export Camera Picture,Convert Dates- excel Tips

Insert Names Across Rows Using Macro in excel

 standard module

 Sub Test()  
   Dim x As Worksheet  
   Application.DisplayAlerts = False  
   On Error Resume Next  
   Sheets("DataSheet").Delete  
   Names("Data").Delete  
   Application.DisplayAlerts = True  
   On Error GoTo 0  
   Sheets.Add().Name = "Datasheet"  
   Dim nm As Name  
   Dim i As Long  
   Dim avarSplit As Variant  
   Dim noPage As Variant  
   i = 1  
   Application.ScreenUpdating = False  
   With Sheets("DataSheet")  
   For Each nm In Names  
     .Cells(1, i) = nm.Name  
     .Cells(2, i) = "'" & nm.RefersTo  
     .Cells(3, i) = nm.Value  
     'noPage = Split( ?????  
     i = i + 1  
   Next nm  
   End With  
   Columns("A").Resize(, i).AutoFit  
 End Sub  
 '-  
 ' in workbook module edd:  
 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)  
 Call Test  
 End Sub  
 '--  

 Code to The Workbook Opens Each Sheet is in the Home Position But the Cursor is On particular Cell

 Sub ResetSheets()  
 Dim wks As Worksheet  
 Application.ScreenUpdating = False  
 For Each wks In Worksheets  
 Application.Goto Reference:=wks.Range("A1")  
 wks.Range("A3").Activate  
 Next wks  
 '--  
 Sheets("Cover").Select  
 MsgBox "All sheets have been reset."  
 End Sub  

Save Or Export Camera Picture, Worksheet, Or Range to JPG

I have a command button that first forces the workbook to be recalculated, then saves the camera picture image to a specified location.  At some point, I can anticipate needing to push this image embedded in an Outlook email using a distribution list.


How to Convert Dates using Excel code

 Sub SaveCameraPicture()  
 ' Description: Save shape as .JPEG  
 ' Potential problem: At next export image file name begins again from the 'CameraPic1'...  
 ' (To eliminate this problem, you can include the date and time the file name.)  
 '--  
 Dim shp As Shape  
   Dim objChart  
   Dim strFileName As String  
   Dim i As Integer  
   ' Customize base file name  
   Const csCPIC As String = "CameraPic"  
   '--  
 i = 0  
   For Each shp In ActiveSheet.Shapes  
     'Debug.Print "Name=" & shp.Name , "Type=" & shp.Type  
     ' Pic: Type = 13  
     If shp.Type = 13 Then  
       i = i + 1  
       shp.Copy  
       ' Create temporary embedded Excel Chart  
       ' (Customize Chart (picture) dimensions)  
       Set objChart = ActiveSheet.ChartObjects.Add(20, 20, 300, 300)  
       objChart.Activate  
       ActiveChart.Paste  
       ' Export Picture as JPEG  
       ActiveChart.Export _  
           Filename:=ActiveWorkbook.Path & _  
                Application.PathSeparator & _  
                csCPIC & Format(i, "000") & ".jpg", _  
                FilterName:="JPEG"  
       objChart.Delete  
       Set objChart = Nothing  
     End If  
   Next shp  
 End Sub  

In Excel, you have a very useful function: DateValue(). This will generate a Date value a formatted text string representing the date. So, by separating the three components in your text string (using standard text-manipulation functions), you can perform the conversion using a formula in a worksheet cell.
For example, suppose that you have one worksheet (named "Input") that has these 8-character date strings in column D, and another worksheet (named "Processed") where you want the corresponding dates in column D.

Then you might put the following formula in cell D2 of the "xyz" worksheet:

=DATEVALUE(MID(Input!D2, 5, 2) & "/" & RIGHT(Input!D2, 2) & "/" & LEFT(Input!D2, 4))

This will convert the text string to a number (for the date 20100611, the number will be 40340), which represents the date in Microsoft Excel's date/time representation. By setting the format of column D in the "xyz" worksheet to Date, you will see the date in a familiar representation (for example, "06/11/2010").

If you then copy the formula from cell D2 down column D, it will correctly convert the values from the successive cells of column D on the "Input" worksheet