Removing select/activate to speed up existing VBA

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
393
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,950
Office Version
  1. 365
Platform
  1. Windows
See if this works any better for you:
VBA Code:
    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

Active Member
Joined
Mar 23, 2017
Messages
393
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
See if this works any better for you:
VBA Code:
    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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,950
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Mar 23, 2017
Messages
393
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,950
Office Version
  1. 365
Platform
  1. Windows
I wonder if placing this line after your "End With" statement will help:
VBA Code:
Application.CutCopyMode = False
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
393
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
I wonder if placing this line after your "End With" statement will help:
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,975
Messages
5,599,129
Members
414,292
Latest member
kingshuk963

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top