Need some help please.

[cwip]

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.

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.

[NeedSomeAnswers]

I Think that part of your problem is as follows:- At the point in the code where i have written ERROR HERE below,
you are essentially trying to copy FROM Masterfile TO Masterfile

When you are setting TempWB = 'ThisWorkbook' at this point, because you have just loaded up Masterfile.xls,
Masterfile.xls is the Active workbook, not Tempfile.xls.

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                   <---------ERROR HERE
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

You need to Specify tempfile as the Workbook, to do this just swap these two lines of code around

Set TempWB = ThisWorkbook    
Set MasterWB = Workbooks.Open("C:\Masterfile.xls") 'it seemed that the codes did nothing from here!!

So that you set the TempWB to the tempfile Workbook before you reopen Materfile !

[cwip]

Hi there. Thanks for your prompt reply. I've swapped the two lines as you adivised but still to no avail. The code still failed to copy the desired values from the tempfile.xls to masterfile.xls. (Its seemed the codes just stopped at the point where the masterfile.xls was opened!). Are there any other ways which I can try?

Cheers.

[NeedSomeAnswers]

The problem lies where you specify the Copy range, this is not actually specifying a range but selecting a single cell

So add the following line of code as shown below, and this should select the range before you copy it:

Private Sub CommandButton2_Click() ' a userform will pop up after tempfile is loaded. This button is to update the record.
 
Dim CopyRange As Range
Dim PasteRange As Range
Dim cntMax As Long
cntMax = Cells(Rows.Count, 1).End(xlUp).Row 'count the no. of row with data
Range("a" & cntMax + 1).Value = TextBox1.Value
Set TempWB = ThisWorkbook
Set MasterWB = Workbooks.Open("C:\Documents and Settings\gilesr\My Documents\Masterfile.xls") 'it seemed that the codes did nothing from here!!
Range("A1:A" & cntMax + 1).Select     '<-- New Line of Code to Select Range !
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

This should work now !

[cwip]

Hi there. I tried another way and found the following change did work. The value of the specified range was successfully copied to masterfile.xls from tempfile.xls.

Change from 
Set TempWB = ThisWorkbook
Set MasterWB = Workbooks.Open("C:\Masterfile.xls") 

To
Set MasterWB = Application.Workbooks("Masterfile.xls")
Set TempWB = Application.Workbooks("tempfile.xls")
Set TempWB = ThisWorkbook

However, the following script failed to kill the tempfile.xls and quit the application after the copy of value .

Set MasterWB = Application.Workbooks("Masterfile.xls")
Set TempWB = Application.Workbooks("tempfile.xls")
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
TempWB.Close SaveChanges:=False
Kill ("c:\tempfile.xls")
Application.Quit
End
End Sub

Any advice please.

[NeedSomeAnswers]

try it with out the brackets

Kill "C:\tempfile.xls"

worked for me !