SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Good Morning,

I exported the contents of a SharePoint list to an Excel worksheet; however, I noticed the colors and formatting that have been applied to the data. This was done automatically as a part of the import process. I want to make changes to the worksheet’s formatting using a Macro without Excel severing the connection to the SharePoint list when I save it. Will that be possible? If so, can you please spell out in step by step detail what I need to do to make this happen? Thank you in advance.


share|improve this question

The easiest way is to record a new macro and then edit the macro to be re-usable.

Here is some basic, in-ellegant code that I used for a similar need:

ActiveSheet.ListObjects(1).TableStyle = ""  'change table style
Selection.AutoFilter    'remove auto-filter
Selection.Delete Shift:=xlShiftleft    'delete first column
Selection.Delete Shift:=xlShiftUp
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.FormulaR1C1 = "Task" & Chr(10) & "Status"
    ActiveCell.FormulaR1C1 = "Task" & Chr(10) & "Description"
    Selection.ColumnWidth = 30

Cells.Select    ' Select All
With Selection        'Format Table Cells
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlTop
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    .Font.Size = 9
End With
Rows("1:1").Select        'Align Headers to Bottom Center
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .Font.Bold = True
End With

ActiveWorkbook.Worksheets(1).Name = "ProjectSchedule-" & Date$      'ChangeTabName

Range("A1").Select     ' Place cursor at the top left
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.