Copy and pasting format and formula for new range.

[noobie]

Hi all :hi: ,

I've been stuck at this for 4 days. Really need your expertise help here.
I'm almost done with this macro. But only one last thing, the values that I got from the userform, does not go to their respective row.

Please Check out my userform code:


Private Sub CommandButton1_Click()
Dim lrow As Long
Dim LastRow As Object

ActiveSheet.Unprotect

With ActiveSheet
lrow = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("A1:N10").copy .Range("A" & lrow)
On Error Resume Next
.Range("A" & lrow + 1 & ":N" & lrow + 14).SpecialCells(xlCellTypeConstants, 23).Value = ""
On Error GoTo 0
End With
Set LastRow = Range("a65536").End(xlUp) ' I want this at the first blank row after the heading
LastRow.Offset(1, 0).Value = A.Text
LastRow.Offset(1, 1).Value = B.Text
LastRow.Offset(1, 13).Value = C.Text
MsgBox "Record submitted."
response = MsgBox("Do you want to enter another record?", _
vbYesNo)
If response = vbYes Then
A.Text = ""
B.Text = ""
C.Text = ""
A.SetFocus
Else
Unload UserForm5
End If
ActiveSheet.Protect
End Sub



http://i25.photobucket.com/albums/c72/everscern/AAA-2.jpg


The values i input are found on the last row (155) but i want it placed at row (144)




You could refer to this thread. Wanted to post this there but the question i initally asked has been solved : http://www.vbaexpress.com/forum/showthread.php?t=10991

[Simon Lloyd]

Hi, you need to change the reference to your LastRow to this

LastRow = Range("A1").End(xlDown).Row
this will find the last row before the first blank then you can use the Offset(x,x) format to move from there.

Regards,
Simon

[noobie]

Thanks for your prompt reply. I was really literally begging for someone to help me. : pray2:

Anyway, tried your code and set lastrow to the reference you suggested. But there was a complie error : code mismatch. :doh:

Also, I would like to ask what does " SpecialCells(xlCellTypeConstants, 23)." mean? What does the 23 signify?

Thanks and best regards,
noobie

[lucas]

are those merged cells on the picture in post #1.....could cause a problem.

[noobie]

Hmm.. yeah. many experts told me that. do u have any suggestions if I do not merge?

[lucas]

Hi Noobie,
I think you have some kind of data or formula in the column...opening stock which is pushing your last row down...

[noobie]

Hi lucas,
thanks for your reply. Yes, indeed I've a formula in one of the columns. Pls take a look at my attached workbook to see the sheet.

Thanks! :bow:

[lucas]

Hi noobie,
quite a workbook.....
I think I would take out some of the protection and hiding until I got it working and then add that last...for instance on worksheet Housing which is the only sheet in the workbook you have this line in a Worksheet_Change event :

lrow = Sheets("Records(HS)").Range("A" & Rows.Count).End(xlUp).Row

It fails because there is no worksheet Records(HS)
Also your hiding some columns.
To be blunt I can't figure out what is supposed to be going on because there is so much going on here.
Maybe you could get it down to just the basics of what you wish to copy and paste first and then go from there. I think there is a lot of code here that could be commented out for the testing period if not altogether.

[noobie]

Hi,

Thanks for looking thru my workbook. I admit it's really complicated cause i've been using 'recording a macro' to get things going. I'll try to simplify everything and hopefully post something up. I'm waiting for your good news too. : pray2:


Thanks alot. :bow: