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

[rob_sheeds]

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

 

[John Davis]

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

How many sheets does the old workbook have and what are there names? Do you already have an email macro? Are you using Lotus Notes or Outlook?

 

[rob_sheeds]

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

 

[John Davis]

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

I don't have Outlook, so I can't offer anything for that, but I was going to suggest something like this:

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

 

[singupalli]

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

Hi Rob

can you send me these to my mail
thanks

prasad@lange-me.com

 

[rob_sheeds]

Works except for this
Sheets("Sheet" & i).Cells.Value = Sheets("Sheet" & i).Cells.Value
error says; Runtime error '9'
Subscript out of range.

Here is the code I am using
Sub robsheeds()
Dim i As Long
Workbooks.Open Filename:= _
"C:\Users\rsheedy\Desktop\Daily Sales\Daily Sales Report NEW Open Sales Orders - Copy.xlsm"
ActiveWorkbook.SaveAs Filename:="R:\Reports\Archive\Daily Sales Reports\May 2011\DSR_" & Format(Date, "DDMMYYYY") & ".xlsm", _
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
Mail_workbook_Outlook_1
End Sub

Note that I have used .xlsm so would this be affecting the columns/rows ranges from .xls to .xlsx?
Or do I have to insert the sheet names into that line?
Cheers