0

I tried searching for this problem but couldn't find a solution.
I am doing a VBA script to copy some numbers from another spreadsheet (.xls) to a new one (.xlsx)

The numbers are all spread around the sheet, so I had made this script:

Sub ColetaDados(planilha, banco_base):

    'Function to copy and paste 

    Workbooks(planilha).ActiveSheet.Range("W1").Select
    ActiveCell.End(xlDown).Select
    ActiveCell.Offset(0, -12).Select
    'finds the last row I have to work with
    i = 0
    Do While i < 3
        ActiveCell.End(xlUp).Select
        i = i + 1
    Loop
    endrow = ActiveCell.Row

    'Starts doing the copying and pasting
    'This copies the header
    Range("V1").Select
    ActiveCell.End(xlDown).Select
    Range(ActiveCell, ActiveCell.Offset(0, -20)).Copy
    Workbooks(banco_base).Activate
    Cells(1, 1).Select
    ActiveSheet.Paste

    'this copies the first value selection
    Workbooks(planilha).Activate
    ActiveCell.Offset(2, 0).Select
    Range(ActiveCell, ActiveCell.Offset(0, -20).End(xlDown)).Copy
    Workbooks(banco_base).Activate
    Cells(2, 1).End(xlUp).Select
    ActiveCell.Offset(1, 0).Select

    ' since this sub goes in a For loop that iterates on 4 workbooks
    ' I created this if to avoid pasting over the wrong cell
    If IsEmpty(ActiveCell.Value) Then
        ActiveSheet.Paste
    Else
        ActiveCell.End(xlDown).Offset(1, 0).Select
        ActiveSheet.Paste
    End If

    ' This iteration copies over the other selections 
    Do While currentrow <= endrow
        Workbooks(planilha).Activate
        ActiveCell.End(xlDown).Select
        ActiveCell.Offset(5, 0).Select
        currentrow = ActiveCell.Row
        Range(ActiveCell, ActiveCell.Offset(0, -20).End(xlDown)).Copy
        Workbooks(banco_base).Activate
        Cells(2, 1).End(xlDown).Select
        ActiveCell.Offset(1, 0).Select
        ActiveSheet.Paste
    Loop
End Sub

And I get the following result: Every cell that is a number, gets pasted as text with three zeros on the end. Example: 11 becomes 11000

I can't figure out what is happening. I checked the .xls file and confirmed the values are correctly inserted, there are no space or other characters on the cell. I also tried using PasteSpecial xlPasteValues, but the same thing happens.

Any idea what might be going on?

L42
  • 19,427
  • 11
  • 44
  • 68
automa7
  • 494
  • 4
  • 15
  • Check this link and then look at the number format in your file to be sure it isn't set up this way. http://spreadsheetpage.com/index.php/tip/format_cells_to_display_in_thousands/ – Evan Callahan Feb 26 '18 at 21:23
  • Also, a huge note that it's best practice to [Avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BruceWayne Feb 26 '18 at 21:39
  • Hello @EvanCallahan, unfortunately the format is set as "0" :( – automa7 Feb 27 '18 at 11:44
  • Thank you, @BruceWayne! Any tips on how I could redo the first block without using select? – automa7 Feb 27 '18 at 11:45

0 Answers0