Hi, first post, hope someone can help me with this Excel macro. I am trying to sort through a column of dates and create a new spreadsheet with the date of each date that is found in the column. The problem lies in how I am using the Worksheet(1).Cells etc. addressing. It always considers Worksheet(1) to be the current worksheet when what I want to do is test the sheet with index 1.

Could someone explain the proper way to do this?

Ex:While IsEmpty(Worksheets(1).Cells((iRowcounter + 2), 1)) = False
While Worksheets(1).Cells(iRowcounter, 1).Value _
= Worksheets(1).Cells((iRowcounter + 1), 1).Value And _
IsEmpty(Worksheets(1).Cells(iRowcounter, 1)) = False
iRowcounter = iRowcounter + 1
Wend
dFirstDate = DateValue(Cells(iRowcounter, 1))
iFirstMonth = Month(dFirstDate)
iFirstDay = Day(dFirstDate)
iFirstYear = Year(dFirstDate)
Sheets.Add.Name = Format(DateSerial(iFirstYear, iFirstMonth, iFirstDay), "mmm dd")
iSheetCount = iSheetCount + 1
iRowcounter = iRowcounter + 1
Wend

Worksheet(1) should always be the same worksheet not the activeworksheet. Anyway I would do it more like this:


Sub bleh()


Dim wb As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set wb = ThisWorkbook
Set ws1 = wb.Worksheets(1)

For i = 1 To ws1.Range("A1").End(xlDown).Row
Set ws2 = wb.Worksheets.Add
ws2.Name = Format(ws1.Range("A" & i).Value, "mmm dd")
Set ws2 = Nothing
Next i

End Sub