Apr 04 2022 12:05 PM
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
Apr 04 2022 01:18 PM
SolutionApr 04 2022 01:18 PM
Solution