Excel vba Macro RRS feed

  • Question

  • I have an excel workbook I populate from sql server. The data comes across in text.  A few of the tabs in the excel are summarized pivot tables.

    Since the data comes accorss as text  - it won't sum on what is supposed to be a numeric column.  I am tring to implement a macro to convert the text to numbers when the excel workbook opens. 

    There are 6 detail tabs, on 3 of the tabs the column that is supposed to be colmn S and the other three are supposed to be coumn W. 

    The attempt at a macro is below - but I am not having any luck with this.  Can you help?

    Private Sub Workbook_Open()
     Dim ws As Worksheet
     For Each ws In ActiveWorkbook.Sheets
     If ws.Name = "Sheet1" Or ws.Name = "Sheet2" Or ws.Name = "Sheet3" Then
       With ws.Columns("S2")
            .NumberFormat = "0"
            .Value = .Value
        End With
     End If
     Next ws
    End Sub


    • Edited by siera_gld Tuesday, March 31, 2020 4:02 PM
    Tuesday, March 31, 2020 4:02 PM