VBA copy from a variable sheet

[Paul_Hossler]

OK then -- this is a sheet event handler that would go on 'CheckProfiles' code module

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim n As Long
    Dim sProfileName As String
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    sProfileName = Range("A1").Value
    n = 0
    On Error Resume Next
    n = Worksheets(sProfileName).Index
    On Error GoTo 0
    
    If n = 0 Then
        Call MsgBox("Worksheet " & sProfileName & " not in workbook", vbExclamation + vbOKOnly, "Profile Finder")
        Exit Sub
    End If
    
    Application.EnableEvents = False
    Worksheets(sProfileName).Range("B2:B10").Copy Me.Range("B2:B10")
    Application.EnableEvents = True
End Sub

I also added ShowAll and HideAll macros to a standard module


The example workbook just has 'Sheet1' to 'Sheet10' in it