Another VBA question

[Royville]

Hi All,

I want to save 2 worksheets from my existing workbook to a new workbook but I can't seem to manage it some how. I have managed to save a single sheet, but could you have a look at my code please and tell me how to make it save 2 sheets, eg "List" and "Results".

Code: Select all

Dim wb As Workbook

    Worksheets("Results").Copy
    Set wb = ActiveWorkbook
    wb.SaveAs "C:\Res" & Format(Date, "dmmm") & ".xls"
    wb.Close

Also, is it possible to include the 'Time' when saving as well as the 'Date'?

Many thanks
Roy.

[osknows]

Hi Roy,

I think this should work

Code: Select all

Option Explicit

Sub test()
'opens specified Workbook, copies Worksheets named 'List' & 'Results' from
'workbook this code is run from to specified Workbook AFTER the Worksheet named 'Sheet3'
Dim path As String
Dim filename As String
Dim extension As String
Dim newfilename As String

path = "C:\Users\osknows\Desktop\"
filename = "Book3.xlsx"     

'open workbook to paste into
Application.Workbooks.Open (path & filename)

'copy sheets
ThisWorkbook.Sheets(Array("List", "Results")).Copy After:=Workbooks(filename).Sheets("Sheet3")

'save opened workbook with timestamp and close
extension = "." & Split(filename, ".")(1)
newfilename = Split(filename, ".")(0) & Format(Now(), " mm_dd_yyyy hhmmAMPM") & extension
Workbooks(filename).SaveAs filename:=path & newfilename
Workbooks(newfilename & extension).Close
End Sub

[Royville]

Thanks Oz,

Works great!
Much appreciated

Roy.