CODEWITHSUNDEEP
vbaexcel
Keva161
Keva161

Reputation: 2693

PasteSpecial causing macro to fail.. why?

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

Upvotes: 0

Views: 9099

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149335

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

Upvotes: 1

DeanOC
DeanOC

Reputation: 7282

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

Upvotes: 0

Related Questions