Avoiding Clipboard with my existing VBA

[ItalianPlatinum]

Hello - looking to speed up my VBA any way i can because right now it clocks. one suggestion i heard is avoiding the clipboard. in one section of my code i have the below that i think i can easily eliminate the clipboard. but not sure how.

Workbooks("ALL.xls").Activate
Sheets("FT").Activate
FTALLV
ActiveWorkbook.Save
Range("A11:BD11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Workbooks("COMPARSION").Activate
Sheets("FT COMPARE").Activate
Range("B23").PasteSpecial Paste:=xlPasteValues

 

[Peter_SSs]

looking to speed up my VBA any way i can

Code is generally slowed considerably by 'activating' and 'selecting'. Most time that is not necessary.

I don't know whether you actually need to activate ALL.xls to start with as I don't know what is in FTALLV however these lines of code

Range("A11:BD11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Can be replaced by these (not exactly sure of the name of that second workbook - spelling or extension - so check), avoiding any activation or selection or copy.

Dim rws As Long

With Workbooks("ALL.xls").Sheets("FT")
  rws = .Range("A11:BD11").End(xlDown).Row - 10
  Workbooks("COMPARSION.xls").Sheets("FT COMPARE").Range("B23").Resize(rws, 56).Value = .Range("A11:BD11").Resize(rws).Value
End With

 

[ItalianPlatinum]

Code is generally slowed considerably by 'activating' and 'selecting'. Most time that is not necessary.

I don't know whether you actually need to activate ALL.xls to start with as I don't know what is in FTALLV however these lines of code

Range("A11:BD11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Can be replaced by these (not exactly sure of the name of that second workbook - spelling or extension - so check), avoiding any activation or selection or copy.

Dim rws As Long

With Workbooks("ALL.xls").Sheets("FT")
  rws = .Range("A11:BD11").End(xlDown).Row - 10
  Workbooks("COMPARSION.xls").Sheets("FT COMPARE").Range("B23").Resize(rws, 56).Value = .Range("A11:BD11").Resize(rws).Value
End With

Hi peter. so FTALLV is code that resides in Workbooks("ALL.xls"). and wasnt sure how to run that code without opening/activating the worksheet.

 

[Peter_SSs]

wasnt sure how to run that code without opening/activating the worksheet.

I'm still not sure either as I don't know what that code was. Anyway, the advice is: don't 'activate' or 'select' unless you absolutely need to.

 

[ItalianPlatinum]

I think that is my problem in my current code. I loop through a ton of data and I can see it clock. would you view the below "activate" as necessary? or do you think that could cause a performance issue?

Dim Cs$
Dim i As Long
Dim lr As Long

' Run Cs loop and paste to Post file
i = 1
Do Until Workbooks("COMPARSION").Sheets("Main").Range("Cs").Offset(i, 0) = ""
Cs = Workbooks("COMPARSION").Sheets("Main").Range("Cs").Offset(i, 0)
Workbooks("ALL.xlsm").Sheets("Sec").Range("SP0") = Cs
Application.Run "'ALL.xlsm'!SECALLV"
Application.ScreenUpdating = False
lr = Range("A11").End(xlDown).row
Range("A11:X" & lr).Copy
Sheets("Pre").Activate
With Sheets("Pre").Range("A" & rows.count).End(xlUp).Offset(1)
    .PasteSpecial Paste:=xlPasteValues
    .PasteSpecial Paste:=xlPasteFormats
End With
Application.CutCopyMode = False
i = i + 1
Loop

 

[Peter_SSs]

I cannot actually test as I do not have your full code, workbooks, worksheets or data. However, that code snippet only has one 'Activate' and to me it appears that that complete line Sheets("Pre").Activate
could be removed.

 

[ItalianPlatinum]

I cannot actually test as I do not have your full code, workbooks, worksheets or data. However, that code snippet only has one 'Activate' and to me it appears that that complete line Sheets("Pre").Activate
could be removed.

will it cause issues with the copy function above it? or can I eliminate the copy by going direct to where i want the data to go

 

[Peter_SSs]

will it cause issues with the copy function above it? or can I eliminate the copy by going direct to where i want the data to go

I'm not sure what you mean. Why don't you try it and see for yourself?

 

[ItalianPlatinum]

I'm not sure what you mean. Why don't you try it and see for yourself?

Alrighty then, will do. thanks for the help. much appreciated

 

[Peter_SSs]

No problem.