Receive Error 1004 while running section of Code Selection.copy,

Question

Heading ## I was able to get the selection to select active Cell a1 but now receive error 1004 Application Object-defined error, AT THE SECTION OF CODE <Sheets.Add before:=Workbooks(myFile).Sheets("Make DMS Report")>

Please see the code below.
The code filters the data from Agile export and filters the product Part number and associates the manufacture part number with the part. 'Start code

  <Sub ImportAgileBOM()
'GoTo test1

    Dim FullFileName As String
    Dim myFile As String
    Dim FileFormat As String
    Dim rng As Range
    
    
    'Open .cvs worksheet and convert to text format.

    myFile = ActiveWorkbook.Name
  
    
    FullFileName = Application.GetOpenFilename("Text files , *.csv; *.txt,Excel files (*.xls*), *.xls*", 2, "Select Agile Mfr BOM Report", , False)

    If FullFileName = "False" Then
        Application.DisplayAlerts = True
        End
    End If
    

'This section converts the .cvs and renames workboot to text 
    If Right(FullFileName, 4) = ".csv" Or Right(FullFileName, 4) = ".txt" Then
        FileCopy FullFileName, FullFileName & "importtemp.txt"
        FileFormat = "Text"
        Workbooks.OpenText Filename:=FullFileName & "importtemp.txt", _
            DataType:=xlDelimited, ConsecutiveDelimiter:=False, _
            Tab:=False, Semicolon:=False, _
            Comma:=True, Space:=False, Other:=False, _
            FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2))

    Else
        FileFormat = "Excel"
        Workbooks.Open Filename:=FullFileName
    End If
'Make Active Range
    Set rng = ActiveSheet.Range("A1")
    'Range("A1").Activate
    
    If FileFormat = "Text" Then
        If ActiveCell <> "Manufacturer BOM Report" Then
            MsgBox "Input file not in Manufacturer BOM Report format"
            ActiveWindow.Close
            Application.DisplayAlerts = True
            End
        End If
    
    ElseIf ActiveCell.Offset(0, 1) <> "Manufacturer BOM Report" Then
        MsgBox "Input file not in Manufacturer BOM Report format"
        ActiveWindow.Close
        Application.DisplayAlerts = True
        End
    End If
    
    'FullFileName = ActiveWorkbook.Name
    'Sheets(ActiveSheet.Name).Copy Sheets.Add Sheet.before:=Workbooks(myFile).Sheets("Make DMS Report")
    'Windows(FullFileName).Activate
    'ActiveWindow.Close savechanges:=False
'Copy data to secound sheet    
    FullFileName = ActiveWorkbook.Name
    Cells.Select
    Selection.Copy
    Workbooks(myFile).Activate
    Sheets.Add before:=Workbooks(myFile).Sheets("Make DMS Report")
    ActiveSheet.Paste>

'I receive the error when trying to add the created sheet to the Active sheet. If you could assist in this issue it would be appreciated

`Cells` is a function that requires a row and column parameter. Something like `Cells(1,1)` — Warcupine, Mar 14 '22 at 14:08
Obligatory https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Warcupine, Mar 14 '22 at 14:08

score 0 · Answer 1 · answered Mar 15 '22 at 17:46

0

I am now receiving an error at <Range("A1"). select> the error message is Run-time error 1004 Application-defined Or Object-defined error, I don't understand why I am receiving this error, just selecting a cell

answered Mar 15 '22 at 17:46

david Diodati

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Mar 15 '22 at 20:08

Receive Error 1004 while running section of Code Selection.copy,

Heading ## I was able to get the selection to select active Cell a1 but now receive error 1004 Application Object-defined error, AT THE SECTION OF CODE <Sheets.Add before:=Workbooks(myFile).Sheets("Make DMS Report")>

1 Answers1