PDA

View Full Version : [SOLVED] Optimizing Copying Sheets & Printing



mikeoly
05-14-2019, 08:22 AM
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

p45cal
05-14-2019, 09:07 AM
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.

p45cal
05-14-2019, 09:14 AM
the macro in the previous message has been modified several times since I posted it.

mikeoly
05-14-2019, 12:11 PM
Thanks, p45cal!

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

p45cal
05-14-2019, 12:48 PM
Do you think printing & appending directly to PDF would run quicker than copying & pasting excel sheets?

That depends on so many things...