I have a sheet that I copy and past into another in a macro using
paste speical values.....most of the cells are blank. I am using a
formula to pick the last 3 rows that have actually info which worked
in a new sheet but does not work when I copy and paste a large sheet
into another....it seems like it does not see the blank cells
correctly....


Please help this is the last piece of the puzzle....


Sheets("P124 copy1").Select
Range("a1").Select
With ActiveSheet
..Cells.Font.Bold = False
..Range("IV2").End(xlToLeft).EntireColumn.Font.Bol d = True
..Range("A" & Rows.Count).End(xlUp).EntireRow.Copy
Sheets("P124 copy1").Select
Range("a1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Sheets("P124 copy1").Select
Range("a1").Select

End With
With ActiveSheet
..Cells.Font.Bold = False
..Range("IV2").End(xlToLeft).EntireColumn.Font.Bol d = True
..Range("A" & Rows.Count).End(xlUp).Offset(-1, 0).EntireRow.Copy
Sheets("P124 copy1").Select
Range("a2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Sheets("P124 copy1").Select
Range("a1").Select

End With
With ActiveSheet
..Cells.Font.Bold = False
..Range("IV2").End(xlToLeft).EntireColumn.Font.Bol d = True
..Range("A" & Rows.Count).End(xlUp).Offset(-2, 0).EntireRow.Copy
Sheets("P124 copy1").Select
Range("a3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Sheets("P124 copy1").Select
Range("a1").Select

End With

Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

=========
If you need to, you can record a macro when you do this manually. Then add that
portion to your existing code.

wrote:

I have a sheet that I copy and past into another in a macro using
paste speical values.....most of the cells are blank. I am using a
formula to pick the last 3 rows that have actually info which worked
in a new sheet but does not work when I copy and paste a large sheet
into another....it seems like it does not see the blank cells
correctly....

Please help this is the last piece of the puzzle....

Sheets("P124 copy1").Select
Range("a1").Select
With ActiveSheet
.Cells.Font.Bold = False
.Range("IV2").End(xlToLeft).EntireColumn.Font.Bold = True
.Range("A" & Rows.Count).End(xlUp).EntireRow.Copy
Sheets("P124 copy1").Select
Range("a1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Sheets("P124 copy1").Select
Range("a1").Select

End With
With ActiveSheet
.Cells.Font.Bold = False
.Range("IV2").End(xlToLeft).EntireColumn.Font.Bold = True
.Range("A" & Rows.Count).End(xlUp).Offset(-1, 0).EntireRow.Copy
Sheets("P124 copy1").Select
Range("a2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Sheets("P124 copy1").Select
Range("a1").Select

End With
With ActiveSheet
.Cells.Font.Bold = False
.Range("IV2").End(xlToLeft).EntireColumn.Font.Bold = True
.Range("A" & Rows.Count).End(xlUp).Offset(-2, 0).EntireRow.Copy
Sheets("P124 copy1").Select
Range("a3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Sheets("P124 copy1").Select
Range("a1").Select

End With

--

Dave Peterson

Yeah I just saw that post!!!! been searching for hours.....
Thanks for the quick response!!!!!

I must say this is the best excel blog I have ever been on!!