SOLVED

autofit problems in 2013 spreadsheet

New Contributor

I am using Excel version 2013. I have opened an xls spreadsheet generated by our business application.

I open the spreadsheet (excel says compatibility mode).   I creating a macro to do some formatting etc on the sheet.  I am using vba.

To develop the macro, I have it create a new worksheet as the first line in the sub.  I have it copy some data from sheet1 to sheet2.  I then try to autofit column "A" and it does not do it.

 

Ok, I close the macro and from the ribbon bar on home tab select Format -> autofit column width and it does not do it.   If I select Format -> Column Width and manually specify a size, it resizes.

 

In vba, I have ensured that I am trying to autofit the column on the correct worksheet.  I have included the meat of the sub defined.   Please be gentle.  I am NOT a vba programmer and am being forced to mod some macros define 12 years ago.

Thanks.

 

 

Dim worksheetName As String
  worksheetName = "Test"
  rawworksheetName = "SalesValue"
 
  'Do our work on a new worksheet
  If Not (Evaluate("ISREF('" & worksheetName & "'!A1)")) Then
   Sheets.Add(After:=Sheets(rawworksheetName)).Name = worksheetName
  End If
  
  Worksheets(rawworksheetName).Activate
  
  Application.ScreenUpdating = False
  Cells.Select                                  'Select entire worksheet
  Selection.MergeCells = False                  'Turn off any merged cells
  ActiveSheet.Cells.UnMerge
  Selection.Rows.AutoFit
  Selection.Columns.AutoFit
    
  'Copy header to new sheet.  And autofit.
  Range("A1").Select
  Selection.Copy Worksheets(worksheetName).Range("A1")
  Range("A2").Select
  Selection.Copy Worksheets(worksheetName).Range("A2")
  Worksheets(worksheetName).Range("A:A").Columns.AutoFit
  
  Dim rawdataStartRow As Integer
  Dim procdataStartRow As Integer
  
  rawdataStartRow = 5 'after title row and datetime row.  Item header string.
  procdataStartRow = 5 'after title row and datetime rows on new worksheet
  Range("A" & rawdataStartRow).Select
  Selection.Copy Worksheets(worksheetName).Range("A" & procdataStartRow)
  Range("A" & rawdataStartRow + 1).Select
  Selection.Copy Worksheets(worksheetName).Range("B" & procdataStartRow)
  
  'Copy Month names from raw (Sheet1) to processed (Test)
  Range("C5").Select
  Selection.Copy Worksheets(worksheetName).Range("D5")
   Range("E5").Select
  Selection.Copy Worksheets(worksheetName).Range("F5")
   Range("G5").Select
  Selection.Copy Worksheets(worksheetName).Range("H5")
   Range("K5").Select
  Selection.Copy Worksheets(worksheetName).Range("J5")
  'Second row of months
    Range("C6").Select
  Selection.Copy Worksheets(worksheetName).Range("L5")
    Range("E6").Select
  Selection.Copy Worksheets(worksheetName).Range("N5")
  Range("G6").Select
  Selection.Copy Worksheets(worksheetName).Range("P5")
  Range("K6").Select
  Selection.Copy Worksheets(worksheetName).Range("R5")
  'Third row of months
   Range("C7").Select
  Selection.Copy Worksheets(worksheetName).Range("T5")
    Range("E7").Select
  Selection.Copy Worksheets(worksheetName).Range("V5")
  Range("G7").Select
  Selection.Copy Worksheets(worksheetName).Range("X5")
  Range("K7").Select
  Selection.Copy Worksheets(worksheetName).Range("Z5")
  
  
  Range("A9").Select
  Selection.Copy Worksheets(worksheetName).Range("A7")
  Range("A10").Select
  Selection.Copy Worksheets(worksheetName).Range("B7")
  
  Worksheets(worksheetName).Activate
  Range("A7").EntireColumn.AutoFit
  Range("B7").EntireColumn.AutoFit
  
  Worksheets(rawworksheetName).Activate
  

Application.ScreenUpdating = True

 

 

1 Reply
best response confirmed by Hans Vogelaar (MVP)
Solution
OK. Figured it out. I was being stupid.
It copied the value AND formatting. Once I only copied the value it works. duh.