Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to copy a linked sheet | Excel Discussion (Misc queries) | |||
How do I add every other coloums in a linked sheet? | Excel Discussion (Misc queries) | |||
CountIF Function On Linked Spreadsheet | Excel Discussion (Misc queries) | |||
Transfer/Copy Sheet, Dynamically linked Sheet | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions |