Copy to another sheet-a UserForm to enter information

First of all, everything I have done in this excel project I have found the answer somewhere on the net. I am a complete novice at this, so please be kind and sympathetic to a beginner.

He goes, I hope I can explain it properly:


I am using a UserForm to enter information in to sheet TicketData (Columns A:CP). I have another sheet with all the formulas on called Workings (Columns A:AR ). I have put simple formulas into row 1 to calculate the info, e.g. =ticketdata!A1*ticketdata!Z1 etc.

When I enter the info into the UserForm it puts all the info in to the next free line in sheet TicketData, and all the calculation are done straight away in the Workings sheet in row 1.

What I need to do now is; when I enter another lot of info into the userform it will enter the information into the next empty row (row 2) in Ticket Data. Now I only have the formulas in row 1 of Workings sheet. I need a way of automatically coping the formulas to row 2, 3 , 4 etc as new info is added. This spreadsheet could have up to 10, 000 rows of info in 1 year.

If anyone can understand this and come back with an answer, I will be truly greatfull.

Cheers Binkx2001

Hi,

Why not keep a copy of your formula tucked away in a named range, say "MyFormula" somewhere above your data, then in your userform include some code to copy and paste it to the appropriate place. e.g.

Range("MyFormula").Copy Destination:= Sheet1.Range("E" & Rows.Count).End(xlUp).Offset(1,0)

If you're handling several additional rows at a time, to avoid jumping backwards and forwards between the VBE and the Excel Application each time you enter new info, which can slow things down, consider only copying the formula in one block to a range of rows at the end of your process of adding new info.

HTH