Hi,
I have received the attached macro that does exactly what I asked for, but it would actually help if somebody could make a minor change in it.
The following macro copy pastes the data into different sheets. That means: in my sheet called Data, the macro copies the data in the whole row if the word xxx is written in column C and pastes it into the sheet with the same name.
Would it be possible to copy paste the data instead of the sheet into a new Excel file? This would mean, that the data of xxx would instead of in a sheet be placed into a new Excel file with the same name.
Thank you very much for your help.
Sub ExtractData()
Dim lr As Long
Dim i As Long
mysheet = Array("www", "xxx", "yyy")
lr = Sheets("Data").Range("C" & Rows.Count).End(xlUp).Row
Application.EnableEvents = False
Application.ScreenUpdating = False
For i = 0 To UBound(mysheet)
Sheets(mysheet(i)).UsedRange.ClearContents
With Sheets("Data").Range("A1:L" & lr)
.AutoFilter Field:=3, Criteria1:=mysheet(i)
.Copy Destination:=Sheets(mysheet(i)).Range("A1")
.AutoFilter
End With
Next i
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
I have received the attached macro that does exactly what I asked for, but it would actually help if somebody could make a minor change in it.
The following macro copy pastes the data into different sheets. That means: in my sheet called Data, the macro copies the data in the whole row if the word xxx is written in column C and pastes it into the sheet with the same name.
Would it be possible to copy paste the data instead of the sheet into a new Excel file? This would mean, that the data of xxx would instead of in a sheet be placed into a new Excel file with the same name.
Thank you very much for your help.
Sub ExtractData()
Dim lr As Long
Dim i As Long
mysheet = Array("www", "xxx", "yyy")
lr = Sheets("Data").Range("C" & Rows.Count).End(xlUp).Row
Application.EnableEvents = False
Application.ScreenUpdating = False
For i = 0 To UBound(mysheet)
Sheets(mysheet(i)).UsedRange.ClearContents
With Sheets("Data").Range("A1:L" & lr)
.AutoFilter Field:=3, Criteria1:=mysheet(i)
.Copy Destination:=Sheets(mysheet(i)).Range("A1")
.AutoFilter
End With
Next i
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub