(VBA) Extracting data from a newly created sheet (dates/multiple tabs)

[Garrix]

I'm not sure if I'm on the right track or if I started off entirely in the wrong place. I have the below, which I am using to create a new workbook. After that workbook is created, I would like to use those same variables (the dstart & dend) to define a range, which will be copied out of each of 3 separately named worksheets (the range will be the same, the worksheets will not be), and then pasted into the newly created workbook.

As a quick note I got part of this (the error handling part, and some optimization) from stackoverflow... and when I went into the second part of what I needed (the extraction part) I found out there is generally a much higher base skill ceiling there; I was a bit out of my depth.

Sub CreateNewWorkbookAsName()
   Dim CurrentWorkbook As String
   Dim CurrentFormat As Long
   Dim dStart As Date
   Dim dEnd As Date
    
    On Error GoTo bm_SlapWrist
    dStart = Application.InputBox("Please enter start date. (dd-mm-yyyy)")
    dEnd = Application.InputBox("Please enter end date. (dd-mm-yyyy)")
    Filename = Format(dStart, "dd-mm-yyyy") + " to " + Format(dEnd, "dd-mm-yyyy") + ".xlsm"
 
    With ThisWorkbook
     CurrentWorkbook = .FullName
     CurrentFormat = .FileFormat
     .SaveAs Filename:=Format(dStart, "dd-mm-yyyy") & " to " & Format(dEnd, "dd-mm-yyyy"), _
            FileFormat:=CurrentFormat
    End With
   '  Call Macro2(ThisWorkbook.Name) (this part was supposed to point to the extraction macro)
    Exit Sub


bm_SlapWrist:
    If Err.Number = 13 Then  'bad date: Type mismatch
        MsgBox "Try to get it right this time.", vbCritical, Title:="Bad User!"
        Err.Clear
        Resume
    End If


End Sub

 

[Garrix]

Hoping this was missed due to it being posted on Sunday!

Anyone have any ideas?