Filtering Data To New Sheets With Macro

[crashing1912]

Hi all,

I'm currently using Excel 2010 and am fairly new to the VBA language and would appreciate any help. What I need to do is to filter through my list of clients (number of clients could change month to month) and copy and paste all data (with headers) for each client into a new worksheet.

<TABLE style="WIDTH: 384pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=509><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" span=3 width=98><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 74pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=98>INV_NO</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 74pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=98>PRODUCT</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 74pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=98>EST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 88pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=117>SERVICE_DATE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 74pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=98>CLT</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; HEIGHT: 15pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 height=20 width=98>09IN3047</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>CAR</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>42</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=117>JUN/11</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5 0.5pt solid; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>A</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; HEIGHT: 15pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 height=20 width=98>09IN3048</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>CAR</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>43</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=117>JUL/11</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>A</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; HEIGHT: 15pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 height=20 width=98>09IN3048</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>CAR</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>43</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=117>SEP/11</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>A</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; HEIGHT: 15pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 height=20 width=98>09IN3049</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>GEN</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>47</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=117>JUL/11</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>B</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; HEIGHT: 15pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 height=20 width=98>09IN3049</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>GEN</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>47</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=117>SEP/11</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>B</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; HEIGHT: 15pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 height=20 width=98>09IN3050</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>GEN</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>57</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=117>SEP/11</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>B</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; HEIGHT: 15pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 height=20 width=98>09IN3051</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>PSF</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>43</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=117>SEP/11</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>C</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; HEIGHT: 15pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 height=20 width=98>09IN3052</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>TRU</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>45</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=117>SEP/11</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>C</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; HEIGHT: 15pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 height=20 width=98>09IN3053</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>TRU</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>46</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=117>SEP/11</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>D</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; HEIGHT: 15pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 height=20 width=98>09IN3054</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>TRU</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>47</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=117>SEP/11</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>E</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; HEIGHT: 15pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 height=20 width=98>09IN3055</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>TRU</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>48</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=117>SEP/11</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 width=98>E</TD></TR></TBODY></TABLE>

I've tried to do this myself using some loop statements, but with no success and I wasn't able to find anything too useful online, so any thoughts would helpful. Thanks in advance.

 

[Trevor G]

This code will pick out a Invoice Number

If you want to split data into separate sheets consider using a Pivot Table as you can place the Clients in a Page option and in the Tabs you can then use the page report to separate the data into individual sheets

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> copyRegion()<br><SPAN style="color:#00007F">Dim</SPAN> R <SPAN style="color:#00007F">As</SPAN> Range<br> Selection.AutoFilter<br>    ActiveSheet.Range("$A$1:$H$500").AutoFilter Field:=1, Criteria1:="09IN3048"<br><SPAN style="color:#00007F">With</SPAN> ActiveCell.CurrentRegion<br><SPAN style="color:#00007F">Set</SPAN> R = Range(.Cells(1, 1), .Cells(.Cells.Count))<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>R.Copy<br>Sheets.Add<br>ActiveSheet.Paste<br>Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[crashing1912]

Hey Trevor, thanks for your quick response. I should have explained a little more what I'm trying to do. I have around 3,000 lines of invoices for 50 clients, but the clients vary month to month. Once each client has it's own tab with their associated invoice data, the file is shared and the end user is able to look at their assigned clients and take the data to finish the billing process.

I think your code will work for the filtering and copy/pasting portion, but I need to be able to have the code repeat for each client listed. I'll continue to try and figure this part out, but any help anyone can provide would be appreciated. Thanks again Trevor!

 

[Trevor G]

Try the Pivot table route as mentioned you can get a separate page through this tool so you can then separate your master list into individual sheets.