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 !