PDA

View Full Version : [SOLVED] VBA copy from a variable sheet



Jonahsav
08-18-2018, 03:56 PM
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 B2:D10) come back to the sheet checkprofiles and paste it on B2:D10.

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
08-18-2018, 05:54 PM
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

22735

22736

Jonahsav
08-19-2018, 04:49 AM
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
08-19-2018, 06:27 AM
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
08-19-2018, 07:31 AM
wow i have no idea how that works, but thank you so much!

really appreciate it!

:bow:

Paul_Hossler
08-19-2018, 10:18 AM
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
08-20-2018, 05:58 AM
aha!

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

Jonahsav
09-11-2018, 04:37 PM
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
09-11-2018, 05:35 PM
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