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

torvaia

New Member
Joined
Feb 22, 2023
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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
VBA Code:
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
1677060809844.png
icon at the top of the window])
 
Upvote 0
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:


VBA Code:
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


VBA Code:
[CODE=vba]
[/CODE]

Now no error is shown, but it does not paste the cells..
 
Upvote 0
You do realise that you are only pasting the formats and not the data?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top