Copy and Paste value from one workbook to another

hajiali

Active Member
Joined
Sep 8, 2018
Messages
330
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
Sub PullFinalBidData()
  Dim wb As Workbook, fullpath As String
  Dim wt As Worksheet
  Set wt = Sheets("BID")
  With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Filters.Add "Excel Files", "*.xlsm", 1
    If .Show <> -1 Then Exit Sub
    fullpath = .SelectedItems.Item(1)
    Set wb = Workbooks.Open(fullpath)
    wb.Sheets("RA Print").Range("E5:S303").Copy ThisWorkbook.Sheets("TOOL DATA").Range("B3")
    wb.Sheets("RA Print").Range("E304:S604").Copy ThisWorkbook.Sheets("TOOL DATA").Range("B303")
    wb.Close False
  End With
End Sub

Using the above code it copies the data but uses just paste. How would I be able to make this paste Value.
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,770
Office Version
  1. 2013
Platform
  1. Windows
Like this

VBA Code:
Sub PullFinalBidData2()
  Dim wb As Workbook, fullpath As String
  Dim wt As Worksheet
  Set wt = Sheets("BID")
  With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Filters.Add "Excel Files", "*.xlsm", 1
    If .Show <> -1 Then Exit Sub
    fullpath = .SelectedItems.Item(1)
    Set wb = Workbooks.Open(fullpath)
    wb.Sheets("RA Print").Range("E5:S303").Copy
    ThisWorkbook.Sheets("TOOL DATA").Range("B3").PasteSpecial xlPasteValues
    wb.Sheets("RA Print").Range("E304:S604").Copy
    ThisWorkbook.Sheets("TOOL DATA").Range("B303").PasteSpecial xlPasteValues
    wb.Close False
  End With
End Sub
 

hajiali

Active Member
Joined
Sep 8, 2018
Messages
330
Office Version
  1. 2016
Platform
  1. Windows
Thanks JLGWhiz that works great.

I am getting the Microsoft Excel Message about "There is a large amount of information on the clipboard. Do you want to be able to paste this information into another program Later? with option Yes No and Cancel.

Is there a way to prevent that message to appering?
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,770
Office Version
  1. 2013
Platform
  1. Windows
Rich (BB code):
 ThisWorkbook.Sheets("TOOL DATA").Range("B303").PasteSpecial xlPasteValues
 Application.CutCopyMode = False
  wb.Close False
add the line in blue as shown. That should clear the clipboard.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,118
Messages
5,576,202
Members
412,706
Latest member
msousa25
Top