none
Error 1004 (bogus) only on one machine. RRS feed

  • Question

  • I get this error:Run-time error '1004':To copy all cells from another worksheet to this worksheet, make sure you paste them into the first cell (A1 or R1C1).


    I have a piece of code on a spreadsheet that imports a .xls file we download from the bank and rearranges it to form an up loadable file for our accounting software.

    A little while ago it stopped working.  Select column F:F, then insert, the insert fails with an error saying if you are trying to copy an entire spreadsheet. you must paste starting in A1.

    This error message does not apply, I am not copying or pasting, simply trying to insert a blank column.   I thought perhaps there was some garbage data on the edges the insert would try to push off, so deleted everything past the range of data,  no luck. 

    I started troubleshooting on my own system and it works just fine.  Every time.

    I have completely removed office using Microsoft's tool and reinstalled it, makes no difference.

    Before I call Microsoft tech support and spend hours on the phone with people who seem to have no actual experience with the programs they are trying to support,  does anyone have any suggestions?

    Here is the code.  Bold and Underlined is where this fails:

    Private Sub Workbook_Open()
    '
    ' Macro1 Macro
    '
     Dim NewFileName As String
     Dim lastrow As Integer
     
     ChosenFile = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file")
    If ChosenFile = False Then
    ' They pressed Cancel
    MsgBox "Stopping because you did not select a file"
    Exit Sub
    Else
    Debug.Print ChosenFile
    Workbooks.Open FileName:=ChosenFile
    End If


    Cells.Select
    Selection.Copy

    Workbooks("Comerica credit card upload macro.xlsm").Activate
    Worksheets("Sheet1").Activate
    Cells(1, 1).Select

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

     
     Cells.Select
     
     With Cells
     
     Range("a1").Select
     
    lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
    End With


     'remove Comerica formatting
     Cells.Select
     Selection.NumberFormat = "General"
        With Selection
            .HorizontalAlignment = xlGeneral
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
    '
    'create invoice# and cleanup
    Columns("f:f").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

        Range(Cells(2, 6), Cells(lastrow, 6)).Select
        
        Selection.NumberFormat = "General"
       
        Application.CutCopyMode = False
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F1").Select
        ActiveCell.FormulaR1C1 = "Invoice"
        Range("F2").Select
        ActiveCell.FormulaR1C1 = "=RC[-3]&"" ""&RC[-2]&"" ""&RC[-1]"
        Range("F2").Select
        Selection.AutoFill Destination:=Range(Cells(2, 6), Cells(lastrow, 6)), Type:=xlFillDefault
        
        Columns("F:F").Select
        
             
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        
        Columns("c:e").Select
    Selection.Delete
        
        'Clean  description
        
        Columns("k:k").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

        Range(Cells(2, 11), Cells(lastrow, 11)).Select
        
        Selection.NumberFormat = "General"
       
        Application.CutCopyMode = False
        Range("K1").Select
        ActiveCell.FormulaR1C1 = "Memo"
        Range("K2").Select
        ActiveCell.FormulaR1C1 = "=RemoveLineFeeds(RC[-1])"
        Range("K2").Select
        Selection.AutoFill Destination:=Range(Cells(2, 11), Cells(lastrow, 11)), Type:=xlFillDefault
        
        Columns("K:K").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        
        Columns("j:j").Select
    Selection.Delete

    Columns("D:D").Select
    Selection.Delete
        
        Cells(1, 1).Select
        
        ActiveCell.FormulaR1C1 = "1"
        Selection.Copy
        Range(Cells(2, 11), Cells(lastrow, 11)).Select
        
           Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
           Application.CutCopyMode = False 'Kill copy mode
           
          NewFileName = InputBox(Prompt:="Filename should contain an extension.", _
              Title:="ENTER DESTINATION FILE NAME", Default:="FileName")
              
               If NewFileName = "FileName" Or _
               NewFileName = vbNullString Then

               Exit Sub
               End If
               
    '            Application.Sheets(Sheet_Name).Select
      
      
      NewFileName = "p:\Comerica_Cards\" & NewFileName & ".csv"
      Debug.Print NewFileName
      
      
        Application.DisplayAlerts = False
       ActiveWorkbook.SaveAs FileName:=NewFileName, FileFormat:=xlCSV, CreateBackup:=False

     '    ActiveWorkbook.Save
     '    ActiveWindow.Close
      '   Windows(oldfilename).Activate

    Application.DisplayAlerts = True

    'Workbooks("Comerica credit card upload macro.xlsm").Activate

    'ActiveWorkbook.Close SaveChanges:=False
        
    End Sub

    Monday, August 28, 2017 10:13 PM

All replies

  • Are you using Excel 2016?

    A recent upgrade to Excel 2016 added functionality to retain the copied data for later use instead of dumping it as soon as other edits etc take place. An identified problem is if you copy data and then paste it and then at a later time you try to insert rows/columns then it thinks you want to insert the last copied data.

    On a worksheet cancel the Copy with Esc key

    In VBA code, after the paste use the following line (or immediately before any insert etc). The code is the equivalent of Esc key on the worksheet to cancel the Copy.

    Application.CutCopyMode = False

    I see that you do have code to copy and paste an entire worksheet so insert the code after the Paste

     

     Cells.Select
     Selection.Copy

     Workbooks("Comerica credit card upload macro.xlsm").Activate
     Worksheets("Sheet1").Activate
     Cells(1, 1).Select

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

    Application.CutCopyMode = False

    Alternatively you can insert the line immediately before the Insert column F

     'create invoice# and cleanup

    Application.CutCopyMode = False

     Columns("f:f").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove


    Regards, OssieMac

    Tuesday, August 29, 2017 7:27 AM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,
    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, August 29, 2017 8:46 AM
  • Nope.  That did not make a difference.  Still works on mine, still fails on her machine.

    Phil

    Tuesday, August 29, 2017 8:27 PM
  • Do you have all updates on the computer that fails because after the initial release of the update that changed how copy retains the copy there was another update to fix initial problems with the changed feature.

    To check for updates

    • Save and close all open documents and office applications (Including Office Outlook)
    • Open Excel at a new blank workbook
    • Select File -> Account -> Update options -> Update now.

    Regards, OssieMac

    Tuesday, August 29, 2017 9:09 PM
  • On the machine in question,  I completely removed and reinstalled O365 from scratch yesterday morning.  That is about as up to date as I can get.

    Phil

    Tuesday, August 29, 2017 9:12 PM
  • On the machine in question,  I completely removed and reinstalled O365 from scratch yesterday morning.  That is about as up to date as I can get

    That is a maybe. Try the update anyway. If it is all up to date then it will simply tell you it is up to date.

    Regards, OssieMac

    Tuesday, August 29, 2017 10:02 PM
  • Hi Phil,

    If cancel copy could not work for you, I suggest you copy Column 6 and then insert new column. Since copied range is a column, it could be paste into the column too. After inserting column, you need clear content in the insert column to make it as a "blank" column which looks like a newly inserted column.

    Here is the example.

    Columns("f:f").Select
    
       Columns("f:f").Copy
    
       Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
       Columns("f:f").Clear

    Best Regards,

    Terry

    Wednesday, August 30, 2017 5:26 AM