MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Data Copy & File Save As in a Sequence

Posted by Mark on October 16, 2001 8:45 AM

can anyone help please...

...generaly I have got 2 files
one (xxxx.xls) contains the
reference data (i.e. single
column of data)

each one of the cells
from file xxxx.xls
needs to be copied in sequence to
a particular cell in
file two (yyyy.xls) as well
as perform a Save As Operation
with the filename equal to the value of
cell copied. Once done, next value from in sequence
(from file xxxx.xls is read and treated same as above)

hope the above makes sense...

Many thanks for your help...


Posted by Dan on October 16, 2001 11:50 AM

...File Save As...

Well, I think I can help with part of that. To save a file using the value of the cell as the name, you could put this in your macro after you paste the data in to the new cell:

dim strFileName as String
strFileName = "c:\" & Range("A1").Value & ".xls"
ActiveWorkbook.SaveAs FileName:=strFileName, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

Here I am supposing that the new cell you are pasting to is A1 and you are saving it to your c:\ drive root directory. Change as needed. HTH.