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
精彩评论