VBA to call a sheet and copy userform values

Kelly05201

New Member
Joined
Jun 17, 2016
Messages
29
Hi all...
Ever get burnt out? The more code you write, the further you are from your objective? I knew you'd understand.

Here's the fast summary of this userform action:
Check to see if a workbook exists -
Code:
fileName:="C:\Users\admin\desktop\KELLY'S CRAP\cambridge\Autosaves\Event " & Format(Date, yyyymmdd) & ".xlsm"
If it exists, is it open? If not open, open it. Once open, Worksheet("Registration").Activate
Then copy some userform values to the last line.
If the workbook doesn't exists, toss up a message box and end the sub.

No biggie, right? My code is sooooo bloated and still doesn't work!

Code:
Dim wBook As Workbook
    On Error Resume Next
    Set wBook = Workbooks("Event " & Format(Date, yyyymmdd) & ".xlsm")

If FileThere("C:\Users\admin\Desktop\KELLY'S CRAP\cambridge\Autosaves\Event " & Format(Date, yyyymmdd) & ".xlsm") Then
        MsgBox ("found the magic file")
        If wBook Is Nothing Then 'Not open
            MsgBox ("Workbook exists, but is not open")
                        Set wBook = Nothing
            On Error GoTo 0
            Workbooks.Open _
                FileName:="C:\Users\admin\desktop\KELLY'S CRAP\cambridge\Autosaves\Event " & Format(Date, yyyymmdd) & ".xlsm", _
                ReadOnly:=False
        Else 'It is open
            Dim EVFNAME As String
            EVFNAME = "Event " & Format(Date, yyyymmdd) & ".xlsm"
            MsgBox (EVFNAME & " is currently open")
            
            Workbooks(EVFNAME).Activate
         
        End If
'  IT'S RIGHT ABOUT HERE WHERE THE CODE FALLS ON ITS FACE, but the rest is silly bloated too.
    Dim wst As Worksheet
    Dim iRow As Long
    Set wst = ActiveWorkbook.Sheets("Registration")
    Sheets(wst).Activate
    'find first empty row in database
    iRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    
    'copy the userform values to the event sheet that reflects today's date
    Cells(iRow, 1).Value = Me.FIRSTNAME.Value
    Cells(iRow, 2).Value = Me.LASTNAME.Value

Else
MsgBox ("There is no book created for today's event. In order to add this person to today's event, the workbook must exist FIRST.")
End If

Application.ScreenUpdating = True
Unload Me
End Sub
Thank you soooooo much in advance!
 

MUKESHY12390

Well-known Member
Joined
Sep 18, 2012
Messages
846
perhaps this....

Code:
Option Explicit


Sub tryThis()
Dim opath As String
Dim File As Variant
Dim ws As Worksheet
Dim ShtRegst As Boolean
Dim oWrk As Workbook
Dim iRow As Long


ShtRegst = False
opath = "C:\Users\e599748\Desktop\CREU - TLP - Cash Self Check Tool" & Application.PathSeparator  ''''path change Here
opath = opath & "Event" & Format(Date, yyyymmdd) '''Name Change Here
opath = opath & ".xlsm"   ''Extension change here




File = Dir(opath)


If Len(File) > 0 Then
  MsgBox (File & " Exists")
  
  Set oWrk = Workbooks.Open(opath)
  
  If InStr(1, oWrk.Name, "Read-Only", vbTextCompare) = 0 Then   ''to check ReadOnly
    
      For Each ws In oWrk.Worksheets
        If InStr(1, ws.Name, "Registration", vbTextCompare) <> 0 Then   '' To confirm Registration sheet status
            ShtRegst = True
            ws.Activate
            iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            ws.Cells(iRow, 1).Value = "ok_FirstName"  'Me.FIRSTNAME.Value
            ws.Cells(iRow, 2).Value = "ok_LastName"  'Me.LASTNAME.Value
        End If
      Next ws
  Else
    MsgBox "Workbook is Already Opened"
    Exit Sub
  End If
    If ShtRegst = False Then MsgBox "Registration worksheet not found"
Else
  MsgBox (opath & " Doesn't Exists")
End If


End Sub
 

Kelly05201

New Member
Joined
Jun 17, 2016
Messages
29
perhaps this....

Code:
Option Explicit


Sub tryThis()
Dim opath As String
Dim File As Variant
Dim ws As Worksheet
Dim ShtRegst As Boolean
Dim oWrk As Workbook
Dim iRow As Long


ShtRegst = False
opath = "C:\Users\e599748\Desktop\CREU - TLP - Cash Self Check Tool" & Application.PathSeparator  ''''path change Here
I'm clueless what a "e599748\Desktop\CREU - TLP - Cash Self Check Tool" & Application.PathSeparator" is.
Code:
opath = opath & "Event" & Format(Date, yyyymmdd) '''Name Change Here
opath = opath & ".xlsm"   ''Extension change here

File = Dir(opath)
Why the complex building of this string "opath"?
The file will always reside here: C:\Users\admin\desktop\KELLY'S CRAP\cambridge\Autosaves\
and have the filename: "Event " & Format(Date, yyyymmdd) & ".xlsm"
Either an exact match will exist or not...
Code:
If Len(File) > 0 Then
  MsgBox (File & " Exists")
  
  Set oWrk = Workbooks.Open(opath)
  
  If InStr(1, oWrk.Name, "Read-Only", vbTextCompare) = 0 Then   ''to check ReadOnly
    
      For Each ws In oWrk.Worksheets
        If InStr(1, ws.Name, "Registration", vbTextCompare) <> 0 Then   '' To confirm Registration sheet status
            ShtRegst = True
            ws.Activate
Also unnecessary... if the workbook exists, the sheet "Registration" will definitely exist.. no need to check for it.
Code:
            iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            ws.Cells(iRow, 1).Value = "ok_FirstName"  'Me.FIRSTNAME.Value
            ws.Cells(iRow, 2).Value = "ok_LastName"  'Me.LASTNAME.Value
        End If
      Next ws
  Else
    MsgBox "Workbook is Already Opened"
    Exit Sub
  End If
    If ShtRegst = False Then MsgBox "Registration worksheet not found"
Else
  MsgBox (opath & " Doesn't Exists")
End If


End Sub
I'm willing to learn the nuts and bolts of your idea though!
 
Last edited:

MUKESHY12390

Well-known Member
Joined
Sep 18, 2012
Messages
846
ShtRegst = False
opath = "C:\Users\e599748\Desktop\CREU - TLP - Cash Self Check Tool" & Application.PathSeparator ''' ''''''just this path with yours.
opath = opath & "Event" & Format(Date, yyyymmdd) '''Name Change Here
opath = opath & ".xlsm" ''Extension change here
change path with yours.

----------------------------------------------
Opath ----I love to follow "Option Explicit" with less number of variable , in big project more variable for same purpose confuse me.

Also unnecessary... if the workbook exists, the sheet "Registration" will definitely exist.. no need to check for it.
As developer , its your job to assume all possible error, which could come in future otherwise "Client/customer" have to chase you every time when error occur.As long as you making macro for yourself then it is completely OK.
 

Forum statistics

Threads
1,081,931
Messages
5,362,198
Members
400,671
Latest member
Tommy00836

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top