Create a Copy of workbook with selected sheets

[Hudson]

Hi all,

Can some one advice . how to create a VB code that dynamically create cope of work book with values ( formulas needs to brake up ) . for example : i prepared a macro template which has 7 sheets with different names . however one of the sheets named " Summary " has formulas where its data source is from remaining sheets . now i want a VBA code to be create . that dynamically create another work book and copy past sheets (" summary"," Invoices","Credits") only . and to be saved by the today report with date . can that be happend ?. am i expecting too much ?.

any inputs are appreciated.

[onlyadrafter]

Hello,

how is this for starters, have made some assumptions. There is no error checking in the macro.


Sub COPY_WORKBOOK() MY_REPORT_NAME = Sheets("Summary").Range("A1").Value
ActiveWorkbook.SaveAs ("C:\" & MY_REPORT_NAME & "_" & Format(Now(), "dd-mm-yy"))
For MY_SHEETS = 7 To 1 Step -1
Select Case Sheets(MY_SHEETS).Name
Case "Summary", "Invoices", "Credits"
Sheets(MY_SHEETS).Cells.Copy
Sheets(MY_SHEETS).Range("A1").PasteSpecial (xlPasteValues)
Case Else
Application.DisplayAlerts = False
Sheets(MY_SHEETS).Delete
Application.DisplayAlerts = True
End Select
Next MY_SHEETS
ActiveWorkbook.Save
End Sub

[Hudson]

Mate- first of all thanks for coming back on my request . and this is something very similar to my request . can this be made more dynamic . lets say i want file to saved as Xlsx extension or xls . with the name to be saved that is in the invoice sheet (B2) cell . can that be happened ?

LETS SAY "Statements_UNI SELECT USA - IEH - IEH(B$2)_date

[Aussiebear]

Mate- first of all thanks for coming back on my request .

LOL. You think Onlyadrafter came back because of your request...... I think its because he knows he can make a difference by being active.

[Hudson]

he addressed back on my request . i felt happy .. definitely he did not only come back on my request .. as you said he is active he came to help me like any other help he do in the forum .. :)

[Hudson]

Onlyadrafter - mate thanks for your help and i really appreciate your time . issue has been resolved .