Having problem with autofilter vba

[sheikh talib]

Hi everyone,
I am new to this forum & have a little knowledge about vba.I have made a workbook for my work,everything is working correctly except the two macros I entered at the end they autofilter data & copy & paste specific rows to sheets.I don't know what I have done wrong ,when I ran macro it disturbed my data sheet.
Can someone help me please,is there any other easy way to solve my problem,if someone make it decent looking I will be grateful to him because stock sheets,sell sheets & customers sheets should be differentiate easily.

[mancubus]

welcome to VBAX.

your workbook contains 8 modules. so finding the correct macro is not easy.
pls paste your macro here as well.

try this:


Sub ertert()
'http://www.excelforum.com/excel-programming/803630-copy-rows-to-new-worksheet-based-on-value-in-column.html


Dim wsh As Worksheet

Application.ScreenUpdating = False

With ThisWorkbook.Sheets("TOTAL SALE")
.AutoFilterMode = False
For Each wsh In ThisWorkbook.Worksheets
If wsh.Name <> "TOTAL SALE" Then
.Range("A6:P" & .Cells(.Rows.Count, 7).End(xlUp).Row).AutoFilter Field:=4, Criteria1:=wsh.Name
With .AutoFilter.Range
.Offset(1).Resize(.Rows.Count - 1).Copy wsh.Range("A7")
End With
End If
Next wsh
.AutoFilterMode = False
End With

Application.ScreenUpdating = True


End Sub

[Jacob Hilderbrand]

Cross Posted Here: http://www.excelforum.com/excel-programming-vba-macros/1006755-having-problem-with-autofilter-vba.html

It is fine to post questions on multiple forums but as a courtessy to the people responding, post a link so time is not spent answering questions that were already answered elsewhere.

[sheikh talib]

Thank you Jacob,I have tried what you suggested but it didn't work,mistake i have made is in last two module but i don't know what it is excel says about a cell reference.
what you said about posting a link,simply i don't know about it.

[sheikh talib]

thank you mancubus,I have tried what you suggested but it didn't work,mistake i have made is in last two module but i don't know what it is excel says about a cell reference.sorry i replied to jacob about formula.

[mancubus]

it worked for me. your total sale worksheet contains only one unique COMP value which is BLUE. so the code copied blues in total sale to worksheet blue.

the same logic has been working for me for years when i want to split data into multiple worksheets.

and what does "did not work" mean? what happened?



ps: i omit the formula errors. it's your file and you should correct the errors. review your formulas to correct the circular references in them.

[sheikh talib]

when i filter data it goes to invoice & data sheet as well,it is happening with both filter codes

[mancubus]

Cross Posted Here: http://www.excelforum.com/excel-programming-vba-macros/1006755-having-problem-with-autofilter-vba.html

It is fine to post questions on multiple forums but as a courtessy to the people responding, post a link so time is not spent answering questions that were already answered elsewhere.

sheikh talib pls refer:
http://www.vbaexpress.com/forum/showthread.php?18537-Cross-Posting

i copied this thread's link to the thread in excelforum.

[mancubus]

sorry, my bad.


try this.
it creates a unique list form column D in total sales then filters and copies data to related tabs.


when designing a workbook, make sure that worksheet manes are identical with the data.
so if value is SINO then name worksheet as SINO, not sino. it may cause problems as in my case.



Sub split_into_sheets()
'http://www.vbaexpress.com/forum/showthread.php?49532-Having-problem-with-autofilter-vba

Dim i As Long
Dim cll As Range
Dim uValz As String
Dim uList

With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With

With ThisWorkbook.Worksheets("TOTAL SALE")
.AutoFilterMode = False

For Each cll In .Range("D7:D" & .Cells(.Rows.Count, 4).End(xlUp).Row)
If InStr(uValz, cll.Value) = 0 Then uValz = uValz & "|" & cll.Value
Next
uList = Split(Mid(uValz, 2), "|")

For i = LBound(uList) To UBound(uList)
.Range("A6:P" & .Cells(.Rows.Count, 7).End(xlUp).Row).AutoFilter Field:=4, Criteria1:=uList(i)
With .AutoFilter.Range
If .Rows.Count > 1 Then
.Offset(1).Resize(.Rows.Count - 1).Copy Worksheets(LCase(uList(i))).Range("A7")
End If
End With
Next i

.AutoFilterMode = False
End With

With Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

End Sub

[sheikh talib]

Thank you mancubus ,you solved my problem.

[mancubus]

you are welcome.
and thanks for the feedback and marking the thread as solved.

since we're creating a unique list of values from column D, and then filtering column D for these unique elements, there is no need to test if auto filter range has visible rows other than the header row.
so you can safely delete (If .Rows.Count > 1 Then) and (End If) lines.
(it happens -ie, you may leave some unnecessary lines- sometimes when writing a new script based on an existing one.)