Wanting to use a macro to copy from a list and paste values in a filter... Any help is appreciated.

[MikeGo]

Looking to use a macro to index down a list copying the value and toggle to a worksheet and paste value into the filter to look at different project details ..

Any ideas on how to use a macro to filter for one specific project would be appreciated..

Thanks,

 

[apo]

Hi..

Your post lacks a few details.. but you should be able to apply this to what you need..

Basically.. it does the following:

1. Loops through a List of filter values on Sheet1 from A2 to the Last used row in A.
2. Applies each Filter value to Sheet2 for the range (A2:C" & Last used row)... (Header row is in row 2)
3. Displays the result on Sheet 2 and prompts the user to continue loop to next filter value or to quit filtering. (Displaying what the next Filter value is on the Popup)

Just add a CommandButton1 to Sheet1 and put your data as shown below to test and then adapt..

Private Sub CommandButton1_Click()
Dim Crit As String, Ans As String
Dim i As Long, LastRow As Long, LastRow2 As Long


LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
LastRow2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row


For i = 2 To LastRow
Crit = Sheets("Sheet1").Cells(i, 1).Value
Sheets("Sheet2").Range("A2:C" & LastRow2).AutoFilter Field:=1, Criteria1:=Crit


If Sheets("Sheet1").Cells(i, 1).Row = LastRow Then Exit Sub


Sheets("Sheet2").Activate
Ans = MsgBox("Go To Next Filter Value?" & Chr$(10) & Chr$(10) & "Next Value = " & Sheets("Sheet1").Cells(i + 1, 1).Value, vbYesNo)


If Ans = vbYes Then
GoTo 1
ElseIf Ans = vbNo Then Exit Sub
End If
1
Next i
End Sub

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:214px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">2</td></tr><tr style="height:22px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">1</td></tr><tr style="height:22px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">4</td></tr><tr style="height:22px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">3</td></tr><tr style="height:22px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">6</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:191px;" /><col style="width:159px;" /><col style="width:97px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#008080; font-weight:bold; ">Key</td><td style="background-color:#008080; font-weight:bold; ">Description</td><td style="background-color:#008080; font-weight:bold; ">Data</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#ffcc99; font-weight:bold; font-family:Arial; text-align:left; ">1</td><td style="background-color:#ffcc99; font-weight:bold; font-family:Arial; text-align:left; ">aaa</td><td style="background-color:#ffcc99; font-weight:bold; text-align:right; ">Yes</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#ffcc99; font-weight:bold; font-family:Arial; text-align:left; ">2</td><td style="background-color:#ffcc99; font-weight:bold; font-family:Arial; text-align:left; ">bbb</td><td style="background-color:#ffcc99; font-weight:bold; text-align:right; ">No</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#ffcc99; font-weight:bold; font-family:Arial; text-align:left; ">3</td><td style="background-color:#ffcc99; font-weight:bold; font-family:Arial; text-align:left; ">ccc</td><td style="background-color:#ffcc99; font-weight:bold; text-align:right; ">Yes</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#ffcc99; font-weight:bold; font-family:Arial; text-align:left; ">2</td><td style="background-color:#ffcc99; font-weight:bold; font-family:Arial; text-align:left; ">bbb</td><td style="background-color:#ffcc99; font-weight:bold; text-align:right; ">No</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#ffcc99; font-weight:bold; font-family:Arial; text-align:left; ">4</td><td style="background-color:#ffcc99; font-weight:bold; font-family:Arial; text-align:left; ">ddd</td><td style="background-color:#ffcc99; font-weight:bold; text-align:right; ">Yes</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#ffcc99; font-weight:bold; font-family:Arial; text-align:left; ">1</td><td style="background-color:#ffcc99; font-weight:bold; font-family:Arial; text-align:left; ">aaa</td><td style="background-color:#ffcc99; font-weight:bold; text-align:right; ">No</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#ffcc99; font-weight:bold; font-family:Arial; text-align:left; ">3</td><td style="background-color:#ffcc99; font-weight:bold; font-family:Arial; text-align:left; ">ccc</td><td style="background-color:#ffcc99; font-weight:bold; text-align:right; ">Yes</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#ffcc99; font-weight:bold; font-family:Arial; text-align:left; ">6</td><td style="background-color:#ffcc99; font-weight:bold; font-family:Arial; text-align:left; ">fff</td><td style="background-color:#ffcc99; font-weight:bold; text-align:right; ">No</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[MikeGo]

Hi..

Your post lacks a few details.. but you should be able to apply this to what you need..

Basically.. it does the following:

1. Loops through a List of filter values on Sheet1 from A2 to the Last used row in A.
2. Applies each Filter value to Sheet2 for the range (A2:C" & Last used row)... (Header row is in row 2)
3. Displays the result on Sheet 2 and prompts the user to continue loop to next filter value or to quit filtering. (Displaying what the next Filter value is on the Popup)

Just add a CommandButton1 to Sheet1 and put your data as shown below to test and then adapt..

Private Sub CommandButton1_Click()
Dim Crit As String, Ans As String
Dim i As Long, LastRow As Long, LastRow2 As Long


LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
LastRow2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row


For i = 2 To LastRow
Crit = Sheets("Sheet1").Cells(i, 1).Value
Sheets("Sheet2").Range("A2:C" & LastRow2).AutoFilter Field:=1, Criteria1:=Crit


If Sheets("Sheet1").Cells(i, 1).Row = LastRow Then Exit Sub


Sheets("Sheet2").Activate
Ans = MsgBox("Go To Next Filter Value?" & Chr$(10) & Chr$(10) & "Next Value = " & Sheets("Sheet1").Cells(i + 1, 1).Value, vbYesNo)


If Ans = vbYes Then
GoTo 1
ElseIf Ans = vbNo Then Exit Sub
End If
1
Next i
End Sub

Sheet1

*	A

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 214px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]6[/TD]

</TBODY>

Excel tables to the web - Excel Jeanie Html 4

Sheet2

*	A	B	C
*	*	*

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 191px"><COL style="WIDTH: 159px"><COL style="WIDTH: 97px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #008080"]Key[/TD]
[TD="bgcolor: #008080"]Description[/TD]
[TD="bgcolor: #008080"]Data[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #ffcc99, align: left"]1[/TD]
[TD="bgcolor: #ffcc99, align: left"]aaa[/TD]
[TD="bgcolor: #ffcc99, align: right"]Yes[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #ffcc99, align: left"]2[/TD]
[TD="bgcolor: #ffcc99, align: left"]bbb[/TD]
[TD="bgcolor: #ffcc99, align: right"]No[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="bgcolor: #ffcc99, align: left"]3[/TD]
[TD="bgcolor: #ffcc99, align: left"]ccc[/TD]
[TD="bgcolor: #ffcc99, align: right"]Yes[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #ffcc99, align: left"]2[/TD]
[TD="bgcolor: #ffcc99, align: left"]bbb[/TD]
[TD="bgcolor: #ffcc99, align: right"]No[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="bgcolor: #ffcc99, align: left"]4[/TD]
[TD="bgcolor: #ffcc99, align: left"]ddd[/TD]
[TD="bgcolor: #ffcc99, align: right"]Yes[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #ffcc99, align: left"]1[/TD]
[TD="bgcolor: #ffcc99, align: left"]aaa[/TD]
[TD="bgcolor: #ffcc99, align: right"]No[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="bgcolor: #ffcc99, align: left"]3[/TD]
[TD="bgcolor: #ffcc99, align: left"]ccc[/TD]
[TD="bgcolor: #ffcc99, align: right"]Yes[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="bgcolor: #ffcc99, align: left"]6[/TD]
[TD="bgcolor: #ffcc99, align: left"]fff[/TD]
[TD="bgcolor: #ffcc99, align: right"]No[/TD]

</TBODY>

Excel tables to the web - Excel Jeanie Html 4

???? I am a record the macro key .. type user ..

If you can tell me how to get your visual basic routine up and runnign I can make the necessary changes but right now I'm a bit stumped ..

Thanks, ( and yes, I probably should make the leap into the VB world .. )

 

[MikeGo]

Hi .. Actually got this to work indexing down the page .. but I want to print out each item as it is indexing down... can you assist??

Thanks.

 

[apo]

Hi..

So you want to print the visible rows on sheet2 after each autofilter criteria is applied?

Is that correct?

 

[MikeGo]

Hi..

So you want to print the visible rows on sheet2 after each autofilter criteria is applied?

Is that correct?

Yes, I want to print each of the filter lists and then cycle down through the list until it ends .. It doesnt need to ask each time if I want to print more .. but that not a bad safegaurd ...I truly appreciate the help ... any suggestions on how I can learn the basics of VB in excel is much appreciated as well .. I think I need to work smarter...

 

[MikeGo]

Found the sentence .. Sheets("Sheet2").PrintOut Copies:=1 Thanks...

Any suggestions for a resource to perform a few various sub-routines?? I am sure I could be working a bit more efficiently with a bit more VBA coding ..

Thanks...