Your worksheet names seems to change: 16-aug or 16aug???

This kind of thing worked ok for me:

Option Explicit
Sub testme01()

Dim GraphicWks As Worksheet
Dim NewWks As Worksheet
Dim DestCell As Range

Set GraphicWks = Worksheets("graphic")
Set NewWks = Worksheets.Add
NewWks.Name = Format(Date, "ddmmm")

With GraphicWks
Set DestCell = .Cells(.Rows.Count, "D").End(xlUp).Offset(1, 0)
End With

With DestCell
.Formula = "=countif('" & NewWks.Name & "'!a2:a25,a2)"
.Offset(1, 0).Formula = "=countif('" & NewWks.Name & "'!a2:a25,a3)"
End With

End Sub


Not sure where the next formula goes, so I went down column D.

wrote:

hi all,

i want to use a countif formula in vba that will count data from
another sheet. this sheet name is variable depending from the date the
macro was use.

example .

run the macro today.

-create new sheet name 16-aug
-in a sheet "graphic" new row named 16-aug. (for the case in cell D1)
-in D2 formula countif("16aug!A2:A25,"=A2") <------- this is what
i want
- in D3 formula countif("16aug!A2:A25,"=A3")

so everytime i run the macro a new sheet is created with a new name and
i want to get countif for the the cells under it.

--

Dave Peterson