Userform VBA questions

[MICHBROWNAOL]

I'm having a problem with my code for a userform, although the code problem isn't the user form itself, it's copying formats and formulas to the row where the userform data is placed in Excel.

What I am trying to do is rather simple - a user enters a series of data on the user form (either numbers or selections from listboxes, and clicks OK. The user form data is then placed in the appropriate cells on the next blank row of the spreadsheet. This part works fine.

The challenge that I'm having is that I then need to apply formating and insert formulas to the new row of data. I currently have a range named "FuncFormat" which contains the formulas and properly formated cells. I need to figure out how to copy this to the new range.

The other challenge I'm having is that I need my user to be able to do a sort of the entries to put them in date order. I've created a range and a macro to do this, however I need the range to continue to expand as the user adds new entries. What's the easiest way to accomplish this?

Here's my code so far:

Private Sub BookButton_Click()
Sheets("Functions").Activate
nextrow = Application.WorksheetFunction.CountA(Range("A:A"))
ActiveCell.Select
Range("funcformat").Copy Range("NEXTROW")
Cells(nextrow, 1) = FName.Text
Cells(nextrow, 2) = Fdate.Value
Cells(nextrow, 4) = Ftime.Value
Cells(nextrow, 5) = FGTD.Value
Cells(nextrow, 6) = FMealT.Text
Cells(nextrow, 7) = FBarT.Text
Cells(nextrow, 8) = FRoom.Text
Cells(nextrow, 9) = FWaitStaff.Value
Selection.Insert shift = xlDown

Call bookrevenue
End Sub


Private Sub bookrevenue()
Sheets("Revenue").Activate
nextrow = Application.WorksheetFunction.CountA(Range("A:A"))
Cells(nextrow, 1) = FName.Text
Cells(nextrow, 2) = Fdate.Value
Cells(nextrow, 3) = FGTD.Value
Cells(nextrow, 4) = FMealT.Text
Cells(nextrow, 5) = FFRev.Value
Cells(nextrow, 6) = FBRev.Value
Cells(nextrow, 7) = FRmFee.Value
Sheets("Functions").Activate
Unload Me
End Sub

 

[MICHBROWNAOL]

I'm sure this is possible... just need to figure out the right sequences for the VBA?

 

[parry]

Hi, I presume your having trouble with this line ...
Range("funcformat").Copy Range("NEXTROW")

Private Sub BookButton_Click()
Dim NextRow As Long

'You dont actually need to activate the sheet to accomplish this
'but it will require some small changes so Ill leave as is so not to
'confuse you.
Sheets("Functions").Activate

'NextRow = Application.WorksheetFunction.CountA(Range("A:A"))
'CountA may not work well if theres gaps in the data. Try this instead...
NextRow = Range("A65536").End(xlUp).Offset(1, 0).Row
'ActiveCell.Select   'You dont need this

Range("funcformat").Copy 'Is the range funcformat 1 row x 9 columns?
Range(Cells(NextRow, 1), Cells(NextRow, 9)).PasteSpecial xlPasteFormats
Cells(NextRow, 1) = FName.Text
Cells(NextRow, 2) = Fdate.Value
Cells(NextRow, 4) = Ftime.Value
Cells(NextRow, 5) = FGTD.Value
Cells(NextRow, 6) = FMealT.Text
Cells(NextRow, 7) = FBarT.Text
Cells(NextRow, 8) = FRoom.Text
Cells(NextRow, 9) = FWaitStaff.Value
'Selection.Insert shift = xlDown  'You dont need this

Call bookrevenue
End Sub