Condense VBA Code

[ryancgarrett]

I am a VBA novice and I'm trying to clean up some code in a program I wrote. The program is basically some rudimentary accounting software for a basic business if that helps at all.

I have a form to add a new account and when I do the following things need to happen:

The hidden "Account Template" sheet should be copied and pasted after the first sheet in the workbook.

The sheet should be renamed to the name given on the form.

The name of the account should be added in cell B2 of the new sheet.

The name of the account should be added to the "Trial Balance" sheet at the end of the list of accounts.

Here is the code that I have, which only works some of the time but not others for some reason:


Sub AddAccount()
Sheets("Account Template").Copy after:=Sheets("Journal")
Sheets(2).Select
Sheets(2).Name = frmAddAccount.lbxAccountName.Value
Sheets(2).Cells(2, 2).Value = frmAddAccount.lbxAccountName.Value
Sheets(2).Visible = True
Sheets("Trial Balance").Range("B" & BlankRow("Trial Balance", 4, 2)).Value = frmAddAccount.lbxAccountName.Value
CloseAddAccount
End Sub

Function BlankRow(sName As String, sRow As Integer, sCol As Integer) As Integer
Do Until Sheets(sName).Cells(sRow, sCol).Value = Empty
sRow = sRow + 1
Loop
BlankRow = sRow
End Function

Can this be cleaned up and does anyone know why it only works some of the time? As well, when other people post I notice they format their code differently on here, how is that done? Sorry I'm such a noob!

 

[Hermanito]

You can put code in your post by selecting it in the editor and clicking the # sign (only shown in the advanced edit). This adds code tags.

Sub AddAccount()
Sheets("Account Template").Copy after:=Sheets("Journal")
'Sheets(2).Select   'This line is not necessary
Sheets(2).Name = frmAddAccount.lbxAccountName.Value
Sheets(2).Cells(2, 2).Value = frmAddAccount.lbxAccountName.Value
Sheets(2).Visible = xlSheetVisible   'Visible is not a boolean, it can have 3 values
With Sheets("Trial Balance")
   .Cells(.Cells(.Rows.Count,2).End(xlup).Row+1,2).Value = frmAddAccount.lbxAccountName.Value
End With
CloseAddAccount
End Sub

No need to create that separate function to find the first empty row, the .End function does that a lot faster...

Not sure why your code only works sometimes, I don't see any obvious errors (but I'm tired, so I might be missing them altogether )