August 27, 2010

Formatting table automatically in excel using macro and/or Visualbasic Editor

How to use macros in MicroSoft Excel 2007?

Follow these steps
  1. We need to enable Developer Toolbar with the following steps
    • Click the Office Button
    • Click Excel Options
    • Click Popular, select "Show Developer tab in the Ribbon" Checkbox
    Note: For reference you can use this link http://office.microsoft.com/en-us/excel-help/show-the-developer-tab-or-run-in-developer-mode-HA010173052.aspx  
  2. Edit Macro settings in Excel by doing the following steps
    • On the Developer tab, in the Code group, click Macro Security
      Note: If the Developer tab is not displayed, do step 1
    • In the Macro Settings category, under Macro Settings, click the option Disable all macros with Notification 
    Note: For reference you can use this link http://office.microsoft.com/en-us/excel-help/change-macro-security-settings-in-excel-HP010096919.aspx 
  3. Save the excel workbook
  4. Select Developer Ribbon Tab and Click on Visual Basic
  5. Microsoft Visual Basic Editor will be opened
  6. Click on Insert menu and then click on Module
  7. Copy and paste the following code
  8. Enter your data as a table format
  9. Select Developer Ribbon Tab and Click on Macros
  10. Now you can see one macro with the name Table_xls_Format
  11. Click on Run
  12. See the data now. It will be in table format with proper header and data alignment
  13. You can change the macro code by clicking edit button in macro window.
Macro Code:

Sub Table_Xls_Format()
    

    'Selecting Header Row
    Dim header_row As Range
    Set header_row = Range("a1", Range("a1").End(xlToRight))
  
    header_row.Select
  
    'Heading Bold, Aqua Color
    header_row.Font.Bold = True
    header_row.HorizontalAlignment = xlCenter
    header_row.Font.ColorIndex = 42
    header_row.Font.Italic = False
    header_row.Font.Underline = False
  
    'Selecting data

    Dim total_data As Range
    Set total_data = Range("A1").CurrentRegion
  
    total_data.Select
    

    total_data.Font.Bold = False
    total_data.Font.Italic = False
    total_data.Font.Underline = False
  
    'Font:Couriernew , 10
    total_data.Font.Name = "Courier New"
    total_data.Font.Size = 10
  
    'Full Border
    total_data.Borders.LineStyle = 1
  
    'Autofit Column Width
    Selection.Columns.AutoFit
  
    'Autosave the workbook
    ActiveWorkbook.Saved = True

End Sub

No comments:

Featured Post

Java Introdcution

Please send your review and feedback to psrdotcom@gmail.com