Results 1 to 2 of 2

referencing a defined worksheet variable in a formula in vba

Threaded View

  1. #1
    Registered User
    Join Date
    10-19-2012
    Location
    Fairbanks, AK
    MS-Off Ver
    Excel 2007
    Posts
    1

    referencing a defined worksheet variable in a formula in vba

    Hello All,

    I'm relatively new to VBA and have enountered a problem that I have not been able to solve on my own. I have created a userform that once the user clicks the okay button, the information is transferred to a worksheet template named 'PO Template', copied, and renamed. That portion works great. I then have code for a summary worksheet named 'PO Log' that finds the next empty row on the log and enters a formula referencing back to the newly created 'PO Form' (note - 'PO Form' is a variable as the actual worksheet name changes with the user input). The formula on the 'PO Log' references the 'PO Form' literally, which gives me a REF! error because there is no sheet actually named that. I've tried defining POForm in the code as a string so I can enter it into the formula code but I get an error because it's already defined as a variable. I hope this makes sense... any suggestions would be appreciated. I've entered the sub routine for clicking the okay button below:

    Sub OkayButton_Click()
    
    'Make PO Template Active
    Sheets("PO Template").Activate
    
    'Copy "PO Template"
    Sheets("PO Template").Copy After:=Worksheets(Worksheets.Count)
    
    Dim POForm As Worksheet
    Set POForm = ActiveSheet
    
    With POForm 'Export Data to PO Form (Copy of Template)
        .Name = PCOTabBox.Value
        Range("F5").Value = PONumberBox.Value
        Range("H5").Value = DateBox.Value
        Range("A13").Value = VendorSubcontractList.Value
        Range("L5").Value = PORequesterBox.Value
        Range("A9").Value = PODescriptionBox.Value
        Range("L13").Value = ManagementApprovalList.Value
        Range("J19").Value = EstimatedCostBox.Value
        Range("N19").Value = JobCodeList.Value
        Range("P19").Value = CostTypeList.Value
        End With
    
    Application.ScreenUpdating = False  'speed up macro execution
    Application.DisplayAlerts = False   'no alerts, default answers used
    
    Sheets("PO Log").Activate 'activate PO Log
    
    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1 'determine empty row
    
    'Transfer data from newly created PO Form to PO Log
    Cells(emptyRow, 1).Value = PCOTabBox.Value
    Cells(emptyRow, 2).Formula = "='POForm'!F5"
    Cells(emptyRow, 3).Formula = "='POForm'!H5"
    Cells(emptyRow, 6).Value = POStatusBox.Value
    Cells(emptyRow, 4).Formula = "='POForm'!A5"
    Cells(emptyRow, 7).Formula = "='POForm'!J19"
    Cells(emptyRow, 5).Formula = "='POForm'!A9"
    Cells(emptyRow, 11).Formula = "='POForm'!N19"
    Cells(emptyRow, 12).Formula = "='POForm'!P19"
    
    Application.ScreenUpdating = True
    
    Unload Me
    
    End Sub
    Last edited by FDibbins; 10-19-2012 at 08:14 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1