VBA copy from a variable sheet

[Jonahsav]

hey guys, im new to vba and i can only really record or copy vba haha has been going fine till now.

my excel has alot of sheets, lets call them userprofiles (userprof1, userprof2, userprof3 etc as sheetnames) when i have a new user i fill in a format and a new sheet automatticly creates itself and hides (this because i have over 100 users and their own sheet but its annoying to have 100 sheets "open")

so now the problem.

sometimes i need to check the user profile, but its really annoying to search for userprof73 unhide it check it and then hide it again so i tought why not make a macro for this.

the idea:

i have a sheet called checkprofiles.
in this sheet i put on A1 what profile i want to check (in this case sheet userprof73)
then i want the macro to go to the sheet that has been typed in A1, copy a certain region (lets say B210) come back to the sheet checkprofiles and paste it on B210.

the only varible is the sheet, ive tried it with indirect but cant get it to work, might be really easy but then again im a noob.


please help me out, would mean alot ive been struggeling with this for like 8 hours now..


thanks in advance

[Paul_Hossler]

What might be easier is to use the built in sheet 'opener'

In the lower left corner to the left of the sheet tabs (sort of where the X is), right click to open a list and then just pick one

1.JPG

2.JPG

[Jonahsav]

thanks for the reply, problem is all my users have a sheet, so i have a lot of sheets, so i want to hide all the user sheets. and if a sheet is hidden it cant be activated, have to unhide it first and then search it. its not the biggest problem but i like to do stuff i cant to so i learn stuff from it. and i tought making a macro was duable.
but i cant find it anywere so yeahh haha

[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

[Jonahsav]

wow i have no idea how that works, but thank you so much!

really appreciate it!

[Paul_Hossler]

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

Fairly straight forward …

Worksheeet_Change is an event handler that gets called each time the worksheet has a cell(s) changed and passed the changed range

Intersect () looks to see if A1 as the cell that changed (= Nothing is the intersection of Target and A1 is empty)

The n =0 part is just the way I check to see it the profile worksheet exists (n <> 0 means it exists)

It's important to not process any more events since otherwise the event handler keeps calling itself

The .Copy copies the cells, not necessary to activate the sheet first

[Jonahsav]

aha!

sorry like I said im really new to via, thanks for helping me out

[Jonahsav]

hey, is it possible to change this macro to a click on a button, so when i click on a button it copys the needed cells, and not update anytime i change the cell?

thanks alot

[Paul_Hossler]

Delete the WS event and put this in the standard module

Option Explicit

Sub CopyFromA1()
    Dim n As Long
    Dim sProfileName As String
    
    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
    
    Worksheets(sProfileName).Range("B2:B10").Copy ActiveSheet.Range("B2:B10")
End Sub


Sub HideAll()
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "CheckProfiles" Then ws.Visible = xlSheetVeryHidden
    Next
End Sub


Sub ShowAll()
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next
End Sub