VBA code help

[martha1016]

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

 

[Kenneth Hobson]

Welcome to the forum! Paste code between code tags to keep structure. Click # icon on toolbar or type the tags.

Parts of it...

  With Worksheets("RESUMPCP Raw")
    Set Rng = Range(.[A1], .Cells(Rows.Count, "A").End(xlUp))
    '....
    With Rng
      .AutoFilter , field:=3, Criteria1:=arCriteria(l)
      '...
    End With
  End With
  '...

 

[martha1016]

Sorry but what are the tags? I'm new working with VBA.

 

[Kenneth Hobson]

Tags are what you don't see in html unless you open it in a text file browser and not a web browser. That is what the forum uses, well sort of. Just click the # icon and you will see (code)(/code) but with []'s rather than ()'s around tags. The help shows them but you just need the code tags for well, codes. vBulletin.org Forum - BB Code List

Quote tags can be used along with others on the toolbar. Quotes should for parts of a post if really needed rather than all of it. Normally one might say, in response to #2 (2nd post in a thread), or just say, hey Ken, you lost me. Even more tags and other features are in the Go Advanced button in lower right of a reply. Most all forums work this way.