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
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