Find last used row and paste from above

[Jeff Gross]

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.

 

[Don Guillett]

As ALWAYS, post YOUR code for comments.

 

[Jeff Gross]

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]

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
=======

 

[Jeff Gross]

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]

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]

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