PasteSpecial causing macro to fail.. why?

Question

The below Macro opens an external workbook, unhides a particular sheet and reads a couple of text values into variables, creates a new sheet in the current workbook based on those variable values, copies the contents of a worksheet in that workbook, then pastes it into the new one.

However, when I use .Paste it works fine, but doesn't retain the formatting and only pastes in the text.

If I try to correct this and use

.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

It fails.

Why is this?

Sub addsheet()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

filename = Sheets("Instructions").Range("C13").Value
report = "report.xlsx"
report_filepath = "\\xxx\xxx\xxx\report.xlsx"
Dim report_name As String

Workbooks.Open Filename:=(report_filepath)

    Windows(report).Activate
    Sheets("Info").Visible = True
    Sheets("Info").Activate
    report_month = Range("B5").Text
    report_year = Range("B4").Text
    Sheets("Report").Range("A1:AJ498").Copy
    Windows(filename).Activate
    Windows(report).Close
    Set newsheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
    report_name = (report_month & " " & report_year)
    newsheet.Name = (report_name)
    Sheets("Instructions").Range("C15").Value = (report_name)
    Sheets(report_name).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
    Sheets("Instructions").Activate


End Sub

I see two problems. Your clipboard might be getting cleared. So move the copy statement before the paste statement. Also Please avoid the use of `.Select/.Activate` You may see [THIS](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros/10718179#10718179) If you are still stuck then please provide the following details and I can give you an example `1` Name of the workbook from where you are running the code. `2` Where do the sheets like "Info", "Report" etc reside? I mean which workbook? — Siddharth Rout, Aug 11 '14 at 08:13
`filename` holds the name of the current workbook. `report` holds the name of the workbook I am trying to copy from. `Info` is a hidden sheet in `report` which I am unhiding, `Report` is a worksheet inside `report` & `Instructions` is a worksheet inside `filename`. — Keva161, Aug 11 '14 at 08:22

score 1 · Accepted Answer · edited May 23 '17 at 10:33

1

Like I mentioned in my comments, you should avoid the use of .Select/.Activate. You may want to see THIS

If I change your code and use objects then your code may look like this.

Also move the copy code before the paste and include DoEvents between then so that Excel gets enough time to place the data onto the clipboard.

Try this

UNTESTED

Sub addsheet()
    Dim report_name As String
    Dim Filename As String, report_filepath As String, report As String
    Dim thisWb As Workbook, thatWb As Workbook
    Dim thatWs As Worksheet, NewSheet As Worksheet
    Dim report_month As String, report_year As String, report_name As String

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Set thisWb = ThisWorkbook

    With thisWb
        Set NewSheet = .Sheets.Add(After:=.Sheets(.Worksheets.Count), Count:=1, Type:=xlWorksheet)
    End With

    report_filepath = "\\xxx\xxx\xxx\report.xlsx"

    Set thatWb = Workbooks.Open(report_filepath)
    Set thatWs = thatWb.Sheets("Info")

    With thatWs
        report_month = .Range("B5").Value
        report_year = .Range("B4").Value
    End With

    report_name = report_month & " " & report_year

    NewSheet.Name = report_name

    thisWb.Sheets("Instructions").Range("C15").Value = report_name

    thatWb.Sheets("Report").Range("A1:AJ498").Copy

    DoEvents

    NewSheet.Range("A1").PasteSpecial Paste:=xlPasteValues, _
                                      Operation:=xlNone, _
                                      SkipBlanks:=False, _
                                      Transpose:=False

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

    thisWb.Sheets("Instructions").Activate

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

edited May 23 '17 at 10:33

Community

1
1

answered Aug 11 '14 at 08:39

Siddharth Rout

147,039
17
206
250

This runs :) However, I am still only getting the text copy and pasted, not the formatting. – Keva161 Aug 11 '14 at 09:09
I have edited the post above. Try it now. You may have to refresh the page :) – Siddharth Rout Aug 11 '14 at 09:13
Perfect :) This works great, however, is there a way to copy and paste the whole sheets data contents? Including imbedded pictures and such? – Keva161 Aug 11 '14 at 09:19
Why do that not create a copy of the sheet instead? – Siddharth Rout Aug 11 '14 at 09:20
@Keva161: See [THIS](http://stackoverflow.com/questions/19584497/how-to-replicate-a-sheet-using-vba-macro-not-copy-replicate) – Siddharth Rout Aug 11 '14 at 09:21
Thanks but I don't want to copy and paste the whole sheet. The sheet contains some formulas, so I only need the values. – Keva161 Aug 11 '14 at 09:50
1

Once you copy the sheet. Do a copy all cells and then paste values :) – Siddharth Rout Aug 11 '14 at 09:54
you don't need a pastesepecial if you want to copy all (including comments, formating, pictures, validation...) : `thatWb.Sheets("Report").Range("A1:AJ498").Copy NewSheet.Range("A1")` . So basicaly its just `A.copy B`, where A and B are ranges – Patrick Lepelletier Aug 11 '14 at 13:21
PS: this method is said to not use the clipboard, thus faster and less problems... – Patrick Lepelletier Aug 11 '14 at 13:23

DeanOC · Answer 2 · 2014-08-11T08:44:02.073

0

According to the Office help file, when you use the Worksheet.PasteSpecial method "You must select the destination range before you use this method.".

If you change

Sheets(report_name).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

to

Sheets(report_name).Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

then it will work.

It would appear that the .Paste method will default to "A1" if no range is specified.

Further investigation shows that if you use .PasteSpecial without any parameters, i.e. Sheets(report_name).PasteSpecial then it will work without specifying a range. But if you include the parameters then it appears that you must specify the range

edited Aug 11 '14 at 08:44

answered Aug 11 '14 at 08:21

DeanOC

7,142
6
42
56

I am getting the error `Run-time error '1004' PasteSpecial method Range class failed` – Keva161 Aug 11 '14 at 08:24
Can you post your code? I tried this before answering, and it worked fine for me. – DeanOC Aug 11 '14 at 08:37

PasteSpecial causing macro to fail.. why?

2 Answers2