Thread: Macro to copy only values and not formulas or links to a master file

Hi everyone again. I am here looking for your help on further develop this macro to copy data from one workbook to another. This macro, from the help of arlu1201, can copy data from one workbook to another. However, I have tried for many days to make this macro to copy values and not formulas or links. All the data on “Stats” sheet should be copied as values to workbook report2012.xls. I have used .PasteSpecial or Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
This is the macro the I am currently using:

Option Explicit
Dim MFile As String
Dim lastrow As Long
Dim frow As Long
Dim lrow As Long

Sub update_master()

MFile = "C:\KBD project\report2012.xls"


If MFile = "" Then
    MsgBox "Please open the KBD Shop Floor Reports 2012", vbCritical
End If
    
Workbooks.Open MFile
MFile = ActiveWorkbook.Name

lastrow = ThisWorkbook.Worksheets("Stats").Range("A" & Rows.Count).End(xlUp).Row
ThisWorkbook.Worksheets("Stats").Range("A2:V" & lastrow).Copy _
Workbooks(MFile).Worksheets("Libros").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    
    Workbooks("report2012.xls").Close SaveChanges:=True
End Sub

Could someone explain to me what I am doing wrong? I am very new to VBA, but I am willing to work hard and learn. See attachment for detail.s
Again, thank you everyone, specially my friends for your time and knowledge.

Mr. JBeaucaire, thank you very much for your help. Your code does what I need. Excellent work!
However, I want to prevent users from running this macro twice during a shift. This macro should only be run one time at the end of each shift; one at 1st shift and one at 2nd shift. I am thinking that I could use the value in column A and column B in worksheet “Stats”. I started to read a thread from Cheelie with the help of mudraker, and I belive it is possible, but I don’t know how to start.
Can I use this piece of code to acomplish this task?

Sub Test1()
   Dim sResp As String
   
   If IsDate(Range("a1").Value) Then
      If Range("a1").Value >= Date - 7 Then
         sResp = MsgBox("Macro has been run this week" _
            & vbLf & vbLf _
            & "Run Macro Again", vbYesNo + vbDefaultButton2)
         If sResp = vbNo Then
            Exit Sub
         End If
      End If
   End If
   
   Range("a1").Value = Date
End Sub

http://www.excelforum.com/excel-prog...n-one-day.html
Your help is very appreciating.

You really got me thinking very hard. What about if I add a header, in column W, called Last changed in MFile to add a timestamp when the last time the file was modified. I did my research and I found this code that can a single timestamp in W2:

[w2].Value = Format(Now, "dd/mm/yy hh:mm:ss")

Mr. JBeaucaire, can I use this piece of code to determine if previous macro was run before?

Thanks snb for contributing. However, what should I do with this code? I replaced JBeaucaire's code with yours but it only copies one row. I need to copy all data in rows in "stats.

This is fantastic! With your last update to the code, I am more close to what I want to be. Mr. JBeaucaire, don’t you think that by adding NOW instead of DATE would be more precise to when the previous code was run? Also, I have added an InputBox to ThisWorkbook to capture user name and save it in "Stats in Column W2. Then when all data is copy and paste in report2012.xls the user name gets copy along with the date and time. This the code in ThisWorkbook

Private Sub Workbook_Open()
MsgBox "Hi, Welcome" & Chr(13) _
& "Hola, Bienvenidos", vbInformation, "Rosendo Moreno"
MyName = InputBox("Enter Your Full Name \Escriba Su Nobre Completo", "What Is Your Complete Name?")
ThisWorkbook.Worksheets("Stats").Range("W2").Value = MyName

End Sub

and your last code is:

Option Explicit

Sub Update_Master()
Dim MFile As Workbook
Dim LR As Long

Set MFile = Workbooks.Open("C:\KBD project\report2012.xls")

If MFile.Sheets("Libros").[X2].Value < Now - 6 Then
    With ThisWorkbook.Worksheets("Stats")
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A2:W" & LR).Copy
        MFile.Sheets("Libros").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        MFile.Sheets("Libros").[X2] = Now
    End With
End If

MFile.Close True
End Sub

Again Mr. JBeaucaire, thank you very much for all your help and pacient. I am a new VBA learner with a lot eager to be like you and my other friends here in this forum.

Originally Posted by JBeaucaire

You can get the Windows Username using the Environ method. (Google it.)

If you want to use NOW, use NOW. Based on all you've said I don't see that information as being that highly relevant. And you might possibly run into a problem where you're trying to run the macro a week later and CAN'T because you did it so late in the day last week that a full week hasn't passed yet. Leave the "time" out and that won't happen.

But it's your call. Experimenting will tell you want works best for you.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.


(and you can call me Jerry)

The only reason I wanted to use now is because this macro should only be ran twice. One at the end of each shift to populate all the data in report 2012. The way this macro is coded, it will only populate data once a day. I believe it won't record 2nd shift data.
Also, I read about the Environ Function in VBA, which it will only give me the user’s name based on the Windows login. However, there are several “operators” for libros012612.xls and only one Windows login name and I would prefer to log the operator’s name when the workbook opens.
Again, Jerry thank you for all your effort and contribution to this project and to the forum.