Learning
10-27-2020, 01:51 PM
Hi,
I have a work tracker that has several columns before I have a "calendar" which is on row 3 for all the dates of the work year for me.
Underneath the dates I have letters corresponding to the people. That work in my office. There are 4 letters that I use and the letter to look for will. Be in cell K1 if the sheet named Main.
What I want the code to do is to go though each of the rows I have information on and then look in each cell if there is a letter that matches the letter in K1.
If it finds a letter match then I want the program to copy the information on cell column E row (whichever row the code is looking at) and paste it on sheet called VISITS and the way I tried to use the variable r to go down on the rows of the page VISITS
CODE AS I HAVE IT
Private Sub CommandButton9_Click()
' Will generate a table of visits for each rep
' First part hides rows that don't match the Stds Rep
Dim b As Integer
For b = 4 To 45
If Cells(b, 3).Value = Range("K1").Value Then
Sheets("Main").Rows(b & ":" & b).EntireRow.Hidden = False
Else
Sheets("Main").Rows(b & ":" & b).EntireRow.Hidden = True
End If
Next
' Finds the column that "today" is on.
Dim a As Integer
a = 106
Do Until Cells(3, a).Value = Date
Columns(a).EntireColumn.Hidden = True
a = a + 1
Loop
Cells(3, a).Select
Dim r As Integer
r = 5
' look in a row
For a = a To 416
Cells(4, a).Select
If ActiveCell.Value = Range("K1").Value Then
Sheets("VISITS").Range("B" & r).Value = Sheets("Main").Range("E" & a).Value
r = r + 1
Else
End If
Next
End Sub
Thank you very much in advance.
I have a work tracker that has several columns before I have a "calendar" which is on row 3 for all the dates of the work year for me.
Underneath the dates I have letters corresponding to the people. That work in my office. There are 4 letters that I use and the letter to look for will. Be in cell K1 if the sheet named Main.
What I want the code to do is to go though each of the rows I have information on and then look in each cell if there is a letter that matches the letter in K1.
If it finds a letter match then I want the program to copy the information on cell column E row (whichever row the code is looking at) and paste it on sheet called VISITS and the way I tried to use the variable r to go down on the rows of the page VISITS
CODE AS I HAVE IT
Private Sub CommandButton9_Click()
' Will generate a table of visits for each rep
' First part hides rows that don't match the Stds Rep
Dim b As Integer
For b = 4 To 45
If Cells(b, 3).Value = Range("K1").Value Then
Sheets("Main").Rows(b & ":" & b).EntireRow.Hidden = False
Else
Sheets("Main").Rows(b & ":" & b).EntireRow.Hidden = True
End If
Next
' Finds the column that "today" is on.
Dim a As Integer
a = 106
Do Until Cells(3, a).Value = Date
Columns(a).EntireColumn.Hidden = True
a = a + 1
Loop
Cells(3, a).Select
Dim r As Integer
r = 5
' look in a row
For a = a To 416
Cells(4, a).Select
If ActiveCell.Value = Range("K1").Value Then
Sheets("VISITS").Range("B" & r).Value = Sheets("Main").Range("E" & a).Value
r = r + 1
Else
End If
Next
End Sub
Thank you very much in advance.