Hello all,
This is a question in reference to a macro written by Erik Van Geit here: https://www.mrexcel.com/forum/excel...t-based-criteria-post3392362.html#post3392362
When I run the macro the header is copied over to it's appropriate sheet, but the data matching does not with the exception of the first array. In my worksheet, Col B has names matching the 4 sheets listed in the macro. I need the entire row that matches the name of the sheet to move to that sheet for all the arrays. I'd greatly appreciate some editing please. Please keep in mind I am a novice when it comes to VBA, I copy and paste and then try to understand each component haha.
I'm using Excel 2010 on a Windows 10 system btw
This is a question in reference to a macro written by Erik Van Geit here: https://www.mrexcel.com/forum/excel...t-based-criteria-post3392362.html#post3392362
When I run the macro the header is copied over to it's appropriate sheet, but the data matching does not with the exception of the first array. In my worksheet, Col B has names matching the 4 sheets listed in the macro. I need the entire row that matches the name of the sheet to move to that sheet for all the arrays. I'd greatly appreciate some editing please. Please keep in mind I am a novice when it comes to VBA, I copy and paste and then try to understand each component haha.
I'm using Excel 2010 on a Windows 10 system btw
Code:
Sub STEP5_Filter_To_Separate_Data_to_Sheets()
'Erik Van Geit
'1302013 2118
'lacks error handling when sheet doesn't exist
'clearing sheets and pasting with columnheaders
'https://www.mrexcel.com/forum/excel-questions/685493-vba-move-rows-another-sheet-based-criteria.html
Dim SourceSheet As Worksheet
Dim TargetSheet As Worksheet
Dim SheetNames As Variant
Dim i As Long
Dim LR As Long
'EDIT
Set SourceSheet = Sheets("Sheet1")
SheetNames = Array("STN", "CO", "BN", "BDE")
Const FilterColumn = 2 'I don't understand what this represents, I assumed column number of where to look for the array
'END EDIT
With SourceSheet
LR = .Range("B" & .Rows.Count).End(xlUp).Row
For i = 0 To UBound(SheetNames)
Set TargetSheet = Worksheets(SheetNames(i))
TargetSheet.Cells.ClearContents
With .Range("A1:W" & LR)
.AutoFilter Field:=FilterColumn, Criteria1:=SheetNames(i)
.Offset(0, 0).Copy TargetSheet.Range("A1")
End With
Next i
End With
End Sub
Last edited: