CODEWITHSUNDEEP
vbaexcel
peterreed
peterreed

Reputation: 33

macro error when copying print area and pasting to another sheet

I have created the code below which works up to a point then I get a run-time error 1004. Could anyone help? The error dialog box says that "PasteSpecial method of Range Class failed" and the first .PasteSpecial line is highlighted.

Sub CopyPO()
'
' CopyPO Macro
'

'
   Dim rngPrintArea As Range
   Set rngPrintArea = ActiveSheet.Range(ActiveSheet.PageSetup.PrintArea)
With Sheets("Inventory").Range("A" & Rows.Count).End(xlUp).Offset(1)
    .PasteSpecial Paste:=xlPasteColumnWidths
    .PasteSpecial Paste:=xlPasteValues
    .PasteSpecial Paste:=xlPasteFormats
End With
End Sub

To clarify, I am trying to copy the print area of the active sheet and paste it in the next available cell on the inventory sheet. This code works if the range is a specific range of cells (i.e. A1:P55) but I want to copy and paste the print area if possible.

Upvotes: 2

Views: 491

Answers (3)

M--
M--

Reputation: 28850

As you figured out yourself, you need to copy the range and then use PasteSpecial. So add rngPrintArea.Copy before your with-block.

Later, if you want to add the recently pasted cells to the print area in the "Inventory" worksheet then you need this:

Sub CopyPO()

   Dim rngPrintArea As Range
   Dim newRange As Range

   Set rngPrintArea = ActiveSheet.Range(ActiveSheet.PageSetup.PrintArea)
   rngPrintArea.Copy

   With Sheets("Inventory").Range("A" & Rows.Count).End(xlUp).Offset(1,0)
    .PasteSpecial xlPasteColumnWidths
    .PasteSpecial xlPasteValues
    .PasteSpecial xlPasteFormats
   End With

  Set newRange = Sheets("Inventory").Range(ActiveSheet.PageSetup.PrintArea)

  'If you want print area to be only the recent pasted range change the following line _
  'to commented line below;

  Set newRange = newRange.Resize(newRange.Rows.Count + _
                 rngPrintArea.Rows.Count, newRange.Columns.Count)

  'Set newRange = Sheets("Inventory").Range("A" & Rows.Count).End(xlUp).Offset(1,0). _
   'Resize(rngPrintArea.Rows.Count,rngPrintArea.Columns.Count)

  Sheets("Inventory").PageSetup.PrintArea = newRange.Address

End Sub

Upvotes: 1

acvbasql
acvbasql

Reputation: 109

There's no code indicating something is being copied. If you add a line to copy, it looks like that'll work.

Upvotes: -1

peterreed
peterreed

Reputation: 33

Like This

Sub CopyPO()

' CopyPO Macro

   Dim rngPrintArea As Range
   Set rngPrintArea = ActiveSheet.Range(ActiveSheet.PageSetup.PrintArea)
   rngPrintArea.Copy
   With Sheets("Inventory").Range("A" & Rows.Count).End(xlUp).Offset(1,0)
        .PasteSpecial xlPasteColumnWidths
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats
   End With
End Sub

However - I could do with adding the copied range to the print area in the Inventory... Do you know what I'd have to add to make that work? I have played around with it myself but haven't sussed that yet...

Upvotes: 1

Related Questions