VBA Question (Copy and Paste)

[Tfox2791]

I would like to only copy the values and the formating to the new workbook and worksheet. My code currently copies everything and all of the formulas so when the new workbooks are emailed out they show errors because the link is broken. I tried a few different methods but have been unsucessful, any thoughts?

Thanks for your sugesstions and help in advance....

Current Code:
Dim wbThis As Workbook
Dim wbNew As Workbook
Dim ws As Worksheet
Dim strFileName As String
Dim path As String
Dim name As String
Application.ScreenUpdating = False
path = "G:\Student Services\"
Set wbThis = ActiveWorkbook


For Each ws In wbThis.Worksheets

strFileName = "Drop Analysis " & ws.Cells(1, 105).Text & ".xlsx"
name = ws.Cells(1, 105)
If Dir(name) <> "" Then GoTo Blank

ws.Copy

Application.DisplayAlerts = False

Set wbNew = ActiveWorkbook

wbNew.SaveAs Filename:=(path & strFileName)

wbNew.Close
Blank:
Next ws

End Sub

 

[wigi]

Hi

Did you try PasteSpecial? For example:

Sub wigi()
    Range("A1").Copy
    Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End Sub

 

[madaknarf]

you can try adding this to your code after you copy the worksheet. The idea is that if the numbers on the copied worksheet are good before you save this will copy and paste special all the values on the sheet before you save as.

Cells.Select
Cells.PasteSpecial xlPasteValuesAndNumberFormats

 

[Tfox2791]

I tried the paste special route but it doesnt seem to work? I tried to insert ws.PasteSpecial xlPasteValuesAndNumberFormats after Set wbNew = ActiveWorkbook and it give me an error (Method of Paste Special Object _ Worksheet Failed)?

 

[wigi]

Yes it does, since I showed you in my example that PasteSpecial applies to a range like Range("A2") instead of to a worksheet like ws.