Copying cell range to a different workbook

Question

I've got the below code to copy a range of different cells to the next available row in another workbook. The issue is that it's pasting the formula's not the values.

Sub SaveInlife()
    Dim wb As Workbook, NR As Long
    Set wb = Workbooks.Open("C:\Users\caseys\Downloads\Operations Score Card.xlsx")
    NR = Sheets("IN LIFE").Range("A" & Rows.Count).End(xlUp).Row + 1

    With ThisWorkbook.Sheets("REPORT")
    .Range("AB85").Copy Destination:=wb.Sheets("IN LIFE").Range("A" & NR)
    End With

    wb.Close savechanges:=True
End Sub

score 2 · Accepted Answer · answered Jun 04 '15 at 09:44

2

Figured it out

Replaced

.Range("AB85").Copy Destination:=wb.Sheets("IN LIFE").Range("A" & NR)

With

wb.Sheets("IN LIFE").Range("A" & NR).Value = .Range("AB85").Value

Seems to do what I need it to :)

answered Jun 04 '15 at 09:44

Shaun Casey

69
1
6

score 0 · Answer 2 · answered Jun 04 '15 at 09:36

0

not sure how to do it in one line of code but

.Range("AB85").Copy 
wb.Sheets("IN LIFE").Range("A" & NR).PasteSpecial xlValues

answered Jun 04 '15 at 09:36

99moorem

1,955
1
15
27

Copying cell range to a different workbook

2 Answers2