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
Bookmarks