Workbooks.Close

[andim]

Hi everyone

I have a command button which takes the value of A20, opens another .xls
file and pastes the value in A20 of the new file (the new file has been
assigned to variable cd).

It is then supposed to close and save the new file with the following code:

Workbooks(cd).Close SaveChanges = True

But it doesn't save the changes. I don't know if it is because the command
button is in the first .xls file or not though.

Any Ideas?

 

[Jim Thomlinson]

Hard to comment only seeing the one line of code but that line you posted is
not correct. You are missing the colon.

Workbooks(cd).Close SaveChanges:= True

So that line you posted will not even execute... Perhpas post all of your
code so we can see what is going on...

 

[Joel]

I always use

set cd = workbooks.open(filename:=FName)

cd.close Savechanges:=true

Excel might not like CD because it is the command "Change Directory". Try a
different name!

 

[andim]

It was the colon missing that was the problem. Thanks very much. Here is all
of the code if you're interested.

Private Sub CommandButton3_Click()

Range("A20").Select
Selection.Copy
Workbooks.Open ("C:\Documents and Settings\Andrew\My Documents\Serious
Stuff\TCSpreadsheets\Convoluted.xls") 'Opens Convoluted
cd = ActiveWorkbook.Name ' Names Convoluted "cd" for closure later
ActiveSheet.Range("A20").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Workbooks(cd).Close SaveChanges:=True ' Closes Convoluted and saves (cd)

End Sub

Thanks again for your help I am very new to this and I am just basically
learning by recording macros and figuring out the code.

 

[Dave Peterson]

CD is the old DOS command.

ChDir is the VBA command to change directory.

 

[john]

this approach may do what you want.

Private Sub CommandButton3_Click()

Dim cd As Workbook
Dim rng1 As Range

Set rng1 = ActiveSheet.Range("A20")
Set cd = Workbooks.Open("C:\Documents and Settings\Andrew\My
Documents\SeriousStuff\TCSpreadsheets\Convoluted.xls") 'Opens Convoluted

rng1.Copy cd.Worksheets("Sheet1").Range("A20") '<<change sheet name as
required
cd.Close True ' Closes Convoluted and saves

Application.CutCopyMode = False
End Sub