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

[Nhien]

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

 

[Dan R.]

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

 

[Nhien]

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

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

 

[Nhien]

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

 

[Dan R.]

Nhien,

Put this at the top of your code:

Application.DisplayAlerts = False


then put this at the bottom:

Application.DisplayAlerts = True

 

[Nhien]

Nhien,

Put this at the top of your code:

Application.DisplayAlerts = False

then put this at the bottom:

Application.DisplayAlerts = True

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

 

[Dan R.]

Nhien,

Put this before your select statement:

ws.Parent.Activate

 

[Nhien]

Nhien,

Put this before your select statement:

ws.Parent.Activate

It worked. Thanks Dan for all your help.

- Nhien