Copy cells with data and paste

  • Thread starter Thread starter Thomp
  • Start date Start date
T

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
 
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.
 
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
 
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 -
Click to expand...

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
 
Back
Top