VB code to change a column of text numbers to Numbers

[seansr]

I have a macro in a spreadsheet that deletes all on one sheet, then opens another spreadsheet, copies it and then goes back to the first sheetwhere it should then do some Vlookups and create an order based on an orer being geneterated by another system but in totally the wrong format

The code I have is:

Sheets("Symphony Order").Select
  Cells.Select
  Selection.Clear
  Workbooks.Open Filename:="C:\Symphony Exports\Report_Order.xls"
  Cells.Select
  Selection.Copy
  Windows("Anchor 4th Nov .xls").Activate
  Sheets("Symphony Order").Select
  Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  Application.DisplayAlerts = False
  Windows("Report_Order.xls").Activate
  ActiveWindow.Close
  Windows("Anchor 4th Nov .xls").Activate
  Sheets("TUCOStMarys").Activate
  Range("D1").Select

I want all the numbers in Column Hin the Anchor 4th Nov .xls sheet which are copied as Text to be converted to Numbers, otherwise my lookups are not working in the TUCOStMarys sheet

 

[Hui]

What about just changing the line
From:
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

To:
Selection.PasteSpecial Paste:=xlPasteAsValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

 

[Deepak]

As @Hui suggested you are free to try the same meanwhile you might interested to share the workbook so that we can diagnosis it. TEXT type data could converted to numeric based on requirement either in xl formula itself or by VBA.

I have alter your current code...

With Sheets("Symphony Order")
    .UsedRange.Cells.Clear
        Workbooks.Open Filename:="C:\Symphony Exports\Report_Order.xls"
            ActiveSheet.UsedRange.Copy
            .PasteSpecial Paste:=xlPasteAsValues
'            .Paste
        ActiveSheet.Parent.Close False
End With
    Sheets("TUCOStMarys").Activate
    Range("D1").Select

 

[seansr]

I have tried both of your suggestions but on both I get a Compile Error: Viarable not Defined on the xlPasteAsValues part

 

[Deepak]

My bad!!!

.Range("A1").PasteSpecial xlValues