# Thread: Optimizing Copying Sheets & Printing

1. ## Optimizing Copying Sheets & Printing

Hello! I'm brainstorming improvements to a process in which exhibits are created using the code below. I know copying and pasting isn't very efficient, but its simple and works well for now. However, I don't think this is scalable. Does anyone have any thoughts on how to speed up this process? I have an exhibit that utilizes many formulas, which then gets copied into a new sheet and the values are hard-coded. The exhibit gets updated with various data as the code cycles through the For loop. Any thoughts?? Any feedback is appreciated. Thanks!

```    For i = 1 To Pcount

Sheets("References & Print Control").Select

If Cells(Prow + i, Pcol) = "Y" Then

Range("Level") = Cells(Prow + i, Pcol - 1)
tabname1 = Cells(Prow + i, Pcol + 1)
tabname2 = Cells(Prow + i, Pcol + 1) & " 2"

Sheets("Sheet2").Copy After:=Sheets(Sheets.Count)

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Sheet2 (2)").Name = tabname2

Sheets("Exhibit").Copy After:=Sheets(Sheets.Count - 1)

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Exhibit (2)").Name = tabname1
Else
End If
Next i```

2. try:
```With Sheets("References & Print Control")
For i = 1 To Pcount
If .Cells(Prow + i, Pcol) = "Y" Then
Range("Level") = .Cells(Prow + i, Pcol - 1)
tabname1 = .Cells(Prow + i, Pcol + 1)

Sheets("Sheet2").Copy After:=Sheets(Sheets.Count)
With ActiveSheet
.UsedRange.Value = .UsedRange.Value
.Name = tabname1 & " 2"
End With    'ActiveSheet

Sheets("Exhibit").Copy After:=Sheets(Sheets.Count - 1)
With ActiveSheet
.UsedRange.Value = .UsedRange.Value
.Name = tabname1
End With    'ActiveSheet
End If
Next i
End With    'Sheets("References & Print Control")```
This assumes that there is only one named range called Level in the workbook.

3. the macro in the previous message has been modified several times since I posted it.

4. Thanks, p45cal!

Do you think printing & appending directly to PDF would run quicker than copying & pasting excel sheets?

5. Originally Posted by mikeoly
Do you think printing & appending directly to PDF would run quicker than copying & pasting excel sheets?
That depends on so many things...

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•