most efficient way of copying and paste a range as values VBA

[daveyc18]

right now im doing swtiching back and forth constantly...

'acct
Range("i2:i" & finalrow).Copy


Sheets("softek upload").Select


Range("a2").PasteSpecial xlPasteValues






'adp code
'


Sheets("index arb").Select


Range("a2:a" & finalrow).Copy
'


Sheets("softek upload").Select


Range("b2").PasteSpecial xlPasteValues

i originally ddid range("a2:a" & finalrow).copy sheets("softek upload").range("b2"), but this falls apart when the original data sheet has formulas...in the destination sheet, it gives me reference errors


so i need to paste as values

 

[DanteAmor]

Try this:

Sub test()
  Dim lr As Long
  lr = Sheets("index arb").Range("A" & Rows.Count).End(xlUp).Row
  Sheets("softek upload").Range("B2").Resize(lr - 1).Value = Sheets("index arb").Range("A2:A" & lr).Value
End Sub

 

[Akuini]

Try:

Dim ws As Worksheet

Set ws = Sheets("softek upload")

With Sheets("index arb")
    ws.Range("a2:a" & finalrow).Value = .Range("i2:i" & finalrow).Value
    ws.Range("b2:b" & finalrow).Value = .Range("a2:a" & finalrow).Value
End With

 

[william_man]

I think this should work:

Sheets("softek upload").Range("b2:b" & finalrow).Value = Range("a2:a" & finalrow).Value

 

[Leith Ross]

Hello daveyc18,

In VBA it is rare that you need to select an object before performing an action on it.

'acct - You may need to replace ActiveSheet with the Sheets("Sheet Name") 
Sheets("softek upload").Range("A2").Resize(finalrow - 1, 1).Value = ActiveSheet.Range("I2:I" & finalrow).Value




'adp code
Sheets("softek upload").Range("B2").Resize(finalrow - 1, 1).Value = Sheets("index arb").Range("A2:A" & finalrow).Value