Hello!
First off, I am totally new to VBA and only have very rudimentary understanding of scripting.
I tried a slightly altered version (for details see below) of the script in post #4 by Peter_SSs above and encountered a problem:
My script is only copying the second (I added an offset) row of my file, not all (51093). I use Excel 2010. I saved the file as an excel2003-file (xls) but I also tried saving it as an xlsx. Neither worked.
I posted my script at the end of this post with the changes marked red and also made a list containing comments specifying the reason for each change.
To be honest I also don't know what "mysheet" is doing and would be glad if someone could also explain it to me. If it is a variable why don't I have to define it first like the "lr" or "i" variables? But first priority is for the script to work
Any help would be greatly appreciated!
Vorador
Changes to the original script:
- Array("1", "2", "3"): changed the values to search for and the names of the (already existing) sheets to copy the rows in
- lr = Sheets("main").Range("F" & Rows.Count).End(xlUp).Row: adapted the name of the original sheet and changed the column there the conditions of interest are located
- Sheets(mysheet(i)).UsedRange.Offset(1).ClearContents: first row contains the header so I added the offset-function
- With Sheets("main").Range("A1:AZ" & lr): adapted the name of the original sheet and the .Range for my file
- .AutoFilter Field:=5, Criteria1:=mysheet(i): changed the Field because the values of interest are in the column "F"
Script:
Sub ExtractData()
Dim lr As Long
Dim i As Long
mysheet = Array("1", "2", "3")
lr = Sheets("main").Range("F" & Rows.Count).End(xlUp).Row
Application.EnableEvents = False
Application.ScreenUpdating = False
For i = 0 To UBound(mysheet)
Sheets(mysheet(i)).UsedRange.Offset(1).ClearContents
With Sheets("main").Range("A1:AZ" & lr)
.AutoFilter Field:=5, Criteria1:=mysheet(i)
.Copy Destination:=Sheets(mysheet(i)).Range("A1")
.AutoFilter
End With
Next i
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub