Speed up my VBA. Small code

[ItalianPlatinum]

Hello - I believe my VBA is clocking because of my large dataset and me copying/pasting the below. is anyone able to assist of how to optimize it?

Application.Run "'_ALL .xlsm'!ALLV"
Application.ScreenUpdating = False
lr = Range("A11").End(xlDown).row
Range("A11:X" & lr).Copy
With Sheets("Pre").Range("A" & rows.count).End(xlUp).Offset(1)
    .PasteSpecial Paste:=xlPasteValues
    .PasteSpecial Paste:=xlPasteFormats
End With
Application.CutCopyMode = False

 

[davesexcel]

Application.Run "'_ALL .xlsm'!ALLV"

Maybe this is slowing you down?

 

[ItalianPlatinum]

Maybe this is slowing you down?

It could but I can't avoid it. I need it to run my VBA

 

[davesexcel]

How many rows could there be?

And what is the code you are not showing?

 

[ItalianPlatinum]

It could but I can't avoid it. I need it to run my VBA

The data could be 100k+ rows with 20 columns. This code I do not actually have access to Application.Run "'_ALL .xlsm'!ALLV" but it is needed to run in my process

 

[MARK858]

If you replace the code that you are showing with the code below and run it, what does the message box state?

    Application.Run "'_ALL .xlsm'!ALLV"
    Application.ScreenUpdating = False
    Dim t As Double
    t = Timer
    lr = Range("A11").End(xlDown).Row
    Range("A11:X" & lr).Copy
    With Sheets("Pre").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .PasteSpecial Paste:=xlPasteValues
        .PasteSpecial Paste:=xlPasteFormats
    End With

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Code took " & Format(Timer - t, "0.00 secs")

 

[ItalianPlatinum]

If you replace the code that you are showing with the code below and run it, what does the message box state?

    Application.Run "'_ALL .xlsm'!ALLV"
    Application.ScreenUpdating = False
    Dim t As Double
    t = Timer
    lr = Range("A11").End(xlDown).Row
    Range("A11:X" & lr).Copy
    With Sheets("Pre").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .PasteSpecial Paste:=xlPasteValues
        .PasteSpecial Paste:=xlPasteFormats
    End With

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Code took " & Format(Timer - t, "0.00 secs")

darn only 3.61 seconds. the file _ALL ends up being 200,000 kb file when i save and open it multiple times. i suppose could be that. alright then

 

[MARK858]

You're welcome (although not of much help if you can't access the other code).