vba countif linked to another sheet

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.

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.

masterbigggy@gmail.com 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

=COUNTIF(INDIRECT(A1&"!A2:A25"),A3)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

<masterbigggy@gmail.com> wrote in message
news:1155766610.289321.280490@i3g2000cwc.googlegroups.com...
> 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.
>

thx dave.. but i got #ref error

it doesn't seem to get the sheet name !!

actually i create a new worksheet for every week name week_1, week_2

and in a worksheet "Graphic" i added the new worksheet name after the
other

like this

week_1 | week_2 | ect . new week come here after.


and the next cell below it is the countif fomula linked to their
respective worksheet !

so something like this

week_1 | week_2
| ect . new week come here after.
---------------------------------------------------------------------------=
------------
=3Dcountif(week_1!$E:$E,"k25") | =3Dcountif(week_2!$E:$E,"k25")


i just want to know how to get the new whorksheet created to have the
formula below it with the right sheetname !!

hope you understand better


Dave Peterson a =E9crit :

> 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 =3D Worksheets("graphic")
> Set NewWks =3D Worksheets.Add
> NewWks.Name =3D Format(Date, "ddmmm")
>
> With GraphicWks
> Set DestCell =3D .Cells(.Rows.Count, "D").End(xlUp).Offset(1, 0)
> End With
>
> With DestCell
> .Formula =3D "=3Dcountif('" & NewWks.Name & "'!a2:a25,a2)"
> .Offset(1, 0).Formula =3D "=3Dcountif('" & NewWks.Name & "'!a2:a2=
5,a3)"
> End With
>
> End Sub
>
>
> Not sure where the next formula goes, so I went down column D.
>
> masterbigggy@gmail.com 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,"=3DA2") <------- this is what
> > i want
> > - in D3 formula countif("16aug!A2:A25,"=3DA3")
> >
> > 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.
>=20
> --=20
>=20
> Dave Peterson

I don't see how #ref! errors could show up. The code adds the sheet before the
formula is built...

But try this:

Option Explicit
Sub testme01()

Dim GraphicWks As Worksheet
Dim NewWks As Worksheet
Dim DestCell As Range
Dim wCtr As Long

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

For wCtr = 1 To 52
Set NewWks = Worksheets.Add
NewWks.Name = "week_" & wCtr
With DestCell
'headers in row above??
.Offset(-1, 0).Value = "'" & NewWks.Name
.Formula = "=countif('" & NewWks.Name & "'!a2:a25,a2)"
.Offset(1, 0).Formula = "=countif('" & NewWks.Name & "'!a2:a25,a3)"
End With
Set DestCell = DestCell.Offset(0, 1)
Next wCtr

End Sub


masterbigggy@gmail.com wrote:
>
> thx dave.. but i got #ref error
>
> it doesn't seem to get the sheet name !!
>
> actually i create a new worksheet for every week name week_1, week_2
>
> and in a worksheet "Graphic" i added the new worksheet name after the
> other
>
> like this
>
> week_1 | week_2 | ect . new week come here after.
>
> and the next cell below it is the countif fomula linked to their
> respective worksheet !
>
> so something like this
>
> week_1 | week_2
> | ect . new week come here after.
> ---------------------------------------------------------------------------------------
> =countif(week_1!$E:$E,"k25") | =countif(week_2!$E:$E,"k25")
>
> i just want to know how to get the new whorksheet created to have the
> formula below it with the right sheetname !!
>
> hope you understand better
>
> Dave Peterson a écrit :
>
> > 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.
> >
> > masterbigggy@gmail.com 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

--

Dave Peterson

this is pretty neat !! if i want to create the new sheet before the
first one ( left side ) what do i need to had in the formula ?

and is there a way to stop the macro or delete a sheet being created if
there another one with the same name ??

Thank you for your help i really appreciate it

Dave Peterson wrote:
> I don't see how #ref! errors could show up. The code adds the sheet befo=
re the
> formula is built...
>
> But try this:
>
> Option Explicit
> Sub testme01()
>
> Dim GraphicWks As Worksheet
> Dim NewWks As Worksheet
> Dim DestCell As Range
> Dim wCtr As Long
>
> Set GraphicWks =3D Worksheets("graphic")
> With GraphicWks
> Set DestCell =3D .Cells(.Rows.Count, "D").End(xlUp).Offset(1, 0)
> End With
>
> For wCtr =3D 1 To 52
> Set NewWks =3D Worksheets.Add
> NewWks.Name =3D "week_" & wCtr
> With DestCell
> 'headers in row above??
> .Offset(-1, 0).Value =3D "'" & NewWks.Name
> .Formula =3D "=3Dcountif('" & NewWks.Name & "'!a2:a25,a2)"
> .Offset(1, 0).Formula =3D "=3Dcountif('" & NewWks.Name & "'!a=
2:a25,a3)"
> End With
> Set DestCell =3D DestCell.Offset(0, 1)
> Next wCtr
>
> End Sub
>
>
> masterbigggy@gmail.com wrote:
> >
> > thx dave.. but i got #ref error
> >
> > it doesn't seem to get the sheet name !!
> >
> > actually i create a new worksheet for every week name week_1, week_2
> >
> > and in a worksheet "Graphic" i added the new worksheet name after the
> > other
> >
> > like this
> >
> > week_1 | week_2 | ect . new week come here after.
> >
> > and the next cell below it is the countif fomula linked to their
> > respective worksheet !
> >
> > so something like this
> >
> > week_1 | week_2
> > | ect . new week come here after.
> > -----------------------------------------------------------------------=
----------------
> > =3Dcountif(week_1!$E:$E,"k25") | =3Dcountif(week_2!$E:$E,"k25")
> >
> > i just want to know how to get the new whorksheet created to have the
> > formula below it with the right sheetname !!
> >
> > hope you understand better
> >
> > Dave Peterson a =E9crit :
> >
> > > 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 =3D Worksheets("graphic")
> > > Set NewWks =3D Worksheets.Add
> > > NewWks.Name =3D Format(Date, "ddmmm")
> > >
> > > With GraphicWks
> > > Set DestCell =3D .Cells(.Rows.Count, "D").End(xlUp).Offset(1,=
0)
> > > End With
> > >
> > > With DestCell
> > > .Formula =3D "=3Dcountif('" & NewWks.Name & "'!a2:a25,a2)"
> > > .Offset(1, 0).Formula =3D "=3Dcountif('" & NewWks.Name & "'!a=
2:a25,a3)"
> > > End With
> > >
> > > End Sub
> > >
> > >
> > > Not sure where the next formula goes, so I went down column D.
> > >
> > > masterbigggy@gmail.com 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 D=
1)
> > > > -in D2 formula countif("16aug!A2:A25,"=3DA2") <------- this is=
what
> > > > i want
> > > > - in D3 formula countif("16aug!A2:A25,"=3DA3")
> > > >
> > > > 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
>=20
> --=20
>=20
> Dave Peterson

maybe...

Option Explicit
Sub testme01()

Dim GraphicWks As Worksheet
Dim NewWks As Worksheet
Dim DestCell As Range
Dim wCtr As Long
Dim NewName As String
Dim TestWks As Worksheet

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

For wCtr = 1 To 52
NewName = "Week_" & wCtr

Set TestWks = Nothing
On Error Resume Next
Set TestWks = Worksheets(NewName)
On Error Resume Next

If TestWks Is Nothing Then
'it doesn't exist, so add it and do all the work
Set NewWks = Worksheets.Add(before:=Worksheets(1))
NewWks.Name = NewName
With DestCell
'headers in row above??
.Offset(-1, 0).Value = "'" & NewName
.Formula = "=countif('" & NewName & "'!a2:a25,a2)"
.Offset(1, 0).Formula = "=countif('" & NewName & "'!a2:a25,a3)"
End With
Set DestCell = DestCell.Offset(0, 1)
End If
Next wCtr

End Sub

masterbigggy@gmail.com wrote:
>
> this is pretty neat !! if i want to create the new sheet before the
> first one ( left side ) what do i need to had in the formula ?
>
> and is there a way to stop the macro or delete a sheet being created if
> there another one with the same name ??
>
> Thank you for your help i really appreciate it
>
> Dave Peterson wrote:
> > I don't see how #ref! errors could show up. The code adds the sheet before the
> > formula is built...
> >
> > But try this:
> >
> > Option Explicit
> > Sub testme01()
> >
> > Dim GraphicWks As Worksheet
> > Dim NewWks As Worksheet
> > Dim DestCell As Range
> > Dim wCtr As Long
> >
> > Set GraphicWks = Worksheets("graphic")
> > With GraphicWks
> > Set DestCell = .Cells(.Rows.Count, "D").End(xlUp).Offset(1, 0)
> > End With
> >
> > For wCtr = 1 To 52
> > Set NewWks = Worksheets.Add
> > NewWks.Name = "week_" & wCtr
> > With DestCell
> > 'headers in row above??
> > .Offset(-1, 0).Value = "'" & NewWks.Name
> > .Formula = "=countif('" & NewWks.Name & "'!a2:a25,a2)"
> > .Offset(1, 0).Formula = "=countif('" & NewWks.Name & "'!a2:a25,a3)"
> > End With
> > Set DestCell = DestCell.Offset(0, 1)
> > Next wCtr
> >
> > End Sub
> >
> >
> > masterbigggy@gmail.com wrote:
> > >
> > > thx dave.. but i got #ref error
> > >
> > > it doesn't seem to get the sheet name !!
> > >
> > > actually i create a new worksheet for every week name week_1, week_2
> > >
> > > and in a worksheet "Graphic" i added the new worksheet name after the
> > > other
> > >
> > > like this
> > >
> > > week_1 | week_2 | ect . new week come here after.
> > >
> > > and the next cell below it is the countif fomula linked to their
> > > respective worksheet !
> > >
> > > so something like this
> > >
> > > week_1 | week_2
> > > | ect . new week come here after.
> > > ---------------------------------------------------------------------------------------
> > > =countif(week_1!$E:$E,"k25") | =countif(week_2!$E:$E,"k25")
> > >
> > > i just want to know how to get the new whorksheet created to have the
> > > formula below it with the right sheetname !!
> > >
> > > hope you understand better
> > >
> > > Dave Peterson a écrit :
> > >
> > > > 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.
> > > >
> > > > masterbigggy@gmail.com 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
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson