Speed up the macro-page layout?

[flg8rgal]

[Aussiebear]

Please give a more detailed outline of the issue. Most people here are far too busy to spend time going through your workbook in an attempt to try and figure out what you are looking for.

[Blade Hunter]

There is almost 1,000 lines of code there.

To speed this up, here is the biggest one:

Stop doing this


cells.select
selection.delete


and do this instead

cells.delete


You don't need to select a cell / range to manipulate it.

Look at how much you can save by doing this:


Sheets("SuitcaseTotal").Select
Range("R1").Select
Selection.End(xlDown).Select
Range(Selection, "R1").Select
Selection.Copy
Sheets(SCACValue).Select
Range("D6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


becomes


Sheets("SuitcaseTotal").Range("R1:R" & Range("R" & Rows.Count).End(xlUp).Row).Copy
Sheets(SCACValue).Range("D6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


Have a go at removing as many select statements as you can and post back. I can guarantee if you make an effort to do this people will be more inclined to help you out but 1,000 lines of recorded code is not something we have the time to read.

Also a lot of that code is redundant, check out your with statements towards the end of the code which is doing the borders, Excel records EVERY setting for the borders when you don't need all of it. Try deleting some of the lines inside the with statements and see if it still works, you will be surprised how much you can remove.

Cheers

Dan

[Paul_Hossler]

Also turn off screen updating before and turn it on afterwards

Maybe the same for calcualtion:


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'long running code

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


Paul

[flg8rgal]

Wow, these are great suggestions. There was a glitch when I posted the original comment and it did not appear with the workbook, so I really appreciate you all taking the time to look through this. Most of what I know about VBA I learned from recording the steps first, so I have no doubt that there is a great deal of redundancy in the code. I'll use these suggestions, though I don't know how soon I can revist this. I'll be sure to post back when I do.

[mdmackillop]

Learn to use variables to refer to sheets, ranges etc. especially where there are multiple references to that object



Dim wsSuit As Worksheet
Dim wsAccru As Worksheet
Dim c As Range

Set wsSuit = Sheets("SuitcaseTotal")
Set wsAccru = Sheets("Accruals")
Set c = wsSuit.Range("Table_Query_from_REQ[[#Headers],[SCAC]]")

Range(c, c.End(xlDown)).Copy
wsAccru.Range("Q1").PasteSpecial Paste:=xlPasteValues