VBA code to create new workbook and move 3 sheets into it

rob_sheeds said:

Hi,
I am looking for help with some code to

• Move 3 sheets (all with formulas) into a new workbook.
• Workbook needs to then be saved as "DSR_DDMMYYYY".xlsx
• Press save again (this will refresh the formulas which use an addin to query a database)
• Select all sheets
• Select all
• Copy, Paste Values
• Press save again
• Initiate another macro to email book

Anyone willing to give this a go?
Cheers
Rob

Click to expand...

rob_sheeds said:

Only 3 sheets visible but all 6 sheets need to move across as they are parameter sheets holding lists etc, but the report may grow. I can read VBA and make minor changes so I can change when needed if the code is forgiving.
I have attached a copy and the macro is sitting in there but it keeps pointing to an earlier version of the macro i think.
Maybe if you can make it point to the one in the book too? Not too good with that.
Cheers!!!!!
If you have an email I can send the book?..Cant attach.
Sheet 1 = Daily Sales Report
Sheet 2 = Open Orders
Sheet 3 = SOGS
Sheet 4 = Lists
Sheet 5 = ListsA
Sheet 6 = AtlasParameters
(Last 3 sheets need hiding)
Macro is Module 1 = "Sub Mail_workbook_Outlook_1()" Hope this helps.
Appreciate your time too.
Thanks

Click to expand...

Sub robsheeds()

Dim i As Long

    Workbooks.Open Filename:= _
        "C:\YOUR PATH\OLDWORKBOOK.xls"
    ActiveWorkbook.SaveAs Filename:="C:YOUR PATH\DSR_" & Format(Date, "DDMMYYYY") & ".xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
        
With ActiveWorkbook

    For i = 1 To 6
    
        Sheets("Sheet" & i).Cells.Value = Sheets("Sheet" & i).Cells.Value
        
    Next i
    
End With

Call Outlook Macro

End Sub

rob_sheeds said:

Only 3 sheets visible but all 6 sheets need to move across as they are parameter sheets holding lists etc, but the report may grow. I can read VBA and make minor changes so I can change when needed if the code is forgiving.
I have attached a copy and the macro is sitting in there but it keeps pointing to an earlier version of the macro i think.
Maybe if you can make it point to the one in the book too? Not too good with that.
Cheers!!!!!
If you have an email I can send the book?..Cant attach.
Sheet 1 = Daily Sales Report
Sheet 2 = Open Orders
Sheet 3 = SOGS
Sheet 4 = Lists
Sheet 5 = ListsA
Sheet 6 = AtlasParameters
(Last 3 sheets need hiding)
Macro is Module 1 = "Sub Mail_workbook_Outlook_1()" Hope this helps.
Appreciate your time too.

Thanks

Click to expand...