VBA Code - PasteSpecial Method of range failed (Error 1004)

[torvaia]

Hi, I am trying to copy paste one table from one workbook to another, but excel keeps telling me this error.

Here the code:

Dim LastRow2 As Long
Dim LastCol2 As Long
Dim LastModified As Date
Dim FilePath As String
Dim FileName As String
Dim FilePath2 As String
Dim FileName2 As String

FilePath = "C:\Users\bterranova\Mitsubishi Chemical Group\TM_OEM Development - General\CLM BDD Report\"
FileName = Dir(FilePath & "*.xlsx")

If FileName <> "" Then
MostRecentFile = FileName
MostRecentDate = FileDateTime(FilePath & FileName)
Do While FileName <> ""
If FileDateTime(FilePath & FileName) > MostRecentDate Then
MostRecentFile = FileName
MostRecentDate = FileDateTime(FilePath & FileName)
End If
FileName = Dir
Loop
End If

FileName = MostRecentFile

'Do While FileName <> ""
'Set wb = Workbooks.Open(FilePath & FileName)
'If wb.BuiltinDocumentProperties("Last Save Time") > LastModified Then
'LastModified = wb.BuiltinDocumentProperties("Last Save Time")
'FileName = wb.Name
'End If
'wb.Close False
'FileName = Dir
'Loop

Set wb = Workbooks.Open(FilePath & FileName)
Set ws = wb.Sheets("Page1")

LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
LastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

ws.Range(ws.Cells(1, 1), ws.Cells(LastRow, LastCol)).Copy


FilePath2 = "C:\Users\bterranova\Mitsubishi Chemical Group\TM_OEM Development - General\PM Templates\"
FileName2 = "BDD CLM Targets - for Graphs.xlsx"

Set wb2 = Workbooks.Open(FilePath2 & FileName2)
Set ws2 = wb2.Sheets("CLM Projects")

LastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
LastCol2 = ws2.Cells(1, ws2.Columns.Count).End(xlToLeft).Column


ws2.Range(ws2.Cells(1, 1), ws2.Cells(LastRow, LastCol)).ClearContents

ws2.Range(ws2.Cells(1, 1), ws2.Cells(LastRow, LastCol)).PasteSpecial xlPasteFormats

Application.CutCopyMode = False

wb.Close False
wb2.Close True

End Sub



Could you please help me?

Thanks

 

[MARK858]

First thing to try I think is to move the copy line immediately above the pastespecial line to see if you get the same result.
Also would also change your Pastespecial line to

ws2.Cells(1, 1).PasteSpecial xlPasteFormats

(please in future can you use code tags around your code to make it easier to read/copy [once you have pasted the code in the thread, select it and click the

icon at the top of the window])

 

[torvaia]

Hi Mark,

First of all, thanks for the tip and the reply. I tried to do both things you suggested, so that my end of code looks like this:

ws2.Range(ws2.Cells(1, 1), ws2.Cells(LastRow, LastCol)).ClearContents

ws.Range(ws.Cells(1, 1), ws.Cells(LastRow, LastCol)).Copy

ws2.Cells(1, 1).PasteSpecial xlPasteFormats

Application.CutCopyMode = False

wb.Close False
wb2.Close True

End Sub

[CODE=vba]

[/CODE]

Now no error is shown, but it does not paste the cells..

 

[MARK858]

You do realise that you are only pasting the formats and not the data?