VBA Question (copy/paste)

[sparkytech]

I seem to be hitting a roadblock with code that previously worked fine. I have an "Export" button to copy / paste data from a table into another "Export" workbook which is identical, minus some unneeded sheets. I am getting an error on the paste portion of the code, so I created a new blank workbook to test paste the data in, which works fine. Basically, it errors on paste to an identical renamed workbook, but pastes fine to a blank workbook.

Here is my code:

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 choosing 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

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

'Copies rows A5 to W to last row and also Z5 to last row on sheet {skips "X:Y"}

Sheet1.Range("A5:W" & MasBotRow).Copy
DestWkb.Sheets(1).Range("A5:W" & MasBotRow).PasteSpecial xlPasteValues

Sheet1.Range("Z5:Z" & MasBotRow).Copy
DestWkb.Sheets(1).Range("X5:X" & MasBotRow).PasteSpecial xlPasteValues

'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

'Turn back on screen updates
Application.ScreenUpdating = True

MsgBox "Export Complete"

End Sub

I get an error "Run-time error '1004' PasteSpecial method of Range class failed" on this line:

DestWkb.Sheets(1).Range("A5:W" & MasBotRow).PasteSpecial xlPasteValues

Any ideas what is wrong?

 

[NdNoviceHlp]

ThisWorkbook.Sheets("Sheet1").Range("A5:W" & MasBotRow).Copy

HTH. Dave

 

[sparkytech]

ThisWorkbook.Sheets("Sheet1").Range("A5:W" & MasBotRow).Copy

HTH. Dave

Thanks! Alright, the original error is gone, and then it was "Run-time error 9, Subscript out of Range". I had to change "Sheet1" to "Master" (the name of the sheet 1).

ThisWorkbook.Sheets("Sheet1").Range("A5:W" & MasBotRow).Copy

Getting closer... now the error is on the

DestWkb.Sheets(1).Range("A5:W" & MasBotRow).PasteSpecial xlPasteValues

line and is again "Run-time error '1004' PasteSpecial method of Range class failed".

 

[sparkytech]

Thanks! Alright, the original error is gone, and then it was "Run-time error 9, Subscript out of Range". I had to change "Sheet1" to "Master" (the name of the sheet 1).

ThisWorkbook.Sheets("Sheet1").Range("A5:W" & MasBotRow).Copy

Getting closer... now the error is on the

DestWkb.Sheets(1).Range("A5:W" & MasBotRow).PasteSpecial xlPasteValues

line and is again "Run-time error '1004' PasteSpecial method of Range class failed".

Disregard. Closed everything, reopened, and its now working. Thanks!

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Dave

 

[sparkytech]

You are welcome. Thanks for posting your outcome. Dave

I think I am getting closer to finding the issue, as it happened again. There is nothing worse than *fixing* a problem without knowing why, as it usually rears its ugly head at some time in the future. My "copy" code searches for the bottom row. There are helper cells way down in the sheet that are not part of the table that dont need to be copied. I *think* when the paste occurs, it doesn't know where to paste these. I changed the code to MasBotRow = 2500 and this *seems* to work every time. Thanks again to the code gurus for all the assistance!