PasteSpecial - Value Only from temp file

TDPorter

New Member
Joined
Sep 13, 2011
Messages
12
I'm trying to copy values from a temp file and paste to my active workbook. The macro is copying the formulas. See code below:

Private Sub CommandButton1_Click()
Dim wbS As Workbook
Dim ws As Worksheet, wsA As Worksheet
Dim myFile As String
Set wsA = ThisWorkbook.Sheets("overview")
myFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
Set wbS = Workbooks.Open(myFile, False, True)
Set ws = wbS.Sheets("DAILY TALLY")
ws.Range("D31").Copy wsA.Range("O40")
ws.Range("E31").Copy wsA.Range("O41")
ws.Range("F31").Copy wsA.Range("O42")
ws.Range("G31").Copy wsA.Range("O43")
ws.Range("H31").Copy wsA.Range("O44")
ws.Range("I31").Copy wsA.Range("O45")
ws.Range("J31").Copy wsA.Range("O46")
ws.Range("K31").Copy wsA.Range("O47")
ws.Range("L31").Copy wsA.Range("O48")
ws.Range("M31").Copy wsA.Range("O49")
ws.Range("N31").Copy wsA.Range("O50")
ws.Range("O31").Copy wsA.Range("O51")
ws.Range("P31").Copy wsA.Range("O52")
ws.Range("Q31").Copy wsA.Range("O53")
ws.Range("R31").Copy wsA.Range("O54")
ws.Range("S31").Copy wsA.Range("O55")
wbS.Close False
End Sub
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board...

Try

ws.Range("D31:S31").Copy
wsA.Range("O40").PasteSpecial Paste:=xlPasteValues, Transpose:=True
 

TDPorter

New Member
Joined
Sep 13, 2011
Messages
12
So it worked perfectly!... Until I tried to use it for some additional cells I wanted to copy from the temp file to the active worksheet "overview". What am I doing wrong? The error I recieved is as follows: Run Time Error '1004' PasteSpecial method of Range class failed. See code below:

Dim wbS As Workbook
Dim ws As Worksheet, wsA As Worksheet
Dim myFile As String
Set wsA = ThisWorkbook.Sheets("overview")
myFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
Set wbS = Workbooks.Open(myFile, False, True)
Set ws = wbS.Sheets("DAILY TALLY")
ws.Range("D31:S31").Copy
wsA.Range("O40").PasteSpecial Paste:=xlPasteValues, Transpose:=True
ws.Range("U31:W31").Copy
wsA.Range("J48").PasteSpecial Paste:=x1PasteValues, Transpose:=True
wbS.Close False
 

Watch MrExcel Video

Forum statistics

Threads
1,119,022
Messages
5,575,622
Members
412,679
Latest member
TSpan
Top