Copying Between Workbooks with VBA

[courtneyjh]

Hello,

I have recently (in the last month) started using VBA for a work project. My goal is to copy the values of certain cells in one workbook and paste them into what will become a Database like file. The macros were written where the destinations are links. I can break the links and the data becomes values but unfortunately if the original file is opened anytime the destination file is opened it will clear all cells linked even if I chose to "not update" them. I think my only choice will be to rewrite the macro so that it is more of a copy and paste rather than a link. The macro reads as follows....

Sub CopyToProductionReport()
'
' CopyToProductionReport Macro
'
'
'

'-----<<<<<<< change file location >>>>>>>--------"
If Not IsFileOpen("H:\00 Production Reporting\DO NOT OPEN ---- Production Reporting Database.xlsx") Then
newLine = LineName()
Workbooks.Open "H:\00 Production Reporting\DO NOT OPEN ---- Production Reporting Database.xlsx", UpdateLinks:=0

Dim vTotal_Row, vCurrent_row, vCurrent_column_p, vCurrent_column_d As Integer

'---------------------------------------------------------------------

Sheets("Sheet1").Select
vCurrent_row_d = Application.Run("FindNextRow")

vCurrent_column_d = 1
vCurrent_column_p = 1
Application.StatusBar = "Total row:" & vCurrent_row_d & " Processing row:" & vCurrent_row_s

'****************** shift 1 info
Sheets("Sheet1").Cells(vCurrent_row_d, vCurrent_column_d).Value = "='[" & newLine & ".xlsm]Data'!R17C3" 'a year
Sheets("Sheet1").Cells(vCurrent_row_d, vCurrent_column_d + 1).Value = "='[" & newLine & ".xlsm]Data'!R17C1" 'b month linked to A16
Sheets("Sheet1").Cells(vCurrent_row_d, vCurrent_column_d + 2).Value = "='[" & newLine & ".xlsm]Data'!R17C4" 'c week linked to D16
Sheets("Sheet1").Cells(vCurrent_row_d, vCurrent_column_d + 3).Value = "='[" & newLine & ".xlsm]Data'!R17C2" 'd day linked to B16

'-----<<<<<<< change file location >>>>>>>--------"
MsgBox "Copy to production report Complete"
ActiveWorkbook.SaveAs FileName:= _
"H:\00 Production Reporting\DO NOT OPEN ---- Production Reporting Database.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=True
ActiveWorkbook.Close True


Application.Run ("ClearSheet")

Else
result = MsgBox("Please close production database report", vbOKOnly + vbCritical, "Abort Encounterd")

End If

End Sub






Any suggestions on how to fix this? A rewrite? or an addition that will keep the links from updating?