PDA

View Full Version : get first blank row and paste formula and format from range



noobie
01-14-2007, 08:51 PM
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.



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





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.

Jacob Hilderbrand
01-14-2007, 09:35 PM
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

noobie
01-14-2007, 09:50 PM
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.

Jacob Hilderbrand
01-14-2007, 10:03 PM
I see, so after the paste, you want to clear the constants.

Add the line to the end:


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


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.

noobie
01-14-2007, 10:15 PM
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. :)

Jacob Hilderbrand
01-14-2007, 11:24 PM
Try this and let me know if it is what you want to do.


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

noobie
01-14-2007, 11:53 PM
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/c72/everscern/AAA-1.jpg

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





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:

Jacob Hilderbrand
01-15-2007, 01:25 AM
To keep the headings we can change this line:


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


To this:

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

noobie
01-15-2007, 02:28 AM
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?




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

noobie
01-16-2007, 12:42 AM
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/c72/everscern/AAA-2.jpg



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





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

Aussiebear
01-17-2007, 04:57 AM
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

noobie
01-17-2007, 06:04 PM
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!