vba countif linked to another sheet

[masterbigggy]

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]

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.

 

[Bob Phillips]

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

--
HTH

Bob Phillips

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

 

[masterbigggy]

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 :

 

[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]

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]

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

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