MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Can the print area of one worksheet be copied into another worksheet? (using VBA)


Posted by Chris on December 17, 2001 9:04 AM

I've got a macro that copies data as values into another workbook. The only thing lacking in the new workbook is the print area of the original worksheet.

Is there a VBA command that allows you to copy the print area of your workbook and apply it to another?

Thanks in advance to anyone who can answer.


Posted by Tom Urtis on December 17, 2001 9:14 AM

You could try something simple like this.

Sub TransferPrintArea()
Application.ScreenUpdating = False
Sheets("Sheet2").Range("A1:D18").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$18"
ActiveSheet.Range("A1:D18").Copy _
Destination:=Sheets("Sheet3").Range("E6")
Sheets("Sheet3").PageSetup.PrintArea = "$E$6:$H$23"
Application.ScreenUpdating = True
End Sub

Any help?

Tom Urtis

Posted by Chris on December 17, 2001 10:16 AM

Tom,
Thanks for the answer. This will help me do what I need to do.