Removing select/activate to speed up existing VBA

[ItalianPlatinum]

Hello - The below VBA works BUT it is a tremendous resource grab. I think due to the fact i am selecting, copy and pasting for my loop. is there a more efficient way to just go direct on the copy and paste?

' Run loop and paste to Pre file
i = 1
Do Until Workbooks("COMPARSION").Sheets("Main").Range("Special").Offset(i, 0) = ""
Special = Workbooks("COMPARSION").Sheets("Main").Range("Special").Offset(i, 0)
Workbooks("ALL.xls").Sheets("Sec").Range("1VALUE") = Special
Application.Run "'ALL.xls'!SECALL"
Range("A11:X11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks("PRE.xlsx").Activate
With Sheets("PRE").Range("A" & rows.count).End(xlUp).Offset(1)
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
End With

i = i + 1
Loop

 

[Joe4]

See if this works any better for you:

    Dim i As Long
    Dim lr As Long
    
    Application.ScreenUpdating = False

'   Run loop and paste to Pre file
    i = 1
    Do Until Workbooks("COMPARSION").Sheets("Main").Range("Special").Offset(i, 0) = ""
        Special = Workbooks("COMPARSION").Sheets("Main").Range("Special").Offset(i, 0)
        Workbooks("ALL.xls").Sheets("Sec").Range("1VALUE") = Special
        Application.Run "'ALL.xls'!SECALL"
        lr = Range("A11").End(xlDown).Row
        Range("A11:X" & lr).Copy
        Workbooks("PRE.xlsx").Activate
        With Sheets("PRE").Range("A" & Rows.Count).End(xlUp).Offset(1)
            .PasteSpecial Paste:=xlPasteValues
            .PasteSpecial Paste:=xlPasteFormats
        End With
        i = i + 1
    Loop

    Application.ScreenUpdating = True

Also, please use the Code Tags when posting your code! It maintains all spacing and makes code easier to read and decipher.
(See here if you are unsure how to do that: How to Post Your VBA Code)

 

[ItalianPlatinum]

See if this works any better for you:

    Dim i As Long
    Dim lr As Long
   
    Application.ScreenUpdating = False

'   Run loop and paste to Pre file
    i = 1
    Do Until Workbooks("COMPARSION").Sheets("Main").Range("Special").Offset(i, 0) = ""
        Special = Workbooks("COMPARSION").Sheets("Main").Range("Special").Offset(i, 0)
        Workbooks("ALL.xls").Sheets("Sec").Range("1VALUE") = Special
        Application.Run "'ALL.xls'!SECALL"
        lr = Range("A11").End(xlDown).Row
        Range("A11:X" & lr).Copy
        Workbooks("PRE.xlsx").Activate
        With Sheets("PRE").Range("A" & Rows.Count).End(xlUp).Offset(1)
            .PasteSpecial Paste:=xlPasteValues
            .PasteSpecial Paste:=xlPasteFormats
        End With
        i = i + 1
    Loop

    Application.ScreenUpdating = True

Also, please use the Code Tags when posting your code! It maintains all spacing and makes code easier to read and decipher.
(See here if you are unsure how to do that: How to Post Your VBA Code)

It seems to have stopped here. .PasteSpecial Paste:=xlPasteValues; saying copy area and paste area aren't the same size error?

 

[Joe4]

OK, I was trying to figure out what was going on just by looking at your code, but I think I am going to need an explanation of what your data looks like, and exactly what you want this code to do.
Can you provide that information?
You can post images from your Excel file using this: XL2BB - Excel Range to BBCode

 

[ItalianPlatinum]

It seems to have stopped here. .PasteSpecial Paste:=xlPasteValues; saying copy area and paste area aren't the same size error?

I since resolved it. Doing the attached is it still utilizing the clipboard? I am getting warnings as my clipboard is full. was trying to also avoid that

 

[Joe4]

I wonder if placing this line after your "End With" statement will help:

Application.CutCopyMode = False

 

[ItalianPlatinum]

I wonder if placing this line after your "End With" statement will help:

Application.CutCopyMode = False

Perfect, looks to have purged the message. it was asking if i wanted to keep the data on the clipboard or remove it to free up memory.