Strange macro behaviour

[Shetty]

I have a macro which copies the usedrange of each sheets to a new
sheet called All(one below another) . When I run the macro, I found
that no data is copied at all. If I use currentregion, I found that in
one sheet, all the rows are not copied. I used ctrl+shift+end to find
out the usedrange which shows 23 rows but macro has copied only 12
rows.

I am making some obvious mistake but cant find what.
Any idea please?

Regards,
Shetty

 

[Chip Pearson]

Shetty,

It would be helpful if you would post the code that you are
currently using.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Tom Ogilvy]

for each sh in ActiveWorkbook.worksheets
if lcase(sh.name) <> "all" then
sh.UsedRange.EntireRow.copy _
Destination:=Worksheets("All").Cells(rows.count,1).End(xlup)(2)
End if
Next

assumes that there will always be values in column A if there is data in the
row.

 

[Shetty]

Hi,
Thanks Tom. You have once again helped me out.
I have written the code as below. It still leaves 6 rows from one
sheet without copy to new sheet. and it happens randomly to any sheet.
Out of 32 sheets 12 sheets are affected. Tom's code works exactly as
expected.(That's why he is a MVP).

My code not working as expected is below:
========
sub merge_Sheets()
Dim rng As Range
Sheets.Add
ActiveSheet.Name = "All"
For Each Sheet In ThisWorkbook.Worksheets
Sheet.Activate
If ActiveSheet.Name <> "All" Then
On Error Resume Next
Range("A1").CurrentRegion.EntireRow.Copy
Destination:=Worksheets("All"). _
Cells(Rows.Count, 1).End(xlUp)
' <<<If I use usedrange, no data is copied at all.>>>
End If
Next
End sub
==========

Regards,
Shetty.