Making duplicate of a current excel and close the new one

[Pavnov]

Hi,

I am totally new to VBA scripting. Here is what I want to achieve -

I have an excel file wbA with worksheets ws1,ws2,ws3.
I want to write a VBA macro that creates an excel file wbB in a given path and copies all 3 work sheets into wbB (wbB will also have ws1, ws2 and ws3 - same names as worksheets in wbA).

At the end wbB should be saved and closed whereas wbA should remain open (I need this macros to run once every 5 min).

Here is my current code -

Public ONTIMER_S As Date

Public Sub SaveBook()

    Workbooks("wbA.xlsm").Save
 
    ONTIMER_S = Now() + TimeValue("00:05:00")
    Application.OnTime ONTIMER_S, "SaveBook"
    
    Dim dt As String
    dt = Format(CStr(Now), "yyyy_Mm_Dd_Hh_Nn_Ss")

    Workbooks("wbA.xlsm").SaveAs Filename:="path\wbB_" & dt & ".xls"

End Sub

The problem with this code is that at the end of the script wbA is getting closed and wbB remains open.

An alternative would be to use the following logic -

1. Open a new blank workbook
2. Save new workbook with required name.
3. Copy data from required sheets in the source workbook.
4. Open destination workbook (saved in step 2).
5. Paste copied data into destination workbook.
6. Close destination workbook.

However, here I couldn't figure out how to paste data from different sheets in the source workbook into different sheets (of the same names) in the destination workbook.

Any help will be greatly appreciated.