Multiple worksheet options

I have a macro that is current set to look for a specific worksheet, Sheets("Prog Off Core"). I was just told that there could be a few variations of Prog Off.

Prog Off Core Print
Prog Off Resource Print
Prog Off Core Digital
Prog Off Resource Digital

Thank you.

Sub SAP_40_Character_Copy()

Application.ScreenUpdating = False

Dim Drng As Long
Dim Counter As Integer

Drng = Sheets("Prog Off Core").Cells(Rows.Count, "BC").End(xlUp).Row


MsgBox "SAP Description about to populate - Rows 3-" & Drng

'Copy Abbreviation - first row
    Sheets("Abbreviations").Select
    Range("A3:U3").Select
    Application.CutCopyMode = False
    Selection.Copy
    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
'Paste Abbreviation formulas
    'Sheets("Abbreviations").Select
    Range("A3:U" & Drng).Select
    ActiveSheet.Paste
       
   
'Copy SAP Desc
    'Sheets("Abbreviations").Select
    Range("A3:A" & Drng).Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Sheets("Prog Off Core").Select
    Range("C3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
        
Counter = "3"

'Do While Loop ()
    Do While Counter <= Drng
    
    'Language Update
    Sheets("Prog Off Core").Select
    Range("J" & Counter).Select
        ActiveCell.FormulaR1C1 = _
            "=IF(VLOOKUP('Prog Off Core'!RC[11],Options_Fields!C10:C12,3,0)<>"""",VLOOKUP('Prog Off Core'!RC[11],Options_Fields!C10:C12,3,0), IF(OR(Abbreviations!RC[6]="""", Abbreviations!RC[6]=""ENG""),""English"",VLOOKUP(Abbreviations!RC[6],Options_Fields!R2C64:R7C65,2,0)))"
    
    
    Counter = Counter + "1"
    Loop  
    
End Sub

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Hi Pablo - Assuming there's only ONE "Prog Off " sheet in the active workbook, give this a try:
Code Updated 12:31 pm

Sub SAP_40_Character_Copy()

Application.ScreenUpdating = False

Dim Drng As Long
Dim Counter As Integer
Dim ws As Worksheet
Dim POCname As String

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "Prog Off *" Then
        POCname = ws.Name
        Exit For
    End If
Next

Drng = Sheets(POCname).Cells(Rows.Count, "BC").End(xlUp).Row

MsgBox "SAP Description about to populate - Rows 3-" & Drng

With Sheets("Abbreviations")
'Copy Abbreviation - first row
.Range("A3:U3").Copy
.Range("A3:U" & Drng).PasteSpecial xlPasteAll
'Copy SAP Desc
.Range("A3:A" & Drng).Copy
End With

With Sheets(POCname)
.Range("C3").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Counter = "3"

'Do While Loop ()
Do While Counter <= Drng

'Language Update
.Range("J" & Counter).FormulaR1C1 = _
"=IF(VLOOKUP('" & POCname & "'!RC[11],Options_Fields!C10:C12,3,0)<>"""",VLOOKUP('" & POCname & "'!RC[11],Options_Fields!C10:C12,3,0), IF(OR(Abbreviations!RC[6]="""", Abbreviations!RC[6]=""ENG""),""English"",VLOOKUP(Abbreviations!RC[6],Options_Fields!R2C64:R7C65,2,0)))"

Counter = Counter + "1"
Loop

End With

End Sub

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee