ExcelBanter - Find last used row and paste from above

Option Explicit
Option Private Module 'hides macros from view

Sub AddDataBlockSAS() 'SalesAidSoftware
Dim lrow As Long

With Sheets("COC")
lrow = .Range("Print_Area").Rows.Count
'MsgBox Lrow
.Rows("14:23").Copy .Cells(lrow + 1, 1)
.PageSetup.PrintArea = "$A$1:$L$" & lrow + 10
.Rows(lrow + 1).Resize(10).ClearContents
End With

End Sub
Sub ClearFormSAS() 'SalesAidSoftware
Dim lrow As Long
Application.ScreenUpdating = False

With Sheets("COC")
..Range("B4,B5:H5,B6:E6,E4:H4,G6:K6,J4:L4,J5:L5"). ClearContents
..Range("C11:E12,G11:H12,K11:L12").ClearContents
..Range("a9:a10,a14:a23").EntireRow.ClearContents
..Range("b4").Select

'Delete Rows
lrow = .Range("Print_Area").Rows.Count
'MsgBox Lrow
If lrow = 23 Then Exit Sub
..Rows(24).Resize(lrow - 23).Delete
End With

Application.ScreenUpdating = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
I don't understand.
If desired, send your file to my address below along with this msg
and a clear explanation of what you want and before/after examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jeff Gross" wrote in message
...
Thanks for the help. This works perfectly if there is an entry in a cell
but
I have drop-down menus in a few of the columns and I thought it would see
that as a used cell even if an item had not been picked from the
drop-down
menu.

As a concept, this is a form that personnel use. If they know that they
will need more than the page 1 number of rows, the code will let them add
a
new page of blank rows (with the drop-down menus there as well).

Jeff

"Don Guillett" wrote:

If you want to copy
Range("A14:L23")
then use
Sheets("COC").Range("A14:L23").Copy
========
Sub CopyRows()' Notice the DOTS
Dim Lrow As Long
with sheets("COC")
Lrow = .Cells(Rows.Count, 1).End(xlUp).Row
.Range("A14:L23").Copy .Cells(Lrow + 1, 1)
end with
End Sub
=======

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jeff Gross" wrote in message
...
Sorry about that. I've tried many versions but the one I'm starting
with
is:

Sub CopyRows()

Dim Lrow As Long

'find last row in Col.A
Lrow = Sheets("COC").Cells(Rows.Count, 1).End(xlUp).Row

'copy and paste range

Sheets("COC").Range("A14:L23" & Lrow).Copy
Destination:=Sheets("COC").Cells(Lrow + 1, 1)

End Sub

Thanks.

"Don Guillett" wrote:

As ALWAYS, post YOUR code for comments.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jeff Gross" wrote in message
...
I've tried various combinations of posted solutions and can't seem
to
get
what I need to work properly.

I have a spreadsheet with one printable page by default. The top
13
rows
are headers for any additional pages (A1:L13) and there are rows
14-23
for
data entry.

I would like to add a button with the option to add additional
pages at
the
bottom of the document and then change the print setup to include
this
new
page as a printable page. It is an easy macro to add just one page
but
I
would like to be able to add any number of pages. Therefore I need
to
find
the last used row and then paste rows 14-23 to the new location
below
the
current page.

Thanks for any help.