Need help making this Copy and paste code run smoother please.

ausrollo

New Member
Joined
Jan 27, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello All,

I have a spreadhseet which we work off to make quotes and i need to make this copy and paste code run a bit smoother, The sheets runs very laggy after I run the code. Any help would be greatful.

I have added a couple of screenshots of the two sheets to help understand a little better.


Sub write_12x12()
'
'
Dim cell As Range

Application.ScreenUpdating = False

Sheets("PS Data Sheet").Visible = True

Sheets("PS Data Sheet").Select
Range("B4:C4").Select
Selection.Copy

Sheets("Quote Sheet").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Sheets("PS Data Sheet").Select
Range("B5:B9").Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Quote Sheet").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 3).Select

Sheets("PS Data Sheet").Select
Range("E5:E9").Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Quote Sheet").Select
ActiveSheet.Paste
ActiveCell.Offset(8, -3).Select
Application.CutCopyMode = False

Sheets("PS Data Sheet").Visible = False

Application.ScreenUpdating = True

End Sub
 

Attachments

  • Capture.JPG
    Capture.JPG
    115.9 KB · Views: 3

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,026
Office Version
  1. 2016
Platform
  1. Windows
There is no need to select, activate worksheet etc in most cases

Range("A1").Copy Range("C1")
Range("A1:A3").Copy Range("D1:D3")
Range("A1:A3").Copy Range("D1"

to different worksheet
Sheets("PS Data Sheet").Range("A1").Copy Sheets("Quote Sheet").Range("C1")
Sheets("PS Data Sheet").Range("A1:A3").Copy Sheets("Quote Sheet").Range("D1:D3")
Sheets("PS Data Sheet").Range("A1:A3").Copy Sheets("Quote Sheet").Range("D1"

This will copy everything like formatting etc.

You can write a straight formula if you need just value without even copy which is faster.
Sheets("Quote Sheet").Range("C1") = Sheets("PS Data Sheet").Range("A1")
 

Watch MrExcel Video

Forum statistics

Threads
1,127,857
Messages
5,627,278
Members
416,236
Latest member
Lynchbox

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