copy data on all sheets except specified sheets

[howard]

I have a macro to copy data from row B3 to the last row containing data in C3 on all sheets except specified sheets and all formulas to remain intact-see specified sheets not to be affected in code for eg Main Menu, YTD-Dashboard etc

Row B3 onwards in being copied on some of the specified sheets and paste valued into Col C


It would be appreciated if someone could kindly amend my formula

 Sub Copy_Data()
Dim lr As Long, ws As Worksheet
For Each ws In Worksheets
    If ws.Name <> "Main Menu" And ws.Name <> "YTD" And ws.Name <> "YTD-Dashboard" And ws.Name <> "Expenses" And _
        ws.Name <> "YTD-Including Units" And ws.Name <> "Months" And ws.Name <> "Monthly Data detail" _
        And ws.Name <> "Scroll Bar By Month" And ws.Name <> "YTD Filtered Data" Then
        lr = ws.Cells(Rows.Count, "B").End(xlUp).Row
        ws.Range("B3:B" & lr).Copy
        ws.Range("C3").PasteSpecial xlPasteValues
    End If
Next ws
Application.CutCopyMode = False
End Sub

 

[Trebor76]

Hi Howard,

See how this goes:

Option Explicit
Sub Macro2()

    Dim varMyArray As Variant
    Dim wsMySheet  As Worksheet
    Dim lngLastRow As Long
    
    varMyArray = Array("Main Menu", "YTD", "YTD-Dashboard", "Expenses", "YTD-Including Units", "Months", "Monthly Data detail", "Scroll Bar By Month", "YTD Filtered Data")
    
    Application.ScreenUpdating = False
    
    For Each wsMySheet In ThisWorkbook.Sheets
        'If the sheet name is not the in 'varMyArray' array, then...
        If IsNumeric(Application.Match(wsMySheet.Name, varMyArray, 0)) = False Then
            With wsMySheet
                lngLastRow = .Cells(Rows.Count, "B").End(xlUp).Row
                '...copy all the formulas from cell B3 to C3 as values
                .Range("B3:B" & lngLastRow).Copy
                .Range("C3").PasteSpecial xlPasteValues
            End With
        End If
    Next wsMySheet
    
    Application.ScreenUpdating = True
    
End Sub

Regards,

Robert

 

[howard]

Thanks for the help Robert. Have teste the code and it works perfectly

 

[Trebor76]

Thanks for the feedback (and the Like). You're welcome