Consulting

Results 1 to 5 of 5

Thread: Optimizing Copying Sheets & Printing

  1. #1
    VBAX Regular
    Joined
    Dec 2012
    Posts
    30
    Location

    Question 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. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,433
    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 - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,433
    the macro in the previous message has been modified several times since I posted it.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    VBAX Regular
    Joined
    Dec 2012
    Posts
    30
    Location
    Thanks, p45cal!

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

  5. #5
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,433
    Quote Originally Posted by mikeoly View Post
    Do you think printing & appending directly to PDF would run quicker than copying & pasting excel sheets?
    That depends on so many things...
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

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