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

[aravindhan_31]

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.

Thanks in advance for your helps
Arvind

 

[aravindhan_31]

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]

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.

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
           Worksheets.Add.Name = destsht
        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]

Hi,

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

Arvind..

 

[jbeaucaire]

try this instead:

Sheets(1).Select