Copy/pasting from one workbook to another - Out of range error

Hello all,

I've been having a little trouble with something that should be simple, and I keep getting an error which I can't figure out.

I have a file with contacts in the "Supplier Contacts" tab. Under certain conditions, I'd like the range A1:F1000 in the current file to be copied to a master file which the user selects using an "Open File" dialogue. The master file is identical in structure to the file I'm working on (same sheets). Up to here, all is ok. But when I get to the pasting in the master file part, I get an Out of range error, and it's driving me nuts.

Here is my code (messy, I apologise beforehand):

Sub SaveContacts()

 If Sheets("Supplier Contacts").Range("Q1") <> Sheets("Supplier Contacts").Range("R1") Then 
 'Get Master file location
     Dim MasterFile As Variant
 'Where is the Master file? Open file dialogue
    MasterFile = Application.GetOpenFilename()
    If MasterFile = False Then 'If user presses "Cancel"
         MsgBox "No file selected."
         Exit Sub
    End If
'Open Master
    Workbooks.Open (MasterFile)
'Copy A2 to F1000 in source file
    Workbooks(ThisFile).Sheets("Supplier Contacts").Range("A2:F1000").Copy
 'Paste to master in "Supplier Contacts" sheet
    Workbooks(MasterFile).Sheets("Supplier Contacts").Range("A2;F1000").PasteSpecial xlPasteValues
 'Save master without prompt
    Workbooks(MasterFile).Save
    ActiveWorkbook.Saved = True
 'Close master without prompt
    Workbooks(MasterFile).Close
 'Come back here
    Workbooks(ThisFile).Sheets("Supplier Contacts").Activate
    MsgBox "Copied to master file"
    Range("A2").Select
 'If no changes were made...
    Else
        MsgBox "No changes were made"
    End If
End Sub

When the macro gets to:

Workbooks(MasterFile).Sheets("Supplier Contacts").Range("A2;F1000").PasteSpecial xlPasteValues

I get the "Subscript out of range" error, and I can't understand why.

I don't know if there are any errors after that line, BTW, as I can't get past the error above

Can anyone here see the error that is eluding me?

Thank you in advance for any advice.

-Isa

Workbooks("MasterFile").Sheets

Use quotation marks around MasterFile is at least one correction. Not certain if there are more.

How about

Range("A2;F1000")

Your other range references use ':'

Thanks to all of you guys, you're a great group of people.

The variable to refer to the workbook being opened resolved the problem. I also corrected the semicolon instead of the colon in my range, thanks for pointing that out.

It all makes perfect sense now, but I was pulling my hair out yesterday. Thanks for restoring my sanity

-Isa