Insert a part code and name ending with "C"

[PRADEEPB270]

A file having 297 sheets and the same format which I have attach here.But here is an example of one sheet and some rows datas.
I want to VBA codes to fill up the 'Part code' and 'Part Name' in column 'K' and 'L' only having end with 'C'only.Can this problem sort out through VBA Codes?

For details,refer attach file.

[Rudi]

Hi Pradeep,

Do you want one list on a single sheet created that contains all 297 sheets data that end in "C"
or
Do you want each sheet of the 297 to have it's own list of codes ending in "C" created?

[PRADEEPB270]

Sir,I want each sheet of the 297 to have it's own list of codes ending in "C" created.

[Rudi]

Try this code...

Code: Select all

Sub CreateCList()
Dim sh As Worksheet
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each sh In Worksheets
        sh.Copy before:=sh
        ActiveSheet.Name = sh.Name & "_temp"
        Rows("1:3").Delete Shift:=xlUp
        Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        ActiveSheet.Range("B1").CurrentRegion.AutoFilter Field:=1, Criteria1:="=*C"
        Range("B1").CurrentRegion.Resize(, 2).Copy
        sh.Range("K1").PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        Sheets(sh.Name & "_temp").Delete
        sh.Columns.AutoFit
        Range("A1").Select
    Next sh
    Worksheets(1).Select
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

[PRADEEPB270]

Macro is working so nice.Thanks Rudi sir for your cooperation.

[Rudi]

TX.

Please note:
Can you change the last line in your code:

Code: Select all

 
    Application.ScreenUpdating = False
End Sub

so it reads...

Code: Select all

 
    Application.ScreenUpdating = True
End Sub

I forgot to change it to true...

[PRADEEPB270]

ok.Changed it.Thanks.