# Filter col B in Sht1 and paste the results in multiple sheets based on creteria

#### aravindhan_31

##### Well-known Member
Hi,

I Have some data in sheet1
There are 5 column as below
Name,ID,Net Amount,Supppier,Desciptions.

I have around 2000 rows of data, Column B have same ID repeated many times the IDs are 002,003,004,007,008 etc around 10 Different ids. Each Id may have 200 rows of data.

There are seperate sheets for each ID in the same work book like 002,003,004,007 ( 10 sheets)

What I am doing manually now is ?
Add a filter to B Bolumn, Filter by 002, I have the result data of only 002,
I will copy the result and paste in sheet 002 like below
Copy the from sheet1 column A result and paste the same in sheet 002 Column C
Copy the from sheet1 Column B result and paste the same in Sheet 002 Column A
Copy the from sheet1 Column C result and paste the same in Sheet 002 Column I
Copy the from sheet1 Column E result and paste the same in Sheet 002 Column K

similarly i need to do for 003, that is filter Column B by 003,
I will copy the result and paste in sheet 003 like below
Copy the from sheet1 column A result and paste the same in sheet 003 Column C
Copy the from sheet1 Column B result and paste the same in Sheet 003 Column A
Copy the from sheet1 Column C result and paste the same in Sheet 003 Column I
Copy the from sheet1 Column E result and paste the same in Sheet 003 Column K

I need to do like this for all the 10 Ids.. is there a way to automate this? pasting destination is always C,A,I,K in all the sheets.

Arvind

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### aravindhan_31

##### Well-known Member
Re: Solved: Filter col B in Sht1 and paste the results in multiple sheets based on creteria

Hi,

I got it.. i recorded a macro by filter then select the whole column then visible cells then copy & paste..and it worked.

thanks..

#### jasonb75

##### Well-known Member
Re: Solved: Filter col B in Sht1 and paste the results in multiple sheets based on creteria

If you need to re use the macro try this one (on a COPY of your file).

This was a quick re-hash of something I use, just had to change columns and delete a couple of IF lines that you wouldn't need for this.

Might be a little slower than your recorded macro, but it will create and name the sheets for you if they don't exist, and does a row count so it doesn't miss anything.

Code:
``````Sub filter_to_sheet()
Application.ScreenUpdating = False
Sheet1.Select
For a = 2 To Cells(Rows.Count, 2).End(xlUp).Row

destsht = Sheet1.Range("B" & a).Text
On Error Resume Next
If Not Worksheets(destsht).Name = destsht Then
End If
Sheets(destsht).Select
lr = Cells(Rows.Count, 1).End(xlUp).Row + 1
Range("C" & lr) = Sheet1.Range("A" & a)
Range("A" & lr) = Sheet1.Range("B" & a)
Range("I" & lr) = Sheet1.Range("C" & a)
Range("K" & lr) = Sheet1.Range("E" & a)
Next a
Application.ScreenUpdating = True
End Sub``````

#### aravindhan_31

##### Well-known Member
Hi,

Thanks for the code, I am getting an debug in Sheet1.select... I am going wrong somewhere..

Arvind..

#### jbeaucaire

##### Well-known Member
Code:
``Sheets(1).Select``

Replies
0
Views
388
Replies
5
Views
565
Replies
3
Views
543
Replies
0
Views
369
Replies
15
Views
2K

1,195,716
Messages
6,011,274
Members
441,598
Latest member
chrispaulpearce

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back