Copy and pasting between excel workbooks

Question

I have a Macro that copies and pastes from one excel document to another. For some reason, I had an error when using pastespecial immediately after copying and pasting from the other source doc. So as a workaround I just pasted normally, and then copied it again and then used pastespecial. My problem is that when running this Macro for some reason it adds a space to the end of the numbers turning them into text. Meaning that my graphs don't recognize them.

Workbooks.Open (fileLocation & "/" & fileName & fileType)
Worksheets(sourceWorksheet).Select
rowInUse = 46           'Add data row and name of sheet being imported into
mySheet = "sheet2"
pasteLocation = "D5"
lastColumn = ActiveSheet.Cells(rowInUse,     Columns.Count).End(xlToLeft).Column
Range(Cells(rowInUse, firstColumn), Cells(rowInUse, lastColumn)).Copy
ActiveWorkbook.Close SaveChanges:=False
Worksheets(mySheet).Select
Range(tempPasteLocation).Select
ActiveSheet.Paste
    Sheets(mySheet).Select
    Range(tempPasteLocation, Cells(tempRow, tempColumn + lastColumn)).Select
    Selection.Copy
    Range(pasteLocation).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
Range(tempPasteLocation, Cells(tempRow, tempColumn + lastColumn)).Select
Selection.ClearContents

Does anyone have any idea why this is happening or how to fix it?

Thank you

Answer 1

Perform a direct value transfer instead of Copy, PasteSpecial, Values.

Replace,

Sheets(mySheet).Select
Range(tempPasteLocation, Cells(tempRow, tempColumn + lastColumn)).Select
Selection.Copy
Range(pasteLocation).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True

With this,

with workSheets(mySheet)
    with .Range(tempPasteLocation, .Cells(tempRow, tempColumn + lastColumn))
        .Range(pasteLocation).resize(.rows.count, .columns.count) = .value2
    end with
end with