Copy cells with data and paste

[Thomp]

Anyone,
I am trying to do what I hope is a simple task. I need to have a two
part macro that will do the following.

1. Copy only the cells in a certain range that have data in the
cells.
2. I then need the macro to paste this data into a new worksheet on
this first available blank row

thanks in advance for anyones help on this.

Bill

 

[Don Guillett]

To learn, Record a macro while
use data>filter>autofilter to filter the NON blanks>copy>paste>
Then clean up the macro
To determine the last row use

lastrow=cells(rows.count,"a").end(xlup).row+1
Post back with further questions.

 

[Don Guillett]

Sub Macro2()'really cleaned up
lr = Cells(Rows.Count, "a").End(xlUp).Row
With Range("A1:A" & lr)
..AutoFilter Field:=1, Criteria1:="<>"
..Offset(1).Copy Range("D23")
..AutoFilter
End With
End Sub

 

[Thomp]

Sub Macro2()'really cleaned up
lr = Cells(Rows.Count, "a").End(xlUp).Row
With Range("A1:A" & lr)
.AutoFilter Field:=1, Criteria1:="<>"
.Offset(1).Copy Range("D23")
.AutoFilter
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software









- Show quoted text -

Don, I am still playing with this one. I figured a few things out so
what I am still stuck with is copying only the non blank cells from
one spreadsheet and pasting (value paste only) into a new spreasheet.
Could you help me with this one. I would like for it to be one macro

thanks,
Bill