Macro to export several sheets to a new workbook then name it with wording and today's date

[Melimob]

Hi there

I seemed to have found macros that do part of what I need but I have no idea how to put it all together..

I need to export 4 worksheets named:
BELGIUM
GERMANY
UK
SUMMARY

to a new workbook in the same location each week and name it:

Weekly_Online_Adoption_TODAYSDATE.xlsx

Todays date is obviously the date I saved.

I know how to record a macro to do part (without the name) but each time I save Book1 may not be book1 so I need it to be dynamic!

Many thanks!
Melissa

 

[VoG]

Try

Sub test()
Sheets(Array("BELGIUM", "GERMANY", "UK", "Summary")).Copy
ActiveWorkbook.SaveAs Filename:="Weekly_Online_Adoption_" & Format(Date, "ddmmyyyy") & ".xlsx", FileFormat:=51
End Sub

 

[Melimob]

Hi Peter,

Thanks so much!! that works perfectly however could you also tell me 2 things that I forgot..

1) In the new workbook, can I also change the page layout theme colours to a saved theme template?
2) It is saving in My Documents, is there anyway I can get it saved to a particular folder?

Many thanks!

 

[VoG]

For #1 try recording a macro whilst doing that manually to give you the code.

for #2 try like this

ActiveWorkbook.SaveAs Filename:="C:\test\Weekly_Online_Adoption_" & Format(Date, "ddmmyyyy") & ".xlsx", FileFormat:=51

 

[Melimob]

wow - Peter, that worked wonderful thank you so much!!

re: point 1 - it doesn't record anything when choosing theme but it's no bother, only takes a sec manually!

cheers again!

Melissa

 

[Melimob]

Try

Sub test()
Sheets(Array("BELGIUM", "GERMANY", "UK", "Summary")).Copy
ActiveWorkbook.SaveAs Filename:="Weekly_Online_Adoption_" & Format(Date, "ddmmyyyy") & ".xlsx", FileFormat:=51
End Sub

Hi Peter, you kindly gave the above yesterday which worked great! I am trying to use it in another workbook and have simply adjusted the tab names and file names accordingly yet I'm getting an error...

It is highlighting the Sheets line?

Would you perhaps know why?

this is what I have:

Sub Export()
'
' Export Macro
'
Sheets(Array("ASIA Summary", "ASIA DETAIL", "EMEA Summary", "EMEA DETAIL")).Copy
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\motest\My Documents\WeeklyFile" & Format(Date, "ddmmyyyy") & ".xlsx", FileFormat:=51
End Sub

 

[VoG]

That compiles fine me. We also have a EMEA region - Yuk!

 

[reinsermat]

Thank you!
Mr. Excel is the best!

 

[pman1867]

Is it possible to edit the code so that the new sheet is saved as values instead of the formulas?

Thanks in advance!