Excel(2007) VBA COpy & Paste

I'm trying to update a price list from one workbook to another except I don't want to send the Customer the macros from the Master Price List...

The Master will eventually have one tab for each vendor.

The Master Copy is will be sent to the customer without the macros..

Here's my code as of now..

I keep getting an Error 1004 Paste Method failed

'Now copy from the Update Master to the Cust Master...
mWrk = "A1:Z" & Trim(Str(TotRows))   <---TotRows is the total # of rows used



Application.CutCopyMode = XLCopy
Worksheets(WhichFile).Range(mWrk).Copy  <-- WhichFile has the value of the sheet name..


Dim mXLCopy As Workbook
Set mXLCopy = Workbooks.Open(ThisWorkbook.Path & "\Customer Master Price.xlsx")

' See if the sheet is already there. if so delete it.
Dim IsThere As Boolean
IsThere = False
For x = 1 To mXLCopy.Sheets.Count
    If UCase(mXLCopy.Sheets(x).Name) = UCase(WhichFile) Then
        IsThere = True
    End If
Next x
Application.DisplayAlerts = False
If IsThere Then
    mXLCopy.Sheets(WhichFile).Delete
End If

'
'Now add it & activate it..
mXLCopy.Sheets.Add().Name = WhichFile
mXLCopy.Activate

With mXLCopy.Sheets(WhichFile)
    Range(mWrk).PasteSpecial xlPasteAll, xlPasteSpecialOperationNone  <- Fails here
End With

Application.DisplayAlerts = True

mXLCopy.Save
mXLCopy.Close
Set mRange = Nothing
Set mXLCopy = Nothing
开发者_开发知识库

Any ideas anyone? GO ahead & make fun of me if you must, but I need an answer & none of mine are working...

The reason this is happening is because your mXLCopy.Sheets(WhichFile).Delete command is clearing the clipboard.

You will have to rearrange the code in such a manner that you delete and re-create the sheet first and only then copy the range to be pasted.

Hope this helps, Be happy