Help With Macro to Create Sheets From Master

[gaspower]

Hello,
I have a master sheet(sheet1),

I need to be able to create three sheets from the master, 811, 811B, 811E. Does not mater if sheets are called the 811's, can just be sheet2, sheet3 etc. I can rename later. But what I would like the macro to do, is on each sheet created, have the corresponding products in the sheet denoted by the "X".

Example:
811

811B

811E

Thanks JR

 

[jbeaucaire]

Give this a try:

Option Explicit

Sub ParseData()
Dim ws As Worksheet, sName As String
Dim Col As Long, LR As Long, LC As Long
Application.ScreenUpdating = False

With Sheets("Master")
    LC = .Cells(1, .Columns.Count).End(xlToLeft).Column
    .AutoFilterMode = False
    .Rows(1).AutoFilter
    For Col = 3 To LC
        sName = .Cells(1, Col)
        If Not Evaluate("ISREF('" & sName & "'!A1)") Then
            Sheets.Add(After:=Sheets(Sheets.Count)).Name = sName
        Else
            Sheets(sName).UsedRange.Clear
        End If
        .Rows(1).AutoFilter Field:=Col, Criteria:="*"
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        If LR > 1 Then .Range("A1:B" & LR).Copy Sheets(sName).Range("A1")
        .Rows(1).AutoFilter Field:=Col
    Next Col
    .AutofilterMode = False
    .Activate
End With

Application.ScreenUpdating = True
End Sub

 

[gaspower]

Hello,

thank you very much for the reply. I tried the macro, but I get an visual basic error 400.

JR

 

[gaspower]

Sorry, forgot to mention, it creates one sheet 811, no data.

JR

 

[jbeaucaire]

Sorry, my bad, little typo. I went ahead and added a formatting tweak, too.

Option Explicit

Sub ParseData()
Dim ws As Worksheet, sName As String
Dim Col As Long, LR As Long, LC As Long
Application.ScreenUpdating = False

With Sheets("Master")
    LC = .Cells(1, .Columns.Count).End(xlToLeft).Column
    .AutoFilterMode = False
    .Rows(1).AutoFilter
    For Col = 3 To LC
        sName = .Cells(1, Col)
        If Not Evaluate("ISREF('" & sName & "'!A1)") Then
            Sheets.Add(After:=Sheets(Sheets.Count)).Name = sName
        Else
            Sheets(sName).UsedRange.Clear
        End If
        .Rows(1).AutoFilter Field:=Col, Criteria1:="*"
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        If LR > 1 Then
            .Range("A1:B" & LR).Copy Sheets(sName).Range("A1")
            Sheets(sName).Columns.AutoFit
        End If
        .Rows(1).AutoFilter Field:=Col
    Next Col
    .AutoFilterMode = False
    .Activate
End With

Application.ScreenUpdating = True
End Sub

 

[gaspower]

Hello,

I really want to thank you for helping me. I think we are very close. Sheet 811 and 811B are created fine. Sheet 811E does not get created correctly. For example 1311 ad 1315 are added to the list when there is no "X" in the field of the master.

Thanks JR

 

[jbeaucaire]

This is what I get when I run it:

Excel Workbook
	A	B
1	Part #	Product List
2	1312	Sample Product 2
3	1321	Sample Product 4
4	1337	Sample Product 5
5	1383	Sample Product 6
6	1394	Sample Product 7
7	1606	Sample Product 10
811E

I'm going to "guess" that the cells in column 811E aren't really "blank" where they appear to be. Perhaps change that to:

.Rows(1).AutoFilter Field:=Col, Criteria1:="x"

 

[gaspower]

Hello,

That worked. Thank you so much. I really appreciate the help.

JR