Optimizing Copying Sheets & Printing

[mikeoly]

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]

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]

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

[mikeoly]

Thanks, p45cal!

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

[p45cal]

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

That depends on so many things...