Copy formulas & values to new workbook

[pingme89]

There are many examples of copying data from one Workbook to another. However, I have a complex problem.
The source workbook has many sheets. The range of cells I need copy are from A1:P5 ONLY. Some of the cells have links to other sheets on the same source workbook and some are just formulas. The cells that have links to other sheets, I DO NOT want to copy the link. I just want to copy the value of the cell.

For instance in the source worksheet, one cell might contain "=A5*A6". I would need this formula to be copied over to the new spreadsheet.

However, there are other cells with contents such as, "=Worksheets(1).Range("A5").value * B3". In this situation I need only the value of the cell and not the formula copied to the new spreadsheet.

Having said that, I do know which cells need the formula copied and which cells need only the value copied over.

The range which the formulas need to be copied over are: A1:P2,G3:G4,J3:P4,A5:P5
The range whicn I only need the value copied over are: A3:F4,G3:I4

I can't find a way to copy 2 types of data. Can someone help me find an elegant solution?

So far this is what I have:

Private Sub Export_Click()
Dim wkbSrc As Workbook
Dim wkbDst As Workbook
Dim ws As String
Dim WBN As String
Dim ID As String
Dim SDrv As String
Dim DDrv As String
Dim Sfname As String
Dim Dfname As String




Dim DateStr As String
Dim DirPath As String
Dim Client As String
Dim ClientInfo


Dim WBName As String
Dim WBNameX As String


WBN = ThisWorkbook.Name




Client = Application.Workbooks(WBN).Worksheets(3).Range("F3")
ID = Application.Workbooks(WBN).Worksheets(1).Range("A7")


MsgBox Client
MsgBox ID


Workbooks.Add
DirPath = "D:\Estimates\"
DateStr = Format(Date, "MMMM dd, yyyy")
Rem Format(Date, "yyyy-mm-dd")


Rem WBName is the NEW Estimate Workbook Name


WBName = DateStr & " " & Client & " SUPP " & ID
MsgBox WBName
WBNameX = DateStr & " " & Client & " SUPP " & ID & ".xlsx"
MsgBox WBNameX




Rem Set this to the relevant worksheet
ActiveWorkbook.SaveAs DirPath & WBName & ".xlsx", FileFormat:=51
Rem ActiveWorkbook.SaveAs Filename:=DirPath & WBName & ".xlsx"




SDrv = "D:\Estimates\"
Sfname = ActiveWorkbook.Name

DDrv = "D:\Estimates\"
Dfname = WBNameX


Set wkbSrc = Workbooks.Open(SDrv & Sfname)
Set wkbDst = Workbooks.Open(DDrv & Dfname)

Application.DisplayAlerts = False









With wkbDst
.Save
Rem .Close


End With
End Sub

 

[GlennUK]

I have an idea ... break the external links in the source workbook, copy all required cells as one block, paste, looping as required. Close the source workbook without saving.