Macro efficiency

[jab973]

This macro works like I want it to, however, it runs extrememly slow. Is there a better way to code this so it speeds up? The macro exports a client file containing graphs and other information. There are more pages than this, but coding just repeats. Any help would be appreciated.

Sub ClientCopy()
' Client  - graphs and reports

    Application.ScreenUpdating = False
    Application.CalculateFull
       
   ThisWorkbook.Worksheets("Co Cover").Cells.Copy
   Workbooks.Add
   wbname = ActiveWorkbook.Name
   ActiveSheet.Name = "Cover"
   Cells.Select
   ActiveSheet.Paste
   Application.CutCopyMode = False
   Range("a1").Select
        With ActiveSheet.PageSetup
        .PrintArea = "$A$1:$N$47"
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0#)
        .FooterMargin = Application.InchesToPoints(0#)
        .Zoom = False
        .CenterHorizontally = True
        .CenterVertically = True
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With

ThisWorkbook.Worksheets("Co Letter").Cells.Copy
  Workbooks(wbname).Sheets.Add after:=ActiveSheet
  ActiveSheet.Name = "Letter"
  Cells.Select
  ActiveSheet.Paste
  Application.CutCopyMode = False
  Range("a1").Select
    With ActiveSheet.PageSetup
        .PrintArea = "$A$1:$N$46"
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0#)
        .FooterMargin = Application.InchesToPoints(0#)
        .Zoom = False
        .CenterHorizontally = True
        .CenterVertically = True
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
 
ThisWorkbook.Worksheets("Template").Cells.Copy
   Workbooks(wbname).Sheets.Add after:=ActiveSheet
   ActiveSheet.Name = "Co RRQ "
   Cells.Select
   ActiveSheet.Paste
   With ActiveSheet.PageSetup
        .PrintArea = "$A$1:$N$46"
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0#)
        .FooterMargin = Application.InchesToPoints(0#)
        .Zoom = False
        .CenterHorizontally = True
        .CenterVertically = True
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
    ThisWorkbook.Worksheets("Co RRQ").ChartObjects("Chart 1").Copy
    Workbooks(wbname).Activate
    Range("C6").Select
    ActiveSheet.Pictures.Paste
    Range("a1").Select
     ThisWorkbook.Worksheets("Co RRQ").Range("C5:L5").Copy  ' co name
     Workbooks(wbname).Worksheets("RRQ ").Range("C5:L5").PasteSpecial (xlPasteValues)
      ThisWorkbook.Worksheets("Co RRQ").Range("C31:L42").Copy ' graph write up
      Workbooks(wbname).Worksheets("RRQ ").Range("C31:L42").PasteSpecial (xlPasteValues)
       ThisWorkbook.Worksheets("Co RRQ").Range("C44:L44").Copy ' pg number
       Workbooks(wbname).Worksheets("RRQ ").Range("C44:L44").PasteSpecial (xlPasteValues)
    Application.CutCopyMode = False

 

[Dan_W]

There likely is a way of reducing the time it takes to complete the task, but it's hard to give any guidance as to how without a bit more information. The part dealing with the sheet name Co RRQ seems quite intricate, so when you say 'export a client file', what do you mean? Also, how long is 'extremely slow'?

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, when posting vba code in the forum, please use the available code tags - it makes your code much easier to read & debug. My signature block below has more details. I have added the codes for you this time. ?

Are we missing some code at the end too?

 

[Dan_W]

I noticed that too. I suspect there's a lot more code we're missing... "pages" of code, in fact.

 

[jab973]

There likely is a way of reducing the time it takes to complete the task, but it's hard to give any guidance as to how without a bit more information. The part dealing with the sheet name Co RRQ seems quite intricate, so when you say 'export a client file', what do you mean? Also, how long is 'extremely slow'?

Thanks, I updated my account info (Windows / 365). It takes about 2-3 minutes to run the macro. The end goal is file with a cover sheet, client letter, about 20 pages of graphs, then some client specific financial information. The macro puts the infomation into a new excel file with the look we want. (we can't copy entire tabs as there are exra calculations/notes.) The "template" page has the formatting, company names, our company logo on it and we copy in a different graph to each one. Then it copies the specific graph title line (line 5), the graph write up (lines 31-42), and the page number (line 44). It just repeats the same code for all the sheets we are copying over. I created a lot of this using the macro recorder, so I know it's not the best way to do it with all the activating sheets.

 

[Dan_W]

You say it's copying the same code over and over again, but the code block dealing with sheet name "Co RRQ " does not do the same thing as the other code blocks above it. It seems, though that there may be room for some further speed gains. The following are general tips that look like they might be useful for your scenario and are provided in the absence of further code.

As a general rule, every time VBA code makes 'contact' with the worksheet, it slows things down. Here, given the nature of the task at hand, this seems as though it's largely unavoidable. You have already turned ScreenUpdating off, which was a good idea. You may want to try something (only marginally) more robust:

Public Sub LudicrousMode(ByVal Toggle As Boolean)
    Application.ScreenUpdating = Not Toggle
    Application.EnableEvents = Not Toggle
    Application.DisplayAlerts = Not Toggle
    Application.EnableAnimations = Not Toggle
    Application.DisplayStatusBar = Not Toggle
    Application.PrintCommunication = Not Toggle
    Application.Calculation = IIf(Toggle, xlCalculationManual, xlCalculationAutomatic)
End Sub

This does much the same thing, so frankly, it may not result in much of a performance boost than you already have, but it's worth a try. You would just need to put the above code in another module, and you can then replace the lines in your code:

Application.ScreenUpdating = False / Application.ScreenUpdating = True

with

LudicrousMode True

and at the end

LudicrousMode False

respectively.

There isn't any indication in your code above that you have any error handling routines used in the routine. Basically, if you encounter an error and stop the code, you will want to make sure that you turn LudicrousMode off (LudicrousMode False), otherwise you'll be scratching your head wondering why things aren't displaying properly, etc. BTW I didn't name the procedure

Some further pointers:

• If you've used the Macro Recorder, there will invariably be a lot of superfluous code - but it's difficult to say what is and what isn't necessary without seeing the entire code and the sample output. As a general rule, wherever the code ends with .Select and starts the next line with Selection., I find that the two lines can (more or less) be merged into one - there may be some adjustments you'll need to make to the code to make sure it works syntactically though.
  
  
• You don't need to copy across values from one sheet to the next. Towards the end of the code snippet you've provided above, you have code like:

ThisWorkbook.Worksheets("Co RRQ").Range("C44:L44").Copy ' pg number       
Workbooks(wbname).Worksheets("RRQ ").Range("C44:L44").PasteSpecial (xlPasteValues)

can be replaced with​

Workbooks(wbname).Worksheets("RRQ ").Range("C44:L44").Value = ThisWorkbook.Worksheets("Co RRQ").Range("C44:L44").Value

My comment above about limiting contact with the worksheet (i.e., in that it slows things down) also applies to the clipboard. The approach set out here avoids the clipboard entirely but just assigning the values of one range to the values of the corresponding range. It won't always be appropriate to avoid the clipboard, but insofar as you're simply transferring the values, this should be a quicker approach.

I suspect others might be able to give better/clear guidance, but this seems to me a good place to start. Hope this helps, but let me know your thoughts.