hi
I have data in sheet MAIN the result should be after it so it should split data based on COL B,C,D and the sheets names should be based on headers when create the new sheets after split them . every time increase data in sheet MAIN then should updating in the created sheets . so the result in sheets IMPORT,EXPORT ,RETURNS
sorry if it's poor detailes , but I think the result after sheet main will be clear . if it's not clear please inform me
Hi ! Try this !
According to your attachment this is the EZ advanced filter beginner way but for good enough readers only !
Once sheets are created with their names according to the MAIN sheet headers
with just the row #1 filled with only the necessary headers (same as MAIN sheet, no typo allowed)
then the sheet is updated when it is activated just pasting this event code to the ThisWorkbook module :
PHP Code:Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim V
With Sheets("MAIN")
V = Application.Match(Sh.Name, .UsedRange.Rows(1), 0): If IsError(V) Then Exit Sub
Sh.UsedRange.Offset(1).Clear
.[Z2].Formula = "=" & .Cells(2, V).Address(0) & ">0"
.[A1].CurrentRegion.AdvancedFilter 2, .[Z1:Z2], Sh.[A1].CurrentRegion.Rows(1)
.[Z2].Clear
End With
End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Hi KalilMe,
Try below code, if the sheet doesn't exist, the code will create one otherwise it will just update it based on any change you make in columns E, F or G in "MAIN" sheet. Right click the "MAIN" sheet tab then click on View Code then insert the below code
Private Sub Worksheet_Change(ByVal Target As Range) Dim Txt$: If Target.CountLarge > 1 Then Exit Sub If Not Intersect(Target, [E:G]) Is Nothing Then If Target.Column = 5 Then Txt = "IMPORT" Else Txt = IIf(Target.Column = 6, "EXPORT", "RETURNS") If Not Evaluate("isref(" & Txt & "!a1)") Then Sheets.Add(, ActiveSheet).Name = Txt Else Sheets(Txt).UsedRange.ClearContents [M1:P1] = [{"ITEM","GOODS","TYPE","PR"}]: [Q1] = Txt: [Q2] = "<>" Sheets(Txt).[A1:E1] = [M1:Q1].Value [A1].CurrentRegion.AdvancedFilter 2, [M1].CurrentRegion, Sheets(Txt).[A1:E1] [M1:Q1].CurrentRegion.Clear With Sheets(Txt).[A1].CurrentRegion .Borders.LineStyle = 1: .Rows(1).Interior.Color = 12566463: .Rows(1).Font.Bold = True .Columns(1).Offset(1).Resize(.Rows.Count - 1) = Evaluate("row(1:" & .Rows.Count & ")") End With End If End Sub
If I was able to help, you can thank me by clicking the * Add Reputation under my user name
Based on what i understood , this should work:
I also attached your file with the macroSub test() j = 5 For i = 1 To 3 Sheets("MAIN").Range("A1:H1").AutoFilter Field:=j, Criteria1:="<>" column_letter = Split(Cells(1, j).Address, "$")(1) Sheets("MAIN").Activate Sheets("MAIN").Range("A:D," & column_letter & ":" & column_letter).Select Selection.Copy sheet_name = Sheets("MAIN").Cells(1, j).Value Sheets.Add(After:=Sheets("MAIN")).Name = sheet_name ActiveSheet.Paste ActiveSheet.Columns.AutoFit ActiveSheet.Range("A1").Select j = j + 1 Next i Sheets("MAIN").AutoFilterMode = False End Sub
"Good Morning, Good Evening and Good Night"
@Marc L & nankw83 your codes works perfectly
thanks so much
@iustin thanks for your trying but some sheets RETURNS ,EXPORT have some data are missed
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
KalilMe 
Register To Reply
Bookmarks