Copy worksheets(only values) from one workbook to another

[edlim85]

hi there,

I tried copying(create a copy) a bunch of worksheets from one workbook to another. I was prompt

'A formula or sheet you want to move or copy contains the name 'body', which already exists on the destination worksheet. Do you want to use this version of the name?

• to use the name as defined in the destination sheet, click yes
• to rename the range referred to in the formula or worksheet, click no

As a result, i have to click many times 'yes'. how do i go around this? I only need to copy the values only..

below is my code

Sub PushTable()
Dim wOB As Workbook, wMB As Workbook
Application.ScreenUpdating = False

Workbooks.Open Filename:="c:\A.xlsx"

Set wOB = Workbooks("A.xlsx")
Set wMB = Workbooks("Final.xlsm")
wOB.Activate

wOB.Worksheets(Array("sheet5", "sheet1", "sheet2", "sheet3", "sheet4")).Copy Before:=wMB.Worksheets(1)
wOB.Close SaveChanges:=False

Application.ScreenUpdating = True
End Sub

Pls advise

Regards
Edmund

 

[Jerry Sullivan]

Hello Edmund,

You can eliminate those warning messages by temporarily setting DisplayAlerts to False

Application.DisplayAlerts = False
'.....code that Copies worksheets
'.....
Application.DisplayAlerts = True

The code you are currently using will copy the entire sheets including formulas and formatting. If you only want to copy values only, you would need to step through each of the 5 worksheets in A.xlsx separately doing a Copy > Pastespecial(xlPasteValues) to new or existing sheets in Final.xlsm.

 

[edlim85]

Hi JS411,

The code you are currently using will copy the entire sheets including formulas and formatting. If you only want to copy values only, you would need to step through each of the 5 worksheets in A.xlsx separately doing a Copy > Pastespecial(xlPasteValues) to new or existing sheets in Final.xlsm.

Thanks for your reply.