Do you mean
sheets("sheet1")is your summary?
sheets 2-10 are the sheets you want data from? If so, where on the sheet?
Last row?
copied to the first available row in sheet 1?

then this works

Sub copyem()
For Each ws In Worksheets
If ws.Name < "Sheet1" Then
x = ws.Range("a65536").End(xlUp).Row
'MsgBox x
y = Sheets("Sheet1").Range("a65536").End(xlUp).Row + 1
'MsgBox y
ws.Range("a" & x).Copy _
Sheets("Sheet1").Range("a" & y)
End If
Next ws
End Sub


--
Don Guillett
SalesAid Software

"strataguru" wrote in message
...
Hi,

I am trying to go through all my worksheets in a workbook- copy a
specific row - and then paste the values to the first empty row after
the row copied.

The going through the worksheets part functions.
The copy functions.
The part where I paste values into the empty row is giving me problems
though.

Let's say - for example - row 3 has data that I want copied. And I
wanna put that data in row 15. The original row and destination row
will always be different (depending on criteria I have in other parts
of my logic).

This is what I have:

Cells.Select
Set rngWs = ActiveCell.CurrentRegion
With ws
Rows(rowToCopy).Copy
Application.CutCopyMode = False
Rows((rngWs.Rows.Count + 1)).Insert
Rows((rngWs.Rows.Count)).PasteSpecial Paste =
xlPasteSpecial
End With

What am I doing incorrectly?

Thanks so much!
-Robin


---
Message posted from http://www.ExcelForum.com/