auto open new file and save as range name

[rayb]

In my open file I have two ranges named "cono" and "new". I trying to write a macro that will open another file with the same name as the contents of "cono" and paste the contents of range named "new" into a range named "inv". I then want to save the new file as the contents of a range named "wktk" and close the file and go back to the original spreadsheet.

Any help would be greatly appreciated.

[mdmackillop]

Hi Rayb,
Welcome to VBAX.
Change the path to suit and try the following
Option Explicit
Sub DoThings()
Dim MyPath As String
Dim NewName As String
MyPath = "C:\AAA\"

Range("New").Copy
NewName = Range("wktk")
Workbooks.Open MyPath & Range("cono")
Range("Inv").Select
ActiveSheet.Paste
ActiveWorkbook.SaveAs MyPath & NewName
ActiveWorkbook.Close
End Sub

[rayb]

Everthing works except for the next to last line where is saving the file as "wktk". I get a Run time Error 1004. The contents in "wktk" is 2372

[mdmackillop]

Try
ActiveWorkbook.SaveAs Filename:=MyPath & NewName, FileFormat:=xlNormal

[rayb]

Unfortunately Your latest suggestion did not work either. I have shown below the code that I have in place. Please take a look at it and see if you can see anything wrong, Obviously, I am a rank amateur at this.

Sub Dothings()
Dim MyPath As String
Dim NewName As String
MyPath = "C:Documents and SettingsraybMy Documents"

Range("New").Copy
Workbooks.Open MyPath & Range("cono")
NewName = Range("wktk")
Range("inv").Select
ActiveSheet.Paste
ActiveWorkbook.SaveAs Filename:=MyPath & NewName, FileFormat:=xlNormal
ActiveWorkbook.Close
End Sub

The file the macro opens is named 118 and it contains the range names "wktk" and the info in field "wtkt" is 2372. There is not a file already named 2372 but for some reason it will not accept it when trying to do a SaveAs.

Any Ideas?

Thanks
rayb

[rayb]

Solved, I moved the line of code that says NewName = Range("wktk") down two lines. I realized that there was no info in the field until the filed was copied. Thanks for your help.

[mdmackillop]

Glad to help.