Error Message Visual Basic 400 - help!

Hi,
the problem is when you select your range before copying. I am not too sure why since I never do it this way.
try replacing the following part of your code

        Workbooks.Open fImport
            Sheets(1).Select
            Range("A4").Select
            Selection.Copy
        Windows(fnTemplate).Activate
            Sheets(1).Select
            Range("A12").Select
            ActiveSheet.Paste
        'Order Date
        Windows(fnImport).Activate
            Range("B4").Select
            Application.CutCopyMode = False
            Selection.Copy
..........
        Windows(fnImport).Activate
        ActiveWindow.Close savechanges:=False
        Windows(fnTemplate).Activate

instead of the entire copy paste part of your code try something like below

        Workbooks.Open fImport
        Set xlimp = Application.Workbooks(fnImport)
        Set xltemp = Application.Workbooks(fnTemplate)
        xltemp.Sheets(1).Range("A12").Value = xlimp.Sheets(1).Range("A4")
        xltemp.Sheets(1).Range("E12").Value = xlimp.Sheets(1).Range("B4")
        xltemp.Sheets(1).Range("G12").Value = xlimp.Sheets(1).Range("C4")
'.....
'Do the same for the rest of the items you need to copy

       xlimp.close False  'close the file without saving

hope it works for you!

Here is my Code..

Sub Auto_Open()
    Call FileToImport
End Sub

Sub btnImport_Click()
    Call FileToImport
End Sub

Sub FileToImport()
    Dim chkOKFileName As Boolean
    Dim fImport As Variant
    Dim fnImport As String
    Dim fnTemplate As String
    Dim fnNewName As String
    Dim WksImport As Worksheet
    Dim WksMain As Worksheet
    
    Set WksMain = ThisWorkbook.Worksheets("Sheet1")
    
    fImport = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select File To Import", , False)
    fnImport = GetFileName(CStr(fImport))
    fnTemplate = "Factory Master Packing List.xls"

    If TypeName(fImport) = "Boolean" Then
        'the user didn't select a file
        MsgBox ("File was NOT selected for import!")
        Exit Sub
    End If
    
        'Assign Import Worksheet to an Object variable
         Workbooks.Open fImport
         Set WksImport = ActiveWorkbook.Sheets(1)
         
        'Order ID Number
         WksMain.Range("A12") = WksImport.Range("A4")
        
        'Order Date
         WksMain.Range("E12") = WksImport.Range("B4")
        
        'Delivery Service
         WksMain.Range("G12") = WksImport.Range("C4")
        
        'Store Location
         WksMain.Range("A14") = WksImport.Range("B7")
         
        'Store
         WksMain.Range("A15") = WksImport.Range("B8")
         
        'Adress 1
         WksMain.Range("A16") = WksImport.Range("B10")
            
        'Address 2
         WksMain.Range("A17") = WksImport.Range("B11")
        
        'Address 3
         WksMain.Range("A18") = WksImport.Range("B12")
            
        'Address 4
         WksMain.Range("A19") = WksImport.Range("B13")
            
        'Address 5
         WksMain.Range("A20") = WksImport.Range("B14")
        
        'Address 6
         WksMain.Range("A21") = WksImport.Range("B15")
        
       'Close the Import Workbook - Don't Save Changes
        WksImport.Parent.Close savechanges:=False
        
        WksMain.Parent.Activate
        Range("C5").Select
        
       'Rmeove All Macros
        Set VBComps = ActiveWorkbook.VBProject.VBComponents

        For Each VBComp In VBComps
           Select Case VBComp.Type
              Case vbext_ct_StdModule, vbext_ct_MSForm, vbext_ct_ClassModule
                 VBComps.Remove VBComp
              Case Else
                 With VBComp.CodeModule
                    .DeleteLines 1, .CountOfLines
                 End With
           End Select
        Next VBComp
        
       'Delete "Import" Sheet and prompt for new file name
        Call DeleteSheet("IMPORT")
        chkOKFileName = Application.Dialogs(xlDialogSaveAs).Show("Enter new file name here")
        
       'Save the new workbook
        If chkOKFileName = True Then
            fnNewName = ActiveWorkbook.Name
        Else
            MsgBox ("File was NOT saved!")
        End If
    
End Sub

Leith,

I ran the macro and the first thing I get is this (attached) error. I click ok and it goes to my form and everything is populated correctly but it completely deletes the code from VB. So I have to close and reopen. ??? Please see attachment. I feel it is sooo close...

Originally Posted by Leith Ross

Hello EarlD,

If you read the comments I inserted into your code, you will find the section that deletes all the macro code from the workbook. It appears you weren't aware that this section of code did that. You can simply delete that section and all the macro code will remain intact. When you copy code, if there is part of it you don't understand then ask someone to explain it.

Delete these line from the macro

       'Rmeove All Macros
        Set VBComps = ActiveWorkbook.VBProject.VBComponents

        For Each VBComp In VBComps
           Select Case VBComp.Type
              Case vbext_ct_StdModule, vbext_ct_MSForm, vbext_ct_ClassModule
                 VBComps.Remove VBComp
              Case Else
                 With VBComp.CodeModule
                    .DeleteLines 1, .CountOfLines
                 End With
           End Select
        Next VBComp

Leith,

You are the MAN!! You are right! Its getting late and I didn't even see that down there. It works absolutely perfect now!! Thank You Leith and Thank You excelforum.com!!!