Jonnathanjons
Member
0
I would like your help in solving a problem I am facing in a countif function from the main sheet "Head Quarters" to another sheets "Laboratory" and "Operations" based on the Columns in the main sheet. but column reference change every month but the name remains the same. so I cannot use column reference in Countif vba scripts. I like to have the their counts occurred in their respective sheets based on the occurrence in the main sheet . I created a vba script but cannot manage to find scripts to pull based on name in the last part of the code where it says " Set rng = ws.Range("AH2:AH" & lr)" I need your help here. Pls review this code and simplify this code if possible to achieve this results..Please note that reference in the main sheet cannot be to a specific column but can only based on column name as "Laboratory" or "Operations" because it keep changing every month. Please see the sample spreadsheet I created for this data and also the VB script I wrote.
Source Sheet as my input as below
Result should be like below and Descending order in their respective sheets.
'Please see the code. It create a new sheet Laboratory and Operations'
>>> use code - tags <<<
I would like your help in solving a problem I am facing in a countif function from the main sheet "Head Quarters" to another sheets "Laboratory" and "Operations" based on the Columns in the main sheet. but column reference change every month but the name remains the same. so I cannot use column reference in Countif vba scripts. I like to have the their counts occurred in their respective sheets based on the occurrence in the main sheet . I created a vba script but cannot manage to find scripts to pull based on name in the last part of the code where it says " Set rng = ws.Range("AH2:AH" & lr)" I need your help here. Pls review this code and simplify this code if possible to achieve this results..Please note that reference in the main sheet cannot be to a specific column but can only based on column name as "Laboratory" or "Operations" because it keep changing every month. Please see the sample spreadsheet I created for this data and also the VB script I wrote.
Source Sheet as my input as below
Result should be like below and Descending order in their respective sheets.
'Please see the code. It create a new sheet Laboratory and Operations'
>>> use code - tags <<<
Code:
Sub List_Unique_Values()
Dim rSelection As Range
Dim qSelection As Range
Dim ws As Worksheet
Dim rng1 As Range
Dim rng2 As Range
ShtName = "Laboratory"
ShtName1 = "Operations"
Set ws = Sheets("Head Quarters")
'This will create a sheet 'Laboratory' and copy the data from main sheet and remove duplicate
If Evaluate("isref('" & ShtName & "'!A1)") Then
'Application.CutCopyMode = False
GoTo Skip
Else
Worksheets("Head Quarters").Select
On Error Resume Next
Set rSelection = Rows("1:1").Find(ShtName, , xlValues, xlWhole, , , True)
rSelection.Range(Range("A1"), Range("A1").End(xlDown)).Copy
Set ws = Worksheets.add(After:=ActiveSheet)
ws.Name = "Laboratory"
End If
With ws.Range("A1")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
'.PasteSpecial xlPasteValuesAndNumberFormats
End With
'Remove duplicates
ws.UsedRange.RemoveDuplicates Columns:=1, Header:=xlYes
'Remove blank cells (optional)
On Error Resume Next
ws.UsedRange.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlShiftUp
On Error GoTo 0
'Autofit column
ws.Columns("A").AutoFit
ws.Range("A1").Select
Skip:
Set ws = Sheets("Head Quarters")
Set ws1 = Sheets("Laboratory")
lr = ws.Cells(Cells.Rows.Count, rSelection.Column).End(xlUp).Row
lRow = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row
lRow1 = ws1.Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set rng = ws.Range("AH2:AH" & lr) '<<<<<< I need help here to choose the range by column header name
'Set rng = ws.Range("AH2:lr" & lr)
With ws1
.Range("A1").Copy .Range("B1")
.Range("B1") = "Rows"
.Range("B2:B" & lRow).Formula = "=COUNTIF('" & ws.Name & "'!" & rng.Address & ",A2)"
.Range("B2:B" & lRow).Value = ws1.Range("B2:B" & lRow).Value
' ws1.Columns.AutoFit
End With
On Error Resume Next
Set rng1 = ws1.Range(Range("A1"), Range("B1").End(xlDown))
rng1.Sort key1:=Range("B1"), order1:=xlDescending, Header:=xlYes
'Similarly create another sheet for 'Operations' and summarize and do countif based on column name
End Sub
Attachments
Last edited by a moderator: