Run-time Error/Crash during Paste Formats

KGee

Well-known Member
Joined
Nov 26, 2008
Messages
530
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I'm getting the error below when running this code and it is failing on the last line (paste formats). I use the same basic code on a number of reports but it is only failing on one file. I tried recording the steps manually again via the macro recorder and it also crashed when I tried to do the paste formats. It may have something to do with our move to Office 365 but as I mentioned the same code still works on other files. What I need to do is open the report, retrieve data for the period in question, then run the macro to save a standalone version of the report that can be emailed. The range being copied has collapsed columns/rows and this machine still has Windows 7 if that makes a difference :(. I'm sure there is also a more efficient way to do the copy/paste sequence so feel free to make suggestions on that.

Run-time error '-2147417848 (80010108)':

Method 'PasteSpecial' of object 'Range' failed



VBA Code:
Application.ScreenUpdating = False

wbkSrcName = ActiveWorkbook.Name

rowEnd = Worksheets("Report").Cells(Rows.Count, "C").End(xlUp).Row

Set rngCopy = Sheets("Report").Range("B4:X" & rowEnd)

rngCopy.SpecialCells(xlCellTypeVisible).Copy

Workbooks.Add

wbkOutName = ActiveWorkbook.Name

Range("A1").PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
     SkipBlanks:=False, Transpose:=False
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,218
Members
417,131
Latest member
Seanr19871

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
Top