Hi there. I've got a project in which I will expect VBA will do the following:

There are two files involved: Masterfile.xls and Tempfile.xls
The vba codes will be the same in both files.

1. open a master file and a userform will pop up;
2. click on a 'duplicate' button to copy the active file (master file) to a temp file;
3. after creating a copy file, the masterfile will be closed.
4. a userform (same as the one of the masterfile) will pop up for user to update the record;
5. value of textbox will be stored in the specified range of the active file (temp file);
5. click on the 'Update' button to copy the specified range from the temp file to the same range of the masterfile.

The problem is: The vba codes just failed to copy the specified range from temp file to masterfile. It seemed that after the re-opening of the masterfile, the codes just did nothing.

The following vba codes are embedded in both the masterfile.xls and tempfile.xls.




Code:
First open the masterfile: 

Sub auto_open() 
Worksheets("sheet1").Activate 
If Range("q1").Value = "Y" Then 
   Load UserForm1 
   UserForm1.Show 
   UserForm1.CommandButton1.Visible = False 
End If 
If Range("q1").Value = "" Then 
   Load UserForm1 
   UserForm1.Show 
   UserForm1.CommandButton2.Visible = False 
End If 
End Sub 

Private Sub CommandButton1_Click() 'button to create a tempfile 

ActiveWorkbook.SaveCopyAs "c:\tempfile.xls" 
Workbooks.Open "c:\tempfile.xls" 
Worksheets("sheet1").Activate 
Range("q1").Value = "Y" 
ActiveWorkbook.RunAutoMacros xlAutoOpen 
Workbooks("masterfile.xls").Close SaveChanges:=False 
End Sub 

Private Sub CommandButton2_Click() ' a userform will pop up after tempfile is loaded. This button is to update the record. 

Dim MasterWB As Workbook 
Dim TempWB As Workbook 
Dim CopyRange As Range 
Dim PasteRange As Range 
cntMax = Cells(Rows.Count, 1).End(xlUp).Row 'count the no. of row with data 
Range("a" & cntMax + 1).Value = TextBox1.Value 
Set MasterWB = Workbooks.Open("C:\Masterfile.xls") 'it seemed that the codes did nothing from here!! 
Set TempWB = ThisWorkbook 
Set CopyRange = TempWB.Worksheets("Sheet1").Range("a1:a" & cntMax+1) 
Set PasteRange = MasterWB.Worksheets("Sheet1").Range("a1:a" & cntMax+1) 
Call CopyRange.Copy(Destination:=PasteRange) 
MasterWB.Close SaveChanges:=True 
End Sub 


Private Sub UserForm_Initialize() 
If Range("q1").Value = "Y" Then 
   Set TempWB = ThisWorkbook 
   CommandButton1.Visible = False 'make 'Duplicate' button invisible 
Else 
   CommandButton2.Visible = False 'make 'Update' button invisible 
End If
What did I miss?

Cheers.