CODEWITHSUNDEEP
vbaexcelcopy-paste
Shaun Casey
Shaun Casey

Reputation: 69

Copying cell range to a different workbook

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

Upvotes: 1

Views: 276

Answers (2)

Shaun Casey
Shaun Casey

Reputation: 69

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 :)

Upvotes: 2

99moorem
99moorem

Reputation: 1983

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

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

Upvotes: 0

Related Questions