Using a Macro to format spreadsheet - warning, there's a twist number of columns varies

[MagicMan]

Hello again everyone!

I'm creating a workbook for someone so all they have to do is press buttons to operate macro's to do their stuff.

Unfortunately, the number of tabs that are imported from Column C can vary between 2, 27 or even more. It's not fixed. However, When the data is imported it resembles the file 20150330 Example Worksheet Without formatting

What I want to do is make it achieve 20150330 Example Worksheet With formatting

How do I achieve the following:
Set Column Width for Column A (User ID) to Auto
Set Column Width for Column B (User Names and Descriptions) to 40 and wrap text
Set Column Width for all the columns up to the "User Parent Company" (in this instance Column G) to 3
Set Column Width for "User Parent Company", "SME Approver", "SME Approver Email", "SME Approver Comments", SME Approver Enter Removal Ticket No" (in this instance Columns G, H, I, L and M) to 20
Set Column Width for "User is a current employee ...." and "Date" (in this instance Columns J and K) to 15
Set Cells A1 and B1 to centre left
Rotate text in cells between (but not including) "User Names and Descriptions" and "User's Parent Company" by 90 degrees (in this instance Cells C1 - F1)
Centre all the columns between (but not including) "User Names and Descriptions" and "User's Parent Company" (in this instance columns C - F)

Please remember, after column B the number of columns prior to "User's Parent Company" may vary

Any help would be greatly appreciated.

Thanks all

[jindon]

Change

    With Sheets("User to System Matrix").Cells(1).Resize(UBound(a, 1), UBound(a, 2))
        .CurrentRegion.ClearContents
        .Value = a
        Sheets("Verification Details").Cells(1).CurrentRegion.Rows(1).Copy .Cells(1, .Columns.Count + 1)
        With .Columns(.Columns.Count + 1).Offset(1).Resize(.Rows.Count - 1)
'            .Formula = "=iferror(vlookup(a2,'Known Users'!a:b,2,false),"""")"
            .Formula = "=if(vlookup(a2,'Known Users'!a:b,2,false)="""","""",vlookup(a2,'Known Users'!a:b,2,false))"
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlExpression, Formula1:="=" & .Cells(1).Address(0, 0) & "="""""
            .FormatConditions(1).Interior.Color = vbRed
        End With
    End With

to

    With Sheets("User to System Matrix").Cells(1).Resize(UBound(a, 1), UBound(a, 2))
        .CurrentRegion.ClearContents
        .Value = a
        With .Cells(1, 3).Resize(, UBound(a, 2) - 2)
            .Orientation = 90
            .Columns.AutoFit
        End With
        Sheets("Verification Details").Cells(1).CurrentRegion.Rows(1).Copy
        With .Cells(1, .Columns.Count + 1)
            .PasteSpecial 8
            .PasteSpecial
        End With
        With .Columns(.Columns.Count + 1).Offset(1).Resize(.Rows.Count - 1)
'            .Formula = "=iferror(vlookup(a2,'Known Users'!a:b,2,false),"""")"
            .Formula = "=if(vlookup(a2,'Known Users'!a:b,2,false)="""","""",vlookup(a2,'Known Users'!a:b,2,false))"
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlExpression, Formula1:="=" & .Cells(1).Address(0, 0) & "="""""
            .FormatConditions(1).Interior.Color = vbRed
        End With
    End With