View Full Version : auto open new file and save as range name
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
08-25-2005, 09:55 AM
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
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
08-25-2005, 03:11 PM
Try
ActiveWorkbook.SaveAs Filename:=MyPath & NewName, FileFormat:=xlNormal
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
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
08-26-2005, 08:13 AM
Glad to help.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.