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.

vba countif linked to another sheet
 

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

vba countif linked to another sheet
 

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

--
HTH

Bob Phillips

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

wrote in message
ups.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.

vba countif linked to another sheet
 

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.

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

vba countif linked to another sheet
 

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


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.

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

vba countif linked to another sheet
 

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


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.

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

vba countif linked to another sheet
 

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(befo=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

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


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.

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