Paste Special Values using

Question

Thanks to another user, I have been able to paste to offset range using destination, now I am trying to alter that so that it pastes as values so that my formatting and formulas don't carry along. The paste is currently causing #REF errors because of the formulas copying over.

Option Explicit Sub CopyPasteOffset() Dim OffsetRange As Long OffsetRange = Cells(78,1).Value Range("B65:F65").Copy _ Destination:=Sheets("stats FY2017").Cells(2+OffsetRange,2).PasteSpecial xlPasteSpecial End Sub

Gives me an "end of statement error"

Replace Destination:=Sheets("stats FY2017").Cells(2+OffsetRange,2).PasteSpecial xlPasteSpecial with Destination:=Sheets("stats FY2017").Cells(2+OffsetRange,2).PasteSpecial xlPasteValues — Shai Rado
– Shai Rado, Commented Apr 11, 2017 at 17:04

Subodh Tiwari sktneer · Accepted Answer · 2017-04-11 17:04:49Z

1

Try this...

Sub CopyPasteOffset() Dim OffsetRange As Long OffsetRange = Cells(78, 1).Value Range("B65:F65").Copy Sheets("stats FY2017").Cells(2 + OffsetRange, 2).PasteSpecial xlPasteValues End Sub

answered Apr 11, 2017 at 17:04

Subodh Tiwari sktneer

9,9862 gold badges20 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

S Beard Over a year ago

Thanks! Worked great. My xlPasteSpecial was the issue, now that I look at it. When I tried this initially that syntax is what gave me the error, its always simpler than it seems.

Subodh Tiwari sktneer Over a year ago

Glad it worked. You might have done something wrong with the syntax. :) Please take a moment to accept the answer to mark your question as Solved.

S Beard Over a year ago

Yup, was just waiting out the timer for it, had a couple minutes left on the timer before I could accept. The syntax was specifically the xlPasteValue item that caused my syntax, I am looking back at the code now

user4039065 · Accepted Answer · 2017-04-11 17:28:08Z

Here are some variations on PasteSpecial xlValues. Some include formatting but all take the values from any formulas in the original.

Sub CopyPasteOffset() Dim offsetRange As Long With Worksheets(1) offsetRange = .Cells(1, 1).Value 'raw value transfer - slightly faster but dates become integers, time becomes doubles 'and currency loses regional information With .Range("B1:F1") Worksheets(2).Cells(1 + offsetRange, 1).Resize(.Rows.Count, .Columns.Count) = .Value2 End With 'value transfer - dates, times and currency are retained With .Range("B1:F1") Worksheets(2).Cells(1 + offsetRange, 1).Resize(.Rows.Count, .Columns.Count) = .Value End With 'add a blank cell and paste the union - provides Value and Formatting transfer (strips formulas) '(assumes that Z1 is a blank cell that will not interfere with anything when pasted) .Range("B1:F1, Z1").Copy _ Destination:=Worksheets(2).Cells(1 + offsetRange, 1) 'PasteSpecial Values, optionally paste formats .Range("B1:F1").Copy With Worksheets(2).Cells(1 + offsetRange, 1) .PasteSpecial xlValues .PasteSpecial xlFormats End With End With End Sub

Collectives™ on Stack Overflow

Paste Special Values using

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related