VB Copy/Paste Loop Across All Worksheet

[EXWIP]

Hello! I'm a newish user having some trouble with bit of code. I need to copy and paste several selections from one part of a sheet to another part of the same sheet and loop that code across 100 other sheets. This loop needs to exclude a list of sheets: NonPropSheets. NonPropSheets is a named array: Dashboard!$W$13:$W$103. W13:W24 are tab names that need to be excluded from the loop. W25:W103 are blank in case anyone needs to add more sheets to this list.

Here's what I have so far:

Sub RolloverLastYear()
' Defines variable
Dim ws As Worksheet


' Defines variable nSheets as the sheets you want to ignore
nSheets = Array("NonPropSheets")


' For each sheet in the active workbook
For Each ws In ActiveWorkbook.Worksheets
    ' If the sheet name is not in the list nSheets then...
    If Not IsNumeric(Application.Match(ws.Name, nSheets, 0)) Then
        ' Copy/Paste Code
    Range("B105").Select
    Selection.Copy
    Range("B104").Select
    ActiveSheet.Paste
   
    Range("AH110:AS133").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("S110").Select
    ActiveSheet.Paste
   
    Range("AH135:AS232").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("S135").Select
    ActiveSheet.Paste
   
    Range("O262:Z263").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("C263").Select
    ActiveSheet.Paste
    End If
' Check next sheet
Next ws

' Turn off CutCopyMode
Application.CutCopyMode = xlCopy

End Sub

Credit: Original Formula is from this thread: Pasting into multiple worksheets VBA

Any help is appreciated.

 

[Trebor76]

Hi EXWIP,

Welcome to MrExcel!!

Let us know how this goes:

Option Explicit
Sub RolloverLastYear()

    Dim strExcludeSheets() As String
    Dim lngArrayIndex As Long
    Dim rngMyCell As Range
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    
    'Build an array of sheet(s) to be excluded
    For Each rngMyCell In ActiveWorkbook.Names("NonPropSheets").RefersToRange
        If Len(rngMyCell) > 0 Then
            ReDim Preserve strExcludeSheets(lngArrayIndex)
            strExcludeSheets(lngArrayIndex) = rngMyCell.Value
            lngArrayIndex = lngArrayIndex + 1
        End If
    Next rngMyCell
    
    For Each ws In ActiveWorkbook.Sheets
        'If the sheet name is not in the 'strExcludeSheets' array, then...
        If Not IsNumeric(Application.Match(ws.Name, strExcludeSheets, 0)) Then
            '...copy and paste as follows.
            ws.Range("B105").Copy Destination:=ws.Range("B104")
            ws.Range("AH110:AS133").Copy Destination:=ws.Range("S110")
            ws.Range("AH135:AS232").Copy Destination:=ws.Range("S135")
            ws.Range("O262:Z263").Copy Destination:=ws.Range("C263")
        End If
    Next ws
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[EXWIP]

Hi EXWIP,

Welcome to MrExcel!!

Let us know how this goes:

Option Explicit
Sub RolloverLastYear()

    Dim strExcludeSheets() As String
    Dim lngArrayIndex As Long
    Dim rngMyCell As Range
    Dim ws As Worksheet
   
    Application.ScreenUpdating = False
   
    'Build an array of sheet(s) to be excluded
    For Each rngMyCell In ActiveWorkbook.Names("NonPropSheets").RefersToRange
        If Len(rngMyCell) > 0 Then
            ReDim Preserve strExcludeSheets(lngArrayIndex)
            strExcludeSheets(lngArrayIndex) = rngMyCell.Value
            lngArrayIndex = lngArrayIndex + 1
        End If
    Next rngMyCell
   
    For Each ws In ActiveWorkbook.Sheets
        'If the sheet name is not in the 'strExcludeSheets' array, then...
        If Not IsNumeric(Application.Match(ws.Name, strExcludeSheets, 0)) Then
            '...copy and paste as follows.
            ws.Range("B105").Copy Destination:=ws.Range("B104")
            ws.Range("AH110:AS133").Copy Destination:=ws.Range("S110")
            ws.Range("AH135:AS232").Copy Destination:=ws.Range("S135")
            ws.Range("O262:Z263").Copy Destination:=ws.Range("C263")
        End If
    Next ws
   
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

Thank you Robert! This worked perfectly.

 

[Trebor76]

Thanks for letting us know and you're welcome