Asking VBA knowledge for my macro

[PaulS]

Hi to the experts,
Using VBA statements from other macros I tried to make the following macro.
The function is to make a new worksheet with data from a large sheet(the database).
The user is asked to give a date. This is the startdate for the selection, the enddate is always 10 days later. Dates are in dd-mm-yyyy format.
In column G is a date to compare
The logic of macro is:

Sub arrivals()
Start in row 2 (row 1 is the header).
Firsttime = Y
Do while a date in cell G.row
      If  startdate < Date in G.row < enddate Then
          If first time = Y Then
              Add worksheet with name (“Arrivals” & startdate)
              Set contents for header in cells of Row1 
              Arrivals.cell A1 = “header A1”  
              Arrivals.cell B1 = “header B1”
              And e few more column headers in cells
          EndIf
          Rownr = Rownr +1 (in new sheet)
          Copy a number of cells from large sheet to new sheet
       End If
Loop
Select all rows of Sheet(Arrivals)
Print Sheet (in landscape all rows can be printed on one or two pages)
Msg “There are “ & Count & “arrivals from “ & strartdate &  ”to” enddate
MsgBox Msg
End Sub

Below my macro. Execution returns error that I cannot handle. Please help.
Thanks,

    Dim sDat As Date
    Dim eDat As Date
    Dim rngaDat As Range, raDam As Range
    Dim sdatYMD As Date
    Dim wSheetname As String
    Dim Firsttime as String 
    Firsttime = “Y”
    HeaderA1 = “A1” ‘for testpurpose
    HeaderB1 = “B1”
    Range("G1").Select
    'Ask for startdate 
    sDat = InputBox("Enter startdate")
    sDat = "01-05-2010"
    eDat = sDat + 10
   
    sdatYMD = Format(sDatum, YYYY - MM - DD)  does not work!
   ' Msg = sdatumYMD & " " & eDatum
   ' MsgBox Msg
    ‘Sheet (1) is the Database
    With Sheets(1)
        Set rngaDat = Range("G2", Range("G" & Rows.Count).End(xlUp))
    End With
   
    For Each raDat In rngaDat
     If raDat < sDat Then           ‘Before startdate
        GoTo NextRow
        End If
     If raDat > eDat Then           ‘after enddate
        GoTo NextRow 
    ‘ Msg = raDat & "copy "
    ‘  MsgBox Msg
    If Firsttime = “Y” Then
        Firsttime = “N”
        'New sheet is called Arrivals
         wsheetname = "Arrivals" & Format(Sdat, MM-DD)
         With ThisWorkbook
              Set wsheetname  = .Sheets.Add(After:=.Sheets(.Sheets.Count))    error???
          End With
        ‘ How to get data in cells A1 = HeaderA1 , B1= HeaderB1 etc ??
        Row = 2
    EndIf
    Çopy from current  row (as from raDat) a number of cells of main sheet to 
                                                 cells of Arrivals sheet 
            ‘Arrivals.A.Row = Database.C.raDate ??
            ‘and so some other cells
    Row = Row + 1
    NextRow:
    Next raDat        
End Sub

 

[jbeaucaire]

One correction:

sdatYMD = Format(sDatum, "YYYY - MM - DD")

Another:

    With Sheets(1)
        Set rngaDat = .Range("G2", .Range("G" & .Rows.Count).End(xlUp))
    End With

 

[PaulS]

This is a step forward. Thanks.
Question 1:
Now the macro gives an error when giving a name to a new sheet:
Some statements of the macro:

Dim wNewSheetname As Worksheet
Dim sDat as Date
e.g. sDate = "01-05-2010"
wNewSheetname = "Arr" & Format(sDat, "dd-mm") <--- error ; what is wrong??
Later in macro:
With ThisWorkbook
Set wNewSheetname = .Sheets.Add(After:=.Sheets(.Sheets.Count))
End With
A new sheet is added with name "Sheet2"

Question 2:
How do I copy cellcontents from the databasesheet to the new sheet? All from the current row of the databasesheet to a new row in the new sheet.
Thanks sofar,
Paul

 

[jbeaucaire]

In the line of code you've pointed to, you're trying to use your sDat variable but you haven't put a value into that variable yet. Do that first.