macro to rename mutiple spreadsheets when being saved

  • Thread starter Thread starter Mikey May
  • Start date Start date
M

Mikey May

I have a number of spreadsheets all named 'CTS Master' but
placed in different places in a network. A macro opens
these files individually and populates another
spreadsheet - 'Batched CTS', with the data from all
the 'CTS Master' files (about 20 of them). Once each
sheet is opened and all the data pulled across I want to
save these files as an electronic backup eg 'CTS Master'
file used by Mark is saved as 'CTS Master - Mark 17 Nov
03'.

Without doing this manually (there will be about 20 'CTS
Master' files on the network)is this possible?

Any help with this would be fantastic as I have a pay
review in a couple of weeks and I have seen a nice
Mercedes I want!!!!
 
Mikey Mercedes,

How do you know the file is used by Mark - is "Mark" written in a cell, or
is the file pulled from a folder named "Mark", or.....?

Answer that, and the rest is easy.

HTH,
Bernie
MS Excel MVP
 
I would expect Bernie or whomever assists to get a "piece of the action" on
that Mercedes.

Gord Dibben XL2002
 
Mikey,

You contacted me privately with this:
Yes Mark will have his name within a cell in his 'CTS Master'.
Click to expand...

So, within your macro, you could use code like this, which assumes
"Mark" is in cell A1 on Sheet1, and that the file is currently the
activeworkbook - to have it better integrated, I would need to see
your code:

ActiveWorkbook.SaveAs "CTS Master - " & _
Worksheets("Sheet1").Range("A1").Value & _
Format(Now(), " dd mmm yy") & ".xls"
ActiveWorkbook.Close False

If you want to specify the folder to save the file, then use

ActiveWorkbook.SaveAs "C:\Folder\CTS Master - " ....

HTH,
Bernie
MS Excel MVP
 
Gord,

Mikey Mercedes said that I could have a ride in his new Mercedes if he
gets one. But I doubt I'll go for that ride, given that Mikey is in
Yorkshire.

Bernie
MS Excel MVP
 
Rats!!

Gord

Gord,

Mikey Mercedes said that I could have a ride in his new Mercedes if he
gets one. But I doubt I'll go for that ride, given that Mikey is in
Yorkshire.

Bernie
MS Excel MVP
Click to expand...

Gord Dibben XL2002
 
Back
Top