martha1016
New Member
- Joined
- Dec 12, 2016
- Messages
- 2
Hello VBA gurus,
I have a macro that copies some rows from one sheet to another. It works except for one thing....it looks in the active sheet for the data to copy from. I would like to put the name of the sheet ("RESUMPCP Raw") in the code.
It seems like this would be easy but I'm new with VBA. I started with some code that I found in an old post and after much effort, it almost works....almost. However, for the life of me I can't figure out the sheet name. Thanks in advance for your help.
Below is what I have. If it makes a difference I'm working in Excel 2016.
Sub CopyOurGroupers()
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Dim Rng As Range, arCriteria(), l As Long
Set Rng = Range([A1], Range("A" & Rows.Count).End(xlUp))
arCriteria = Array("80910059", "85910246", "85910247", "85910248", "85910308", "85910309", "85910332")
For l = 0 To UBound(arCriteria)
On Error Resume Next
With Worksheets("RESUMPCP Raw").Rng
.AutoFilter , field:=3, Criteria1:=arCriteria(l)
.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Sheets("Data").Range("A" & Rows.Count).End(xlUp)
'If the "Data" tab isn't formatted as a table, then append this code to previous line .Offset(1)
ActiveSheet.ShowAllData
End With
On Error GoTo 0
Next l
Application.EnableEvents = True
End Sub
I have a macro that copies some rows from one sheet to another. It works except for one thing....it looks in the active sheet for the data to copy from. I would like to put the name of the sheet ("RESUMPCP Raw") in the code.
It seems like this would be easy but I'm new with VBA. I started with some code that I found in an old post and after much effort, it almost works....almost. However, for the life of me I can't figure out the sheet name. Thanks in advance for your help.
Below is what I have. If it makes a difference I'm working in Excel 2016.
Sub CopyOurGroupers()
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Dim Rng As Range, arCriteria(), l As Long
Set Rng = Range([A1], Range("A" & Rows.Count).End(xlUp))
arCriteria = Array("80910059", "85910246", "85910247", "85910248", "85910308", "85910309", "85910332")
For l = 0 To UBound(arCriteria)
On Error Resume Next
With Worksheets("RESUMPCP Raw").Rng
.AutoFilter , field:=3, Criteria1:=arCriteria(l)
.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Sheets("Data").Range("A" & Rows.Count).End(xlUp)
'If the "Data" tab isn't formatted as a table, then append this code to previous line .Offset(1)
ActiveSheet.ShowAllData
End With
On Error GoTo 0
Next l
Application.EnableEvents = True
End Sub