CODEWITHSUNDEEP
excelvba
Victor Lagrotta
Victor Lagrotta

Reputation: 3

How to replace select with range offset?

I have a macro that goes through a suspended list on a file and copies the data from a worksheets to another for each item in the worksheet.

Since there are 200 items in the list, and roughly 200 copy and paste operations, it's taking too long to run.

I'm trying to optimize, removing copy and paste, and select.

In my old code, I used range and offset, to repeat some operations, and now I can't use them, so I don't know how to rewrite the code.

This is the old code

Sheets("Motor").Select
Range("A140").Select
Selection.Copy
Sheets("Registros_Saidas_RS1").Select
MyRange.Offset(0, -3).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True

This is what I'm trying

Sheets("Registros_Saidas_RS1").MyRange.Offset(0, -3).Value = Sheets("Motor").Range("A140").Value

I get an error that I can't use this method.

Upvotes: 0

Views: 257

Answers (1)

chris neilsen
chris neilsen

Reputation: 53126

Remove the Selects like this

' acts on the ActiveWorkbook.  Is that what you want?
Sheets("Motor").Range("A140").Copy
' requires MyRange to have been previously set to the required range
MyRange.Offset(0, -3).PasteSpecial _
    Paste:=xlPasteValues, _
    Operation:=xlNone, _
    SkipBlanks :=False, _
    Transpose:=True

Then you can use

MyRange.Offset(0, -3).Value = _
Sheets("Motor").Range("A140") _
.Resize(MyRange.Rows.Count, MyRange.Columns.Count).Value

Upvotes: 1

Related Questions