Copy Paste Values to Last Row

[gldurand]

Hi Excel Warriors

I have this VBA code that works fine, but i need to modify it to always copy to LAST ROW. As the criteria changes based on the selection, i am forced to copy all pages of the spreadsheet even if i only have a few entries.

Can anyone assist

Dim extension As String

extension = ".xlsx"

Application.DisplayAlerts = False
Application.CopyObjectsWithCells = False ' Does not copy Macro objects

ActiveSheet.Copy
With ActiveSheet.UsedRange
.Value = .Value
.Validation.Delete ' Removes all Drop Down Lists
End With

Set wbNew = ActiveWorkbook
wbNew.SaveAs ThisWorkbook.Path & "\" & Format(Date, "yyyy-mm-dd") & " " & ActiveSheet.Range("L2") & extension
wbNew.Close True

Application.DisplayAlerts = True
Application.CopyObjectsWithCells = True

 

[DanteAmor]

i am forced to copy all pages of the spreadsheet

Do you want to paste each sheet on the same target sheet?
for example, do you copy sheet1 and paste into sheetX, then copy sheet2 and paste into sheetX but under the previously pasted data?

 

[gldurand]

Its only 1 sheet (Active) in the workbook that I am working with.

 

[DanteAmor]

Sorry but I don't understand.

ActiveSheet.Copy

What your macro does is copy the entire sheet into a new book.
What do you mean with " but i need to modify it to always copy to LAST ROW. "

 

[gldurand]

there is a drop down in my sheet that lists all the projects for that person. then i copy that to another workbook. But every time i select a new person, the number of projects change. I want to be able to only copy up to Last row. Right now its copying the entire Print Area (6 pages) even though the person might only have 1 page of data

Hope this helps, and thanks for your patience

 

[DanteAmor]

there is a drop down in my sheet that lists all the projects for that person.

Do you have a macro for that?

You could explain it with some examples, what you have on the sheet, what you put in your drop down list, what you should copy and what you expect as a final result.

 

[DanteAmor]

there is a drop down in my sheet that lists all the projects for that person.

Do you have a macro for that? You put the macro here.

You could explain it with some examples, what you have on the sheet, what you put in your drop down list, what you should copy and what you expect as a final result.
Use the XL2BB tool to show examples, see my signature.