Need help with VBA Error

[sparkytech]

I have the code below, and get a Run-time error '1004' "PasteSpecial method of Range class failed" at:

DestWkb.Sheets(1).Cells(1, 1).PasteSpecial

The original sheet has an "Export" button which runs this macro, and exports to another workbook. I didn't write this code, but modified both workbooks structure. Workbook 1 row 5 and down used to export to "Export" workbook, starting at row 1 and down. Now it exports the same table starting at row 5, but is supposed to paste at row 5 on the export workbook. Any ideas why this error?

Sub ExportCleanExcel(control As IRibbonControl)
'Exports the information in the master sheet to a chosen excel file; will just move
'the data without macros or other extra features

Dim FileToOpen As Variant
Dim DestWkb As Workbook
Dim MasBotRow As Long
Dim MasLasCol As Long

'Turn off screen updates to improve performance
Application.ScreenUpdating = False

'Allows user to select a file to export to using the traditional open file window
FileToOpen = Application.GetOpenFilename("All Excel Files (*.xls?), *.xls?", , "Please export file")

'If the user selects cancel when chosing a file to open FileToOpen is set to FALSE
'Using this we can check if a file was actually selected before continuing
If FileToOpen <> False Then
'Find the bottom row and last column of the table on the master sheet

MasBotRow = Sheet1.Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
MasLasCol = Sheet1.Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column

'Copies the entire table from the master sheet
Sheet1.Range(Cells(1, 1), Cells(MasBotRow, MasLasCol)).Copy

'Open the file that was selected and then set that workbook as DestWkb
Set DestWkb = Application.Workbooks.Open(FileToOpen)

'Pastes the table into the destination file in the A1 cell of the first sheet
DestWkb.Sheets(1).Cells(1, 1).PasteSpecial

'Turn off alerts to avoid clipboard notices when closing the file
Application.DisplayAlerts = False

'Close the data file
DestWkb.Close True

'Turn back on alerts after closing the file
Application.DisplayAlerts = True
End If

Application.CutCopyMode = False
Range("A4").Select ' btn set cursor location to cell A4

'Turn back on screen updates
Application.ScreenUpdating = True

End Sub

 

[BSALV]

you have to be more specific, how do you want to paste it ? Just the values, the formulas, ....
see XlPasteType enumeration (Excel)

so try for example
DestWkb.Sheets(1).Cells(1, 1).PasteSpecial xlpasteall

 

[sparkytech]

you have to be more specific, how do you want to paste it ? Just the values, the formulas, ....
see XlPasteType enumeration (Excel)

so try for example
DestWkb.Sheets(1).Cells(1, 1).PasteSpecial xlpasteall

Should only copy values, copying from row 5 down and pasting row 5 down (I realize the Cells 1,1 needs to be changed). The way the code is currently written (with only PasteSpecial) was working at some point recently. I'm not sure what changed to make it fail?

 

[BSALV]

okay, that argument is optional, as i read the instructions.
So it has to do with the range.

* are you sure the 1st sheet is a worksheet and not a chart ?
modify DestWkb.workSheets(1).Cells(1, 1).PasteSpecial

* is that sheet protected against changes ?
Open it manually and copy&paste it yourself. Any problems ?

 

[Norie]

What happens if you move the code around a bit so the workbook is opened, then the range is copied and pasted?

'Open the file that was selected and then set that workbook as DestWkb
Set DestWkb = Application.Workbooks.Open(FileToOpen)

'Copies the entire table from the master sheet
Sheet1.Cells(1,1).Resize(MasBotRow, MasLasCol).Copy

'Pastes the table into the destination file in the A1 cell of the first sheet
DestWkb.Sheets(1).Cells(1, 1).PasteSpecial xlPasteValues

 

[BSALV]

isn't it "thisworkbook.sheet1.cells(1,1)...." otherwise you're in the wrong workbook ?

 

[Norie]

No, ThisWorkbook.Sheet1, will give you a compile error - Method or data member not found.

The object Sheet1 will refer to the worksheet with the codename Sheet1 in the same workbook the code is in.

 

[BSALV]

Yes ? It felt like it would refer to the active workbook. Sorry, my mistake.

 

[sparkytech]

What happens if you move the code around a bit so the workbook is opened, then the range is copied and pasted?

'Open the file that was selected and then set that workbook as DestWkb
Set DestWkb = Application.Workbooks.Open(FileToOpen)

'Copies the entire table from the master sheet
Sheet1.Cells(1,1).Resize(MasBotRow, MasLasCol).Copy

'Pastes the table into the destination file in the A1 cell of the first sheet
DestWkb.Sheets(1).Cells(1, 1).PasteSpecial xlPasteValues

I just moved the code as suggested, but the same error pops up.

 

[sparkytech]

okay, that argument is optional, as i read the instructions.
So it has to do with the range.

* are you sure the 1st sheet is a worksheet and not a chart ?
modify DestWkb.workSheets(1).Cells(1, 1).PasteSpecial

* is that sheet protected against changes ?
Open it manually and copy&paste it yourself. Any problems ?

On the "Master" sheet (data to be copied from), there is a graph and some other objects in rows 1-3. Row 4 starts a table which runs down the sheet. On the "Export" sheet, the sheet is structured exactly the same, and the data should be pasted starting at row 5. The "Export" sheet is not protected, and I can copy from the Master and paste into the Export without any issues.