VBA code to copy and paste by values from one Excel workbook to another
 

Would anyone have a VBA function that copies everything that's on one
Excel workbook and paste just the values to another Excel workbook? I
just want to copy the values and not the references. It would be one
sheet to another sheet.

I've looked on the newsgroup, but am not able to find anything. Any
help is much appreciated.

Thanks,
Nhien

VBA code to copy and paste by values from one Excel workbook to another
 

Here's an example:

Sub test()
Dim wb As Workbook, ws As Worksheet

Set ws = ThisWorkbook.ActiveSheet
Set wb = Workbooks.Open("C:\filename.xls")

wb.Sheets(1).Range("A1").Copy
ws.Range("A1").PasteSpecial xlPasteValues

wb.Close Savechanges:=False
End Sub

--
Dan

VBA code to copy and paste by values from one Excel workbook to another
 

On Aug 13, 5:37 pm, "Dan R." wrote:
Here's an example:

Sub test()
Dim wb As Workbook, ws As Worksheet

Set ws = ThisWorkbook.ActiveSheet
Set wb = Workbooks.Open("C:\filename.xls")

wb.Sheets(1).Range("A1").Copy
ws.Range("A1").PasteSpecial xlPasteValues

wb.Close Savechanges:=False
End Sub

--
Dan

How do I reference the Activesheet that I'm pasting to, if the sheet
is in another excel file? I'm calling the function from outside the 2
excel files. Do I open the workbook first. What is the code to make
that sheet active before I paste?

Nhien

VBA code to copy and paste by values from one Excel workbook to another
 

On Aug 14, 10:58 am, Nhien wrote:
On Aug 13, 5:37 pm, "Dan R." wrote:





Here's an example:

Sub test()
Dim wb As Workbook, ws As Worksheet

Set ws = ThisWorkbook.ActiveSheet
Set wb = Workbooks.Open("C:\filename.xls")

wb.Sheets(1).Range("A1").Copy
ws.Range("A1").PasteSpecial xlPasteValues

wb.Close Savechanges:=False
End Sub

--
Dan

How do I reference the Activesheet that I'm pasting to, if the sheet
is in another excel file? I'm calling the function from outside the 2
excel files. Do I open the workbook first. What is the code to make
that sheet active before Ipaste?

Nhien- Hide quoted text -

- Show quoted text -

I was able to get this working, but now whenever I close the workbook
that I copied from, it prompts me if I want to save the data in the
clipboard. How do I bypass the prompt if I am using the code above?

- Nhien

VBA code to copy and paste by values from one Excel workbook to another
 

Nhien,

Put this at the top of your code:

Application.DisplayAlerts = False


then put this at the bottom:

Application.DisplayAlerts = True

--
Dan

VBA code to copy and paste by values from one Excel workbook to another
 

On Aug 14, 5:47 pm, "Dan R." wrote:
Nhien,

Put this at the top of your code:

Application.DisplayAlerts = False

then put this at the bottom:

Application.DisplayAlerts = True

--
Dan

Thanks, I was able to get the DisplayAlerts to work. The only problem
I have now is after pasting the data, I want to remove the highlight
from the sheet. Here's my code. I get an error at
ws.Range("A1").Select. What am doing wrong?


Dim XL As Excel.Application
Dim wb As Workbook
Dim wb2 As Workbook
Dim ws As Worksheet


Set wb2 = Workbooks.Open(strMainFile)
Set ws = wb2.Sheets("POC_datasheet")
Set wb = Workbooks.Open(strWorkFile)
Set XL = wb2.Parent
XL.DisplayAlerts = False



wb.Sheets("POC_data").Range("A1:F65").Copy
ws.Range("A1:F65").PasteSpecial xlPasteValues
ws.Range("A1").Select

wb.Close SaveChanges:=True
wb2.Close SaveChanges:=True

-Nhien

VBA code to copy and paste by values from one Excel workbook to another
 

Nhien,

Put this before your select statement:

ws.Parent.Activate

--
Dan

VBA code to copy and paste by values from one Excel workbook to another
 

On Aug 15, 1:33 pm, "Dan R." wrote:
Nhien,

Put this before your select statement:

ws.Parent.Activate

--
Dan

It worked. Thanks Dan for all your help.

- Nhien