get first blank row and paste formula and format from range

Hi all,
I need a macro which
1) find the first blank row and then
2) copies and paste the formula and format only from range A1:H10

This macro will only occur within the same sheet. As such, I've actually constructed a macro using my limited knowledge. Hope you guys could kindly help me modify from here.

[vba]

Sub copy_it()
Dim lrow As Long
Dim lrow2 As Long
lrow = Sheets(1).Range("A1:H10").End(xlUp).Row
lrow2 = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
lrow2 = lrow2 + 1
Sheets(1).Range("A" & lrow & ":H" & lrow).copy Sheets(1).Range("A" & lrow2)
Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End Sub


[/vba]


sorry as I'm really bad at this. So I usually depend on recording macros to get my ideas through.

Thanks in advance for the reply.

I am not clear on what you want to do that this code does not do. It looks like you are already determining the last row and then copying and pasting.

Can you clarrify what the problem is a bit more?

Thanks

I can only figure out the last row macro. But I could not paste special the formulas and format only without any values. I think I'm pretty bad at explaining so pls see the attached workbook.

I see, so after the paste, you want to clear the constants.

Add the line to the end:

[vba]
On Error Resume Next
Sheets(1).Range("A" & lrow2 & ":H" & lrow2).SpecialCells(xlCellTypeConstants, 23).Value = ""
On Error Goto 0
[/vba]

And get rid of the paste special line. Just do the normal paste, then clear the constants. This will get you the formulas and formats.

Code:

---

Sub copy_it()
Dim lrow2 As Long
lrow2 = Sheets("INVENTORY").Range("A" & Rows.Count).End(xlUp).Row
lrow2 = lrow2 + 1
    Selection.Copy
    Range("A85:N90").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A91").Select
    Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("A91:N91").Select
    Application.CutCopyMode = False
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
 
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Rows("91:91").RowHeight = 28.5
On Error Resume Next
Sheets(1).Range("A" & lrow2 & ":H" & lrow2).SpecialCells(xlCellTypeConstants, 23).Value = ""
On Error GoTo 0
End Sub

---

Is it something like this? Cause I tried it but it does not paste any formula or format. There should be at least the heading and the formulas available.

Pls advise. Thanks. :)

Try this and let me know if it is what you want to do.

[vba]
Option Explicit

Sub copy_it()

Dim lrow As Long

With Sheets(1)
lrow = .Range("H" & Rows.Count).End(xlUp).Row + 1
.Range("A1:N15").Copy .Range("A" & lrow)
On Error Resume Next
.Range("A" & lrow & ":N" & lrow + 14).SpecialCells(xlCellTypeConstants, 23).Value = ""
On Error GoTo 0
End With

End Sub
[/vba]

Yup, that's what I wanted. Thanks!! :rofl:
Only one thing, can i keep the headings? And I want a userform to input some values into their respective cells.

http://i25.photobucket.com/albums/c7...cern/AAA-1.jpg

These values are then placed under their respective headings in the first row right after the headings.

Code:

---


Sub copy_it()
 
    Dim lrow            As Long
    Dim LastRow As Object
    Set LastRow = Sheet("INVENTORY").Range("a65536").End(xlUp)
 
    Load UserForm5
 
    LastRow.Offset(1, 0).Value = TextBox1.Text
    LastRow.Offset(1, 1).Value = TextBox2.Text
    LastRow.Offset(1, 2).Value = TextBox3.Text
    MsgBox "One record written to Sheet1"
    response = MsgBox("Do you want to enter another record?", _
    vbYesNo)
    If response = vbYes Then
        TextBox1.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox1.SetFocus
    Else
        Unload Me
    End If
    With Sheets("INVENTORY")
        lrow = .Range("H" & Rows.Count).End(xlUp).Row + 1
        .Range("A1:N15").Copy .Range("A" & lrow)
        On Error Resume Next
        .Range("A" & lrow & ":N" & lrow + 14).SpecialCells(xlCellTypeConstants, 23).Value = ""
        On Error GoTo 0
    End With
 
End Sub

---

Something like the above. Sorry for being too messy. But i hope it's clear enough. :)

Thanks!! :beerchug:

To keep the headings we can change this line:
[VBA]

.Range("A" & lrow & ":N" & lrow + 14).SpecialCells(xlCellTypeConstants, 23).Value = ""
[/VBA]

To this:

[VBA].Range("A" & lrow + 1 & ":N" & lrow + 14).SpecialCells(xlCellTypeConstants, 23).Value = ""[/VBA]

Thanks. That was what I needed. :clap:

I managed to come up with a code very similar to what I wanted. But.. The values input from the text boxes were pasted on the last row. How do I paste it on the first row instead right after the heading?


[VBA]

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

With Sheets("HOUSING")
lrow = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("A18:N29").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 = Worksheets("Housing").Range("a1").End(xlUp)
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
End Sub



[/VBA]

This is what it looks like now. the values inputted from the userform are sent to the last row instead ( row 155) of the row just below the heading ( row 144). How do i input the values to be at row 144?



http://i25.photobucket.com/albums/c7...cern/AAA-2.jpg


[VBA]
Private Sub CommandButton1_Click()
Dim lrow As Long
Dim LastRow As Object

With Sheets("HOUSING")
lrow = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("A18:N29").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 = Worksheets("Housing").Range("a65536").End(xlUp)
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
End Sub



[/VBA]

Attached is my code. Pls look through. Thanks Alot! :bow:

Hi Noobie, Using your graphic as a reference, are you looking to insert the new data in row 144 and push any existing data down so that all new data entered is just below the titles?

Ted

hi Ted,
Many thanks for your reply. :) It was my fault for not have mark this thread solved. My primary question was answered so I moved my question to another thread. You may find the continuation here : http://vbaexpress.com/forum/showthread.php?t=11039


Now, my answer to your question. Not exactly. My main objective is to paste the new formatting in line 144. Thereafter, paste the values I've got from the userform to line 144.

In short, mixer 3503 will be in cell A144, 20 will be in cell B144, and 20.035m will be in cell m144. The whole range will be paste from 144 to 155.

Hope this is clear enough. thanks so much for replying!